vba coding to cross-check values in another table based on a value entered in a form using a recordset

I have a form called "Labor Tracker" whose data source is a table called "Labor".  In this form, the user enters information for three fields called

1. IITR (integer)
2. WorkOrderNo (text)

In the before update event of this form, I would like a table called "iitdata" to be opened, where vba checks the "IITReportNo" field (integer) and checks for a matching IITR value AND checks the "ReceiverNo" field (text) and checks for a matching WorkOrderNo.  If neither matches are found, an error message stating that the IITR and/or WorkOrderNo are incorrect.  So IITR should map to IITReportNo and WorkOrderNo maps to ReceiverNo.

I only got so far as to open the iitdata table as a recordset but am not sure how to now perform the check.  I could really use expert help please.  Thanks.
Dim db As DAO.Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcErr

Dim rst As DAO.Recordset
Dim rstcount As Integer
Dim TableName As String

TableName = "iitdata"

Set db = CurrentDb
Set rst = db.OpenRecordset(TableName)

Open in new window

Who is Participating?
Bill RossConnect With a Mentor Commented:

You can easily look up a value using the dlookup function.  Check help for the syntax but it will return a unique valu for the column and row.  I think in your case you would need something like:

dim valuefound
valuefound=dlookup("IITReportNo","iitdata","ReceiverNo='" & me!WorkOrderNo & "'")
if isnull(valuefound) or me!IITR <> valuefound  then
  msgbox "Not found or invalid IITR."

This is rough but may get you started.

BTW - use the after update of the fields - not the form.



Note that you need to put single quotes around the WorkOrderNo field since it's text.

sxxguptaAuthor Commented:
Thanks Bill!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.