VBA Code to lookup table and output MessageBox upon result? 1

[Kobayashi]

I'm sure this is quite straight foward using a DLookup function of similar, but can anybody help with the following:

I have a main table with a number field. On either the BeforeUpdate or AfterUpdate events I need the number the user enters into this field to lookup another table to see if the number entered is amongst the numbers in this 2nd table? If so, 'true', I then need to output a message box and perhaps autofill some other fields on the form. The latter part I can do (hopefully) but I'm stuck with the code for the lookup part?

Any help is always greatly appreciated!

 

[WMcGregor]

If you are going to retrieve more values to populate other fields on the screen, I would go about it like so:

Dim db As DATABASE, rst As Recordset, strsql As String
Set db = CurrentDb
strsql = "SELECT Field1, Field2, Field3 " & _
"FROM Table2 WHERE tbl2_nbr = " & Me!frmnbr & ";"

Set rst = db.OpenRecordset(strsql)
If rst.EOF = False Then
msgbox "The value you entered is not in ..."
Else
Me!frmField1 = rst!Field1
Me!frmField2 = rst!Field2
Me!frmField3 = rst!Field3
End If
rst.Close

You will have to supply the field1,2,3 names etc...

Hope this helps

 

[WMcGregor]

One minor change, the If should read

If rst.EOF = True then
msgbox...
else
....
end if

 

[Kobayashi]

Many thanks for your prompt reply!
I look forward to trying this but before I do I'd just like to clarify the following:

I only wish to show a Msgbox should the number entered into the form actually match one in the table. I presume I should change the 'false' in the code you provided to 'true'?
Also, the other fields I will populate should this be true will be filled with text strings I specify in the code, not data in the recordset. Again, I presume I just change the 'Me!(whatever) = "(text that I want to say?&quot.

Regards,

 

[WMcGregor]

If you only want to do something when the record exists, use the original code just move the form fields up by the msgbox and get rid of the ELSE altogether. The IF rst.EOF=False indicates you found a matching record... You would not need the recordset fields either, just assign your text values to the form fields as you indicated...

 

[Kobayashi]

Works like a dream! Many thanks indeed!
However, you've really started something now!

Would it be possible, using this method, to have the same field lookup other tables/recordsets in addition to this one?

Regards,

 

[WMcGregor]

Sure, just add rst2 as recordset, rst3 as recordset, etc. and do another set rst2 = db.openrecordset("Select...&quot and reference rst2.EOF etc... and rst2.close...

 

[Kobayashi]

Excellent!

I'm sure I'll have some more questions about expanding this idea which, hopefully you'll still be around to help with, but for now many thanks indeed! You're going to make me look far more adept than I actually am at work!!!

All the best

Adrian