Search Form

[mattieflo]

Hello, I'm trying to write a search form that will search a main form for
fields. The main form consists of several of several subforms. The following
piece of code successfully searches for the Buyer Field on the main field.

Private Sub cmdSearchBuyer_Click()

Dim stbuyernumber As String
Dim stDocName As String
Dim stLinkCriteria As String


stbuyernumber = InputBox("Enter Buyer Number", "Search Buyer Number")
If stbuyernumber <> "" Then
stDocName = "frmMain2"
stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearch"
End If

A buyer can have many emails. I have an EMAIL subform that is linked to the
BUYER mainform. I want to search for the primary key of the email subform
(EmailID) so that it brings up the appropriate parent buyer number. Is there
a way to adjust this line in the code so it does that?

"stLinkCriteria = "[tblBuyer].[BuyerNum] = " & stbuyernumber"

Thanks!

End Sub

[Allen Browne]

Presumably you have a one-to-many relationship between buyers and emails, so
your email table has a foreign key named BuyerNum.

If so, you need to DLookup() the BuyerNum for the particular email. You can
then use this number in your string.

This kind of thing:

Dim strWhere As String
Dim varResult As Variant
strWhere = "EmailID = " & Me.[EmailID]
varResult = DLookup("BuyerNum", "EmailTable", strWhere)
If IsNull(varResult) then
MsgBox "Email ID not found"
Else
stLinkCriteria = "BuyerNum = " & varResult
'Now use the string as you expect to.
End If

If you need help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mattieflo" <matt...@discussions.microsoft.com> wrote in message
news:C37F8B79-F4D1-4658...@microsoft.com...

[mattieflo]

Thanks for that Allen. It worked like a charm for this and will come in handy
for the future. Greatly appreciated!