Access Open Form with WhereCondition

[rdw1260]

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.

 

[Abraham Andres Luna]

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.

 

[rdw1260]

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.

 

[Abraham Andres Luna]

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.

 

[rdw1260]

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

 

[Abraham Andres Luna]

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

 

[rdw1260]

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

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.