Compare data in 2 tables

[Barra44]

I have a form based on table1
when i enter a number eg 1234 in field JobNumber I want to check that 1234 exists in table2 field Job_No and field status = "A" or "P"
If 1234 does not exit in table2 then a mesage apears stating Job number does not exist
If 1234 exists but staus is not equal to "A" or "P" then a message apears stating Job is closed

Thanks in advance

 

[ZmrAbdulla]

Look for DLookUpin the VBA help file

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[BobStubbs]

You could do this using DCount. Example:

Dim intCounter as Integer

intCounter = DCount("[Job_No]", "table2", "[Job_No]=" & Me.JobNumber)

if intCounter = 0 then
    MsgBox "Job does not exist"
    Exit Sub
end if

intCounter = DCount("[Job_No]", "table2", "[Job_No]=" & Me.JobNumber & " AND [Status]<>'A' AND [Status]<>'P'")

if intCounter = 0 then
    MsgBox "Job is closed"
    Exit Sub
end if

My example assumes that JobNumber is numeric - for alphanumeric job numbers you would need extra single quotes in the DCount statement, e.g.:

intCounter = DCount("[Job_No]", "table2", "[Job_No]='" & Me.JobNumber & "'")

Take care with the quote marks ' and " - they must be exactly right.

You could put this code in the Lost_Focus event of the JobNumber field, but I think the neatest idea would be to create a command button called [Check Job], next to the JobNumber field, and place the code in the button's On_Click event.

Then you can check as and when you need to.

I hope that this is useful.





Bob Stubbs

 

[PHV]

Are the pairs (Job_No,status) unique in table2 ?
I.E. may a Job_No have different status records ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Barra44]

Thank for your responses to my thread
My apologies for not responding back sooner
I have only just received your replies as I had forgot that my emails were going to an email address which does not exist anymore.Have fixed that problem know. Will try your sugestions and see how it goes
Thanks again