Prev: get ip address
Next: Two problems, Conditional Formatting and Printing a record
From: iccsi on 13 May 2010 10:40
I use CurrentDb.Execute strMYSQL, dbFailOnError to run my update
query.

I got run time error 3022 which is duplicate records found.
I tried to update multi records from the code.

I use following code to filter out the error, MS Access only updates
the first record.

Err_Handle:
Select Case Err
Case 3022
Resume Exit_Handle
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) &
vbCrLf
strErrMsg = strErrMsg & "Error Description: " &
Err.Description
Resume Exit_Handle
End Select

I would like to know is it possible to update mutli records using VBA
code like CurrentDB.Execute


Your help is great appreciated,
From: Dirk Goldgar on 13 May 2010 11:55
"iccsi" <inungh(a)gmail.com> wrote in message
news:b014425f-ea6b-4196-9743-494b1a1f1049(a)b7g2000yqk.googlegroups.com...
>I use CurrentDb.Execute strMYSQL, dbFailOnError to run my update
> query.
>
> I got run time error 3022 which is duplicate records found.
> I tried to update multi records from the code.
>
> I use following code to filter out the error, MS Access only updates
> the first record.
>
> Err_Handle:
> Select Case Err
> Case 3022
> Resume Exit_Handle
> Case Else
> strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) &
> vbCrLf
> strErrMsg = strErrMsg & "Error Description: " &
> Err.Description
> Resume Exit_Handle
> End Select
>
> I would like to know is it possible to update mutli records using VBA
> code like CurrentDB.Execute


Are you *updating* multiple records, which is no problem at all for an
UPDATE query, or ar you *inserting* multiple records with an append query,
and want duplicates to be ignored? If you're getting error 3022, I think
you must be inserting records, not updating them, unless you are running an
update query that changes the value of a unique key.

If you are running an append query, you can remove the "dbFailOnError"
argument and I believe the non-duplicate records will be appended. That is,
instead of:

> CurrentDb.Execute strMYSQL, dbFailOnError

.... use:

CurrentDb.Execute strMYSQL

I'm not sure if that works with an update query or not.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 | 
Pages: 1
Prev: get ip address
Next: Two problems, Conditional Formatting and Printing a record