Compare fields from different sources?

Hi All.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11943-compare-fields-from-different-sources.html

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11943
Else
End
End If


Thanks in advance for any help provided.

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11943
> 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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11943
>
> 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....