If statement that looks up values from one form to another form (1 Viewer)

[Harry Paraskeva]

Hello to all,

I have an issue that can be described as follows:
- There is a textbox on a form and when I fill in its value, I would like it to lookup whether that value matches any of the values in another form and if it does present a message box. Both fields hold only numerical data, so no type mismatch issues.

I've tried the code following, but it only looks up the first value on the second form, not its entire dataset, which is problematic.

Private Sub Lot_AfterUpdate()
DoCmd.OpenForm ("Pot_Pot_ExtraLots")
If Me.Lot.Value = Forms!Pot_Pot_ExtraLots!ExtraPotLots.Value Then
MsgBox "There is a bag with extra sherds found during other analyses from this Lot! Lookup and combine results!"
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
Else
DoCmd.Close acForm, "Pot_Pot_ExtraLots", acSaveNo
End If
End Sub

Any help is much appreciated!

 

[arnelgp]

Why using form when you can directly lookup to the table where Pot_Pot_ExtraLots form is saving its records. You can use DLookup or DCount, etc.

 

[namliam]

Data in an access database is held in tables not in forms.

You should try finding the data in your table(s) not in the forms, the form only sees its 'current' record.

Using any domain function like DCount or DLookup should be avoided, as it leads to overusage of this functions and will cause a major drag on you database.

 

[Harry Paraskeva]

I've managed to get it working with DLookup, as below, but I was wondering if there is a less heavy way to go about it. I'm marking the thread as solved, but if any ideas are out there, I'd be interested to hear them.

Private Sub Lot_AfterUpdate()
If DLookup("ExtraPotLots", "Pot_Pot_ExtraLots", "[ExtraPotLots] = Forms![Pottery_Analysis]!Lot") = Me.Lot.Value Then
MsgBox "There is a bag with extra sherds found during other analyses from this Lot! Lookup and combine results!"
End If
End Sub