VBA code for form to display Error message

[CTVT]

I have a query that has a parameter input to enter a customer ID.


There is a form that references this query.
In the form on the On Activate code for vba I am trying to show a message if the user inputs
a record number that does not exist with a message "No Record found"
My code in the On Activate event procedure for the form is this:

Private Sub Form_Activate()
If DCount("*", "FindID#") = 0 Then
MsgBox "No record"
Else
DoCmd.OpenForm (FindID#)
End If
End Sub

When I run the form and enter a record known not to exist I get this:

Run-time error '2471':

The expressions you entered as a query parameter produced this error:
'[Enter ID #]'

The above is the parameter that opens when you run the query.
The query works fine. If I enter a known non existent number in the query
it shows a window with no record.

Where in my coding am I wrong? Do I not have it in the proper event procedure?
Or is the code I have entered just wrong?

[ranman256]

if the ID is entered into a text box,. txtFindID
check if exists, then open the form

If isnull(DLOOKUP("*", "table", "[ID]=" & txtFindID)) Then
    msgbox "No record"
else
    DoCmd.OpenForm "frmDetail",,,"[ID]=" & txtFindID
end if

[June7]

I have never seen OpenForm method use that syntax for arguments. Why is FindID# within parens? What is the name of this form?

I NEVER use dynamic parameterized queries.


BTW, should not use spaces or special characters/punctuation (underscore is exception) in naming convention.

[CTVT]

if the ID is entered into a text box,. txtFindID
check if exists, then open the form

If isnull(DLOOKUP("*", "table", "[ID]=" & txtFindID)) Then
    msgbox "No record"
else
    DoCmd.OpenForm "frmDetail",,,"[ID]=" & txtFindID
end if

When I used this code I received error 3075.
It said Missing operator in query expression "*",

In you above code I put in AllRecords for the table,
and Find_ID_# for my form.

I may need to rename my queries and forms without spaces.
It's a hard habit to break. I have always used spaces for better understanding
instead of abbreviating and running all letters together.

[June7]

That's why many use underscore.

[CTVT]

That's why many use underscore.

What is the answer why I got the error message about the missing operator in query expression "*"?
Yes, when I entered the vba code I used the underscore for the form name.

[June7]

Did you actually change the name of the form object in the navigation pane?

You have a table named "table"?

DLookup requires a field to be specified for looking at, cannot use * for DLookup. DCount() can use *.

[CTVT]

Did you actually change the name of the form object in the navigation pane?

You have a table named "table"?

DLookup requires a field to be specified for looking at, cannot use * for DLookup. DCount() can use *.

I am sending you an image of the exact code I have. It was suggested by ranman256.
It gives me an error of missing operator in query expression "*",
however when I click on the end button in the vba debugger it continues on and gives me the record I asked for.

[June7]

Doesn't alter my comment.

Specify search field instead of wildcard.

Or use DCount() conditional instead.

[Xipooo]

Just so I can get some clarification, what is "FindID#"? Is it a query? As June7 said, you shouldn't use special characters as a general rule for any of your names for things. Symbols tend to mean something in Access that can lead to unintended consequences.

If we knew a bit more of your intent with this code, and what FindID# is, we might be able to help you out.