Re: Primary Key Violation Error Message

Dylan, I should have read your query more carefully. The Execute method
cannot call the Expression Service which Access uses to resolve the
references to the controls on the form.

Concatenate the values from the form into the string. Numbers don't need any
delimiter, but you do need to check that a value is present. Text type
fields need quotes as delimiters. Dates need # as the delimiter, and if your
database could be used in a country that uses non-US dates, you need to
force the string to use the US date order and slash separators.

It will end up something like this:

Const conJetDate = "\#mm\/dd\/yyyy\#"

With [Forms]![frm23AdminAnalysisSetUp]
strSql = "INSERT INTO tblErrAnalysis (Year, CoID) SELECT " & _
![cboPeriodYears] & ", tblCompany.CoID FROM tblCompany " & _
"WHERE ((tblCompany.LicenceGranted <= " & _
Format(![txtPeriodEndDate], conJetDate) & _
") AND (tblCompany.LicenceSurrendered Is Null)) " & _
"OR ((tblCompany.LicenceGranted <= " & _
Format(![txtPeriodEndDate], conJetDate) & _
" AND ...
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dylan Moran" <DylanMoran@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3DD25DA-F574-4DED-8B0C-883C529ABA89@xxxxxxxxxxxxxxxx
> Hi Allen,
>
> Thanks for the information and the code on your website. I have applied my
> SQL and touches to your code and unfortunately I am getting an error "Too
> Few
> parameters. Expected 3."
>
>
> I have done some searches and found this information
> http://support.microsoft.com/default.aspx/kb/210244#XSLTH3154121123120121120120
> But am not sure if this applies to my case.
>
> Any ideas where the problem is?
>
> My current code is:
>
> Sub cmdExecuteSetUp_Click()
>
> On Error GoTo ErrorPoint
> Dim ws As DAO.Workspace 'Current workspace (for transaction).
> Dim db As DAO.Database 'Inside the transaction.
> Dim bInTrans As Boolean 'Flag that transaction is active.
> Dim strSql As String 'Action query statements.
> Dim strMsg As String 'MsgBox message.
>
> 'Step 1: Initialize database object inside a transaction.
> Set ws = DBEngine(0)
> ws.BeginTrans
> bInTrans = True
> Set db = ws(0)
>
> 'Step 2: Execute the append.
> strSql = "INSERT INTO tblErrAnalysis (Year, CoID) " & _
> "SELECT [Forms]![frm23AdminAnalysisSetUp]![cboPeriodYears],
> tblCompany.CoID " & _
> "FROM tblCompany " & _
> "WHERE
> (((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
> AND ((tblCompany.LicenceSurrendered) Is Null)) " & _
> "OR
> (((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
> AND
> ((tblCompany.LicenceSurrendered)>=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodStartDate]));"
> db.Execute strSql, dbFailOnError
>
> 'Step 3: Get user confirmation to commit the change.
> strMsg = "Execute the Set Up of" & db.RecordsAffected & " record(s)?"
> If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
> ws.CommitTrans
> bInTrans = False
> End If
>
> ExitPoint:
> 'Step 4: Clean up
> On Error Resume Next
> Set db = Nothing
> If bInTrans Then 'Rollback if the transaction is active.
> ws.Rollback
> End If
> Set ws = Nothing
> Exit Sub
>
> ErrorPoint:
> MsgBox Err.Description, vbExclamation, "Execution of Set Up failed: Error
> " & Err.Number
> Resume ExitPoint
> End Sub
>
>
> --
> I may not know VBA inside out, but from the outside I am looking in.
> Dylan Moran - Melbourne Australia
>
>
> "Allen Browne" wrote:
>
>> Instead of OpenQuery, use the Execute method inside a transaction. You
>> can
>> pop up your on message to get the user's response, and RollBack the
>> entire
>> operation if they don't want to proceed.
>>
>> If you have never used transactions, there are several pitfalls, so see
>> the
>> example in this article:
>> Archive: Move records to another table
>> at:
>> http://allenbrowne.com/ser-37.html
>> The example shows how to execute a pair of action queries in tandem for
>> an
>> all-or-nothing result, but the same principles apply to your single
>> query.
>>
>>
>> "Dylan Moran" <DylanMoran@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:323E9542-C16B-4333-9B94-C46648C2DC87@xxxxxxxxxxxxxxxx
>> >
>> > I have an "insert into query" sql query that runs well (code below).
>> > What
>> > I
>> > want is to ensure that you cannot be run it twice. The primary key for
>> > the
>> > table is a combination key of Year (Year) and CompanyID (CoID). The
>> > Insert
>> > Into query inserts the appropriate year (which is used in the primary
>> > key).
>> >
>> > Hence when it attempts to run it again, an error message appears saying
>> > "Didn't add records due to key violations, Do you want to run this
>> > action
>> > query anyway? To ignore the errors and run the query click Yes. (with
>> > yes
>> > no & help buttons)".
>> >
>> > I want to handle this error/situation and not get this Access created
>> > message and somehow have my own custom message.
>> >
>> > Any ideas how to go about this? Thanks for any assistance.
>> >
>> > ****code starts here****
>> > Dim db As DAO.Database
>> > Dim qdf As DAO.QueryDef
>> > Dim strSQL As String
>> >
>> >
>> > ' Turn off screen updating
>> > DoCmd.Echo False
>> >
>> > strSQL = "INSERT INTO tblAnalysis (Year, CoID) " & _
>> > "SELECT [Forms]![frm23AdminAnalysisSetUp]![cboPeriodYears],
>> > tblCompany.CoID " & _
>> > "FROM tblCompany " & _
>> > "WHERE
>> > (((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
>> > AND ((tblCompany.LicenceSurrendered) Is Null)) " & _
>> > "OR
>> > (((tblCompany.LicenceGranted)<=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodEndDate])
>> > AND
>> > ((tblCompany.LicenceSurrendered)>=[Forms]![frm23AdminAnalysisSetUp]![txtPeriodStartDate]));"
>> >
>> >
>> > ' Apply the SQL statement to the stored query
>> > Set db = CurrentDb
>> > Set qdf = db.QueryDefs("qry37AdminAnalysisSetUpStoredQuery")
>> > qdf.SQL = strSQL
>> > Set qdf = Nothing
>> > Set db = Nothing
>> >
>> > 'Open the Query
>> > DoCmd.OpenQuery "qry37AdminAnalysisSetUpStoredQuery"
>> >
>> > ****code ends here****
>> >
>> > --
>> > I may not know VBA inside out, but from the outside I am looking in.
>> > Dylan Moran


.