Debugger complains about [apparently] non-existant characters

[John O'Malvern]

I'm using this code to open a form in print preview:

Private Sub cmdPrint_Click()
    Dim strWhere As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[Contact ID] = " & Me.[Contact ID]
        DoCmd.OpenReport "Associate_MembershipCard_FormGDPRSel_frmNewAssoc", acViewPreview, , strWhere
    End If
End Sub

but it throws up this error:

and when I click on debug, it highlights [in yellow] the line with 'Contact ID' (I inherited this db with spaces in the fieldnames )
There is no |1 in the highlighted line, and I've no idea where else to find it. (must be a typo)
I'm not good at VBA, the code is from 'Microsoft Access Tips for Casual Users'

Any suggestions on what I'm doing wrong, please?

Thanks,
John

 

[Gasman]

Is there actually a control in the form and a field called [Contact ID] in the report source?

 

[Minty]

That would suggest (rather obtusely I'll admit) that Me.[Contact ID] is null.
After you set strWhere add the following line

Debug.Print strWhere & " " & IsNull(Me.[Contact ID],"Its Null!")

And see what you get in the immediate window. (Press Ctrl + G in the VBA editor to make it visible if it isn't)

 

[arnelgp]

check the Name of the Textbox and use it on your Criteria string.

 

[CJ_London]

I presume contact id is numeric?

 

[The_Doc_Man]

The error "can't find |n referred to in ..." suggests that something is spelled or referenced incorrectly. Since it is calling out |1 it means that the first field of that query is not right. (I have seen |2 on rare occasions, calling out the 2nd field of a query, so yes, the number following the | is the ordinal position of the field in question.)

The Debug button highlights the strWhere line, so something on that line triggered a faulty reference and the only non-constant and non-local variable on the line is Me.[Contact ID] - so you have to focus on the form's controls.

Do you have a control called [Contact ID] on the form? If not, perhaps that is your problem.

But the other possibility is this: Do you have a control on that form named [Contact ID] and does the form's .RecordSource also have a FIELD named [Contact ID] - in other words, TWO things that could rightly be named [Contact ID] ? This occurs when a control has the same name as a field, which can occur when someone uses a wizard to build a form's controls. The control and the corresponding underlying field name are BOTH visible to Me.[X] and the syntax for both references is the same, so Access doesn't know which one to use.

 

[John O'Malvern]

Is there actually a control in the form and a field called [Contact ID] in the report source?

Spot on! I've illustrated the dangers of using something that already works in one situation, but now in a different way, and 'coming a cropper'!

 

[John O'Malvern]

That would suggest (rather obtusely I'll admit) that Me.[Contact ID] is null.
After you set strWhere add the following line

Debug.Print strWhere & " " & IsNull(Me.[Contact ID],"Its Null!")

And see what you get in the immediate window. (Press Ctrl + G in the VBA editor to make it visible if it isn't)

Ironically, ContactID /doesn't/ have a space, although many other FN do. so that wasn't helping, but Gasman suggested the actual problem.
The report works fine when called in a different way, as has been the case for many months.

 

[June7]

Doc,
I tested a form with an UNBOUND textbox named same as a field in RecordSource. Following code both returned value from textbox:
Debug.Print Me.Test1
Debug.Print Me!Test1

When there was no control by that name, same code returned value from field.

 

[The_Doc_Man]

@June7, the different between dot and bang is one of internal timing, because the default property of "Me." is Controls(), and using . you get that control at compile time, with bang, you find it at run time. Your experiment suggests there is a priority on whether you find a control name (as a property of the form) or a field name (as a property of the form).

Ironically, ContactID /doesn't/ have a space, although many other FN

That spelling difference would trigger the reported error message since it was asking for something that DID have a space.