Compare fields from different sources?

[PROskid TechInfo]

Hi All.

I have a form which contains a subform.

I was thinking of using the "On Exit" event in one of the subform fields to
trigger the following:

1. Take the value the user has just entered into the sform field with the on
Exit event attached
2. See if that value exists in a field of a particular table (the table name
would be included in the code)
3. If the value does exist display another form showing fields from the
table (based on a select query where the value the user entered is the WHERE
part)
4. If the value does not exist in the checked table - continue working
normally.



ie. Dim myValue As String

myValue = Forms!sfmMyForm.[egField] 'How do I narrow this down to the
current row?

If myValue exists in Tables!tblTest.[testField] Then
DoCmd.OpenForm "myOtherForm" ' Open the form based on the select
query containing the myValue in the WHERE part
Else
End
End If


Thanks in advance for any help provided.

 

[Steve Schapel]

PROskid,

I would use the After Update event rather than the Exit event.

Dim strCrit As String
strCrit = "[egField]='" & Me.TestField & "'"
If DCount("*","tblTest",strCrit) > 0 Then
DoCmd.OpenForm "MyOtherForm", , , strCrit
End If

 

[JamesW]

Thanks for the help.

I'll give it a go....