How to use a recordset to validate and entry?

[Steve in MN]

I have a form where user enters a BOL number (bill of lading) and on lost
focus of that field i would like it to be able to go out and look at another
table to see if that BOL is a valid number.
Can someone give me an idea on how to do this?

 

[Allen Browne]

Use DLookup() in the AfterUpdate event procedure of this text box.

If you need help, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Use the text box's BeforeUpdate event (and set Cancel=True) if you want to
jam the user in this text box until they enter a valid number or undo (by
pressing Esc.)

 

[Mark Grizzle]

Use the lost focus event for the control to open a recordset to seach for
the bol number. Psuedo code follows...

Sub field_lost_focus

On error goto LocalError

Dim cnn as adodb.connection
Dim rs as adodb.recordset
Dim sql as string

Sql = "select count(1) from bol_table where bolfield = '" & me.bolnumber &
"' "

'load an ado connection

Rs.open sql, cnn

If rs.fields(0) = 1 then
'record found
Else
'record not found
End if

Rs.close
Set rs = nothing

ExitHere:
exit sub

LocalError:
'handle error
resume exithere

End sub

Hope this helps,
mark

 

[Steve in MN]

Thanks for the help guys!
Much appreciated.

Steve