Make a query or code check if a record exists & prompt user for action

[KennyJaymes]

Hi all, I am a relitive newcomer to VB-A so please treat me like a dummy. I am hoping to learn from your kind replies.

I have an append query that is part of a chain of querys used to import data from a outside database.

Each record in both my own and the outside db have a unique Catalogue Number.

I need some way of checking if this catalogue number already exists in my own database and if it does,
I would like the query to be paused while a message box pop's up and warns the user that Catalogue Number <what ever it happens to be to be shown to the user> already exists and wont be appended, the user then clicks on an OK button, and the code then continues until either it happens again or the import is completed.

Is this going to be very hard to do and could someone suggest how I might do it. Code examples would be great if I am not being to cheaky or imposing on everyone (I know you are all busy people).

Thank you in advance,

Kenny [sig][/sig]

 

[Amiel]

Hey Kenny, try this:

Private Function ItemExists() as Boolean

Dim db as database
Dim rs as recordset
Set db = CurrentDB

Set rs = db.openrecordset(&quot;SELECT [ID] from [TableofInterest] where [TableofInterest].[ID]=&quot; & ID_ImLookingFor

[red]If Not rs.EOF and not rs.BOF then[/red]
' it exists
ItemExist = TRUE

Else
'it doesnt exist.
ItemExist = FALSE

End If

End Function

You'll get plenty of mileage from this one.



[sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]

 

[cmmrfrds]

In case the user wants to do something else while the import is running your could log the records that are duplicates. Use Amiel's routine to determine the duplicates and then use this code to log records after you have created the log table with appropriate fields to track the information you want.
Dim RST2 As New Recordset, cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

'-- Open the the LogTable table
RST2.Open &quot;LogTable&quot;, cnn, adOpenKeyset, adLockOptimistic
RST2.AddNew
'---- Build the record
RST2!logField1 = &quot;whateveryouwantfrominput&quot;
RST2!logField2 = &quot;whateveryouwantfrominput&quot;
RST2!logDate = Now()
'-etc.........
RST2.Update
RST2.Close

This is using the syntax for an ADO RecordSet -- everything after the .AddNew is the same as a non ADO RecordSet like Amiel is showing. After the import is done you can check the log table for any records -- check Amiel's function, table name will be different and no criteria is needed. If any records exist then open a form that will display the records. This way the user can handle the errors at their convience.

Jerry
[sig][/sig]