Access Open Form with WhereCondition


Joined
Apr 23, 2018
Messages
4
Reaction score
2
Hello, I have a split form: frmTotalAmtUnionDuesOwedByMemberID that I open with a cmd button. Once the form opens it has the field: NALCMBRID as though it is a hyperlink. I have the following code for the OnClick event procedure:

Private Sub TextBoxNALCMBRID_Click()
Dim stLinkCriteria As String
stDocName = "Branch 142 Membership"
stLinkCriteria = "[tblMembers.NALCMBRID]=" & Me![NALCMBRID]
strWhere = "[NALCMBRID] = '" & [NALCMBRID] & "'"
DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.NALCMBRID=" & "Me.NALCMBRID"
End Sub

When I select the field: NALCMBRID on the form: frmTotalAmtUnionDuesOwedByMemberID instead of it going to the record with the NALCMBRID I selected. I get "Enter Parameter Value" for Me.NALCMBID. When I enter the NALCMBID, the form: Branch 142 Membership will open for the NALCMBID I entered.

The code is supposed to take me to the record in the XTab query dataset based on the NALCMBRID selected on either the form part or the datasheet part of a split form that's based on a XTab query. The query is a XTab.

I have had nothing but problems getting this same method that has worked on other split forms that are not based on XTab queries. I've used the following in an On Click event procedure for non-XTab queries:

DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.NALCMBRID=" & Me.NALCMBRID.

Thanks for the help.
 
Ad

Advertisements

Joined
Mar 14, 2018
Messages
638
Reaction score
209
Why did you wrap the Me.NALCMBRID in quotes? When you are appending a control's value to a string you leave the quotes off.
 
Joined
Apr 23, 2018
Messages
4
Reaction score
2
Why did you wrap the Me.NALCMBRID in quotes? When you are appending a control's value to a string you leave the quotes off.
Because I keep getting a run-time error 3464: Data type mismatch in criteria expression and at least Me.NALCMBRID in quotes was taking me to the first record in the tblMembers for the form: Branch 142 Membership.
 
Joined
Mar 14, 2018
Messages
638
Reaction score
209
What line are you receiving the "Data type mismatch" error? My guess it's when you are calling OpenForm because you are not enclosing the value in single quotes. Your code should look something like this instead:

DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.NALCMBRID ='" & Me.NALCMBRID & "'"

I hope this helps.
 
Joined
Apr 23, 2018
Messages
4
Reaction score
2
What line are you receiving the "Data type mismatch" error? My guess it's when you are calling OpenForm because you are not enclosing the value in single quotes. Your code should look something like this instead:

DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.NALCMBRID ='" & Me.NALCMBRID & "'"

I hope this helps.

Thanks for the help. It did work. So I can learn from this please explain why the & "'" worked. I could not find anything that would show that & "'" was needed at the end.

So the final OnClick event procedure is:
Private Sub TextBoxNALCMBRID_Click()
Dim stLinkCriteria As String
stDocName = "Branch 142 Membership"
stLinkCriteria = "[tblMembers.NALCMBRID]=" & Me![NALCMBRID]
strWhere = "[NALCMBRID] = '" & [NALCMBRID] & "'"
DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.NALCMBRID ='" & Me.NALCMBRID & "'"
End Sub
 
Joined
Mar 14, 2018
Messages
638
Reaction score
209
Ok, so let me explain why my code worked.
The WhereCondition accepts a statement that follows this format: tblName.ColumnName = 'value'
See the apostrophe surrounding the word value?
When you send the value of a control without the apostrophes the compiler thinks you are trying to use a keyword so it throws errors because it doesn't recognize it.
I'm glad you got it to work :dance:
 
Ad

Advertisements

Joined
Apr 23, 2018
Messages
4
Reaction score
2
Ok, so let me explain why my code worked.
The WhereCondition accepts a statement that follows this format: tblName.ColumnName = 'value'
See the apostrophe surrounding the word value?
When you send the value of a control without the apostrophes the compiler thinks you are trying to use a keyword so it throws errors because it doesn't recognize it.
I'm glad you got it to work :dance:

I did not do anything, you did. BTW, I do not see a way to mark your response as answered. Thanks again for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top