Command Button, find multiple matching fields

[SherryScrapDog]

Hi, I know the answer must be here somewhere but I have not been successful
with what I have tried so far. I have 3 Tables:
Master: [Id], [Last], [First]
Detail: [Id], [MasterId], [BookId] (multiple records per Master & Book)
Book: [Id]

I have a form with a command button where I can enter the last name and find
all of the records with the matching last name. This works fine.

I would also like to be able to have a command button where I can enter the
last name and the first name and find only those records. There is only one
Master record with the same Last Name and First name, then multiple Detail
records.

Here is my latest attempt and I get a compile error:

stLinkCriteria = "[Last]= " & Chr(34) & Me![EnterLast] & Chr(34) & " AND
"[First]= " Chr(34) & Me![EnerFirst] & Chr(34) &"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be so appreciated!! Thanks, Sherry

 

[John W. Vinson]

Hi, I know the answer must be here somewhere but I have not been successful
with what I have tried so far. I have 3 Tables:
Master: [Id], [Last], [First]
Detail: [Id], [MasterId], [BookId] (multiple records per Master & Book)
Book: [Id]

I have a form with a command button where I can enter the last name and find
all of the records with the matching last name. This works fine.

I would also like to be able to have a command button where I can enter the
last name and the first name and find only those records. There is only one
Master record with the same Last Name and First name, then multiple Detail
records.

Here is my latest attempt and I get a compile error:

stLinkCriteria = "[Last]= " & Chr(34) & Me![EnterLast] & Chr(34) & " AND
"[First]= " Chr(34) & Me![EnerFirst] & Chr(34) &"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be so appreciated!! Thanks, Sherry

You've just got some misplaced and extra quotemarks. Chr(34) is the code for
a " delimiter; you can use it, or just use two consecutive doublequotes in a
string to represent one doublequote. Try

stLinkCriteria = "[Last]= """ & Me![EnterLast] & """ AND [First]= """ &
Me![EnerFirst] & """"

or

stLinkCriteria = "[Last]= " & Chr(34) & Me![EnterLast] & Chr(34) & " AND
[First]= " & Chr(34) & Me![EnerFirst] & Chr(34)

The result of either will be a stLinkCriteria string like

[Last] = "Doe" AND [First] = "John"

 

[SherryScrapDog]

Hi John, Thanks!!!!! It is now working! I keep learning!
Have a great weekend, Sherry

Hi, I know the answer must be here somewhere but I have not been successful
with what I have tried so far. I have 3 Tables:
Master: [Id], [Last], [First]
Detail: [Id], [MasterId], [BookId] (multiple records per Master & Book)
Book: [Id]

I have a form with a command button where I can enter the last name and find
all of the records with the matching last name. This works fine.

I would also like to be able to have a command button where I can enter the
last name and the first name and find only those records. There is only one
Master record with the same Last Name and First name, then multiple Detail
records.

Here is my latest attempt and I get a compile error:

stLinkCriteria = "[Last]= " & Chr(34) & Me![EnterLast] & Chr(34) & " AND
"[First]= " Chr(34) & Me![EnerFirst] & Chr(34) &"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be so appreciated!! Thanks, Sherry

You've just got some misplaced and extra quotemarks. Chr(34) is the code for
a " delimiter; you can use it, or just use two consecutive doublequotes in a
string to represent one doublequote. Try

stLinkCriteria = "[Last]= """ & Me![EnterLast] & """ AND [First]= """ &
Me![EnerFirst] & """"

or

stLinkCriteria = "[Last]= " & Chr(34) & Me![EnterLast] & Chr(34) & " AND
[First]= " & Chr(34) & Me![EnerFirst] & Chr(34)

The result of either will be a stLinkCriteria string like

[Last] = "Doe" AND [First] = "John"

 

[SherryScrapDog]

Thanks so much!!! It's now working and now I have learned something else!!
Have a great weekend, Sherry

DoCmd.OpenForm stDocName, , , "[Last] & [First]='" & Me.EnterLast & Me.
EnterFirst & "'"
You can use the stLinkCriteria variable, but if not used again in procedure
not necessary.

Hi, I know the answer must be here somewhere but I have not been successful
with what I have tried so far. I have 3 Tables:
Master: [Id], [Last], [First]
Detail: [Id], [MasterId], [BookId] (multiple records per Master & Book)
Book: [Id]

I have a form with a command button where I can enter the last name and find
all of the records with the matching last name. This works fine.

I would also like to be able to have a command button where I can enter the
last name and the first name and find only those records. There is only one
Master record with the same Last Name and First name, then multiple Detail
records.

Here is my latest attempt and I get a compile error:

stLinkCriteria = "[Last]= " & Chr(34) & Me![EnterLast] & Chr(34) & " AND
"[First]= " Chr(34) & Me![EnerFirst] & Chr(34) &"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be so appreciated!! Thanks, Sherry