No records showing on linked form

[Guest]

Hi All,

Can someone tell me why the following code is not filtering properly? My
subform opens okay but there are no records showing and I know that there are
matching records. Both link fields, "CDT_Contract_No" and "Contract_No" are
text fields. I'm baffled!

Private Sub Contract_No_DblClick(Cancel As Integer)

On Error GoTo Err_Contract_No_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_Contract_View"
stLinkCriteria = "'[CDT_Contract_No]'=" & "'" & Me![Contract_No] & "'"

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

Exit_Contract_No_DblClick:
Exit Sub

Err_Contract_No_DblClick:
msgbox Err.Description
Resume Exit_Contract_No_DblClick

End Sub

 

[Rick Brandt]

Hi All,

Can someone tell me why the following code is not filtering properly? My
subform opens okay but there are no records showing and I know that there are
matching records. Both link fields, "CDT_Contract_No" and "Contract_No" are
text fields. I'm baffled!

Private Sub Contract_No_DblClick(Cancel As Integer)

On Error GoTo Err_Contract_No_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_Contract_View"
stLinkCriteria = "'[CDT_Contract_No]'=" & "'" & Me![Contract_No] & "'"

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

There should be single quotes around the second part of the stLinkCriteria
string but not around the field name.

stLinkCriteria = "[CDT_Contract_No] = '" & Me![Contract_No] & "'"

When the variable is evaluated you want a result like...

[CDT_Contract_No] = 'SomeString'

The way you had it would result in...

'[CDT_Contract_No]' = 'SomeString'

 

[Guest]

Thanks! I swear these quotations are sending me to an early grave!

Hi All,

Can someone tell me why the following code is not filtering properly? My
subform opens okay but there are no records showing and I know that there are
matching records. Both link fields, "CDT_Contract_No" and "Contract_No" are
text fields. I'm baffled!

Private Sub Contract_No_DblClick(Cancel As Integer)

On Error GoTo Err_Contract_No_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm_Contract_View"
stLinkCriteria = "'[CDT_Contract_No]'=" & "'" & Me![Contract_No] & "'"

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

There should be single quotes around the second part of the stLinkCriteria
string but not around the field name.

stLinkCriteria = "[CDT_Contract_No] = '" & Me![Contract_No] & "'"

When the variable is evaluated you want a result like...

[CDT_Contract_No] = 'SomeString'

The way you had it would result in...

'[CDT_Contract_No]' = 'SomeString'