Check table for existing data via form (1 Viewer)

[MaryMo]

Can anyone help me please? I have set up Query1 with 2 prompts: (id) and (case). in my main form, I would like to check tblData to see if the ID and Case exist. If it does, a messagebox will alert the user that a report already exists. My code looks like this:

dim myquery as querydef
set myquery=dbengine(0).databases(0).querydef ("query1")
myquery.parameters("checkID")=me.ID
myquery.parameters("checkCase)=me.Case

[now i'm missing something here...]

if not EOF then
msgbox ("An Initial report already exists. You must make this a Supplement.")
exit sub

I know I'm suppose to "get" my recordset here but I can't quite seem to get the code down.

Any advise would be great!

 

[D-Fresh]

Try using SQL in your code.

MyDB as Database
MySQL as String
MyRecs as Recordset

MySQL = "SELECT tblData.ID, tblData.Case FROM tblData Where tblData.ID=" & me!ID & " AND tblData.Case=" & me!Case & ";"

Set MyDB = CurrentDB
Set MyRecs = MyDB.OpenRecordset(MySQL)

'then here you can do your check to see if the recordset exists...

if Not MyRecs.EOF then
msgbox "Sorry, already exists."
end if

'Continue your code after this...

If you have problems writing the SQL string, create the query and then copy/paste the SQL string it creates for you into your code. Just replace the criteria with the Where clause I gave you. Good luck.

HTH
Doug