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.

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

--
Steve Schapel, Microsoft Access MVP

PROskid TechInfo wrote:
> 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.
>
>

Thanks for the help.

I'll give it a go....