compare data from form to a different table

[lazyluci]

Hi,
I have a form that I enter data into that updates the table CNA. When I enter data in the Site No field, I have written code to check the table "Agencies" and compare the Site No fields for a match. I loaded the Agencies table into a recordset in form load and then did the comparison in the Site No_LostFocus() function.

The problem is, I need to check the next field Instructor No with the table "Instructors" to see the the Instructor No matches InstructorID. How can I do both of these comparisons in the same form?

Any help would be much appreciated

 

[wemeier]

Use two different recordsets. Open both in the OnOpen or OnLoad event of the form:

Private Sub Form_Open()
    Set db = CurrentDb()
    Set rstAgencies = db.OpenRecordset("Agencies")
    Set rstInstructors = db.OpenRecordset("Instructors")
Exit Sub

In the Instructor No control's OnLostFocus event use the recordset that is associated with the Instructors table.

Private Sub Instructor_No_LostFocus()
    rstInstructors.FindFirst "[Instructor]='" & Me("Instructor No") & "'"
    If rstInstructors.NoMatch Then
        Msgbox "No such instructor!"
    End If
End Sub

Private Sub Site_No_LostFocus()
    rstAgencies.FindFirst "[Agency]='" & Me("Site No") & "'"
    If rstAgencies.NoMatch Then
        Msgbox "No such Agency!"
    End If
End Sub

Finally, don't forget to close the recordsets and remove them from memory before closing the form!

Private Sub Form_Close(Cancel as Integer)
    rstAgencies.Close
    rstInstructors.Close
    Set db = Nothing

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[willir]

Lucy

Provided you just need to find one record, you can use
DLookUp

Form based on...
Site No field on table CNA

Comapre
CNA.SiteNo -> Agencies.SiteNo
If on a form[tt]
=DLookUp("[SiteNo]", "YourAgenciesTable", "[SiteNo] = " & SiteNo)[/tt]

If in code[tt]
Dim lngSiteID as Long
lngSiteID = Nz(DLookUp("[SiteNo]", "YourAgenciesTable", "[SiteNo] = " & Me.SiteNo), 0)

...or with code

If Not (Nz(DLookUp("[SiteNo]", "YourAgenciesTable", "[SiteNo] = " & Me.SiteNo), 0)) Then
'... no match found
End If[/tt]

Compare
CNA.InstructorNo -> Instructors.InstructorNo

Repeat the above, but swap tables and fields as is appropriate.


...Moving On
Are you trying to link the Agency and Site, and Instructor when you are creating your CNA record ?

Have you looked at using combo or list boxes to accomplish this? You can use the RecordSource with an SQL statement to query the related table. For example, depending on the design...

SELECT SiteID from YourAgenciesTable Where SiteID = Me.SiteID

(You would have to do the above with some simple VBA code)

Richard

 

[lazyluci]

wemeier,
I am having a problem with the code you submitted. I put the first part of the code in the Form_Open event and it isn't loading the recordsets, then I tried moving it to the Form_Load event and still nothing. Any suggestions.

And willir,
I tried the dlookup on the form and no such luck. i'm just lost. the sites worked but the instructors did not.

 

[willir]

Luci

What is the design of your Instructors table? Design on the CNA table would be helpful too.
How do you grab the info - InstructorID, siteID ??

 

[lazyluci]

OH MY! I figured it out. Silly me. In the table Instructors, the InstructorID (which is their license number) was set up as a TEXT field and i was referring to it as an integer field in vb. Now that I have changed that, all is working just great. Thank you both so much for your help.