Can an IF-Then in VB evaluate a Query Result? Part 2

[dmon000]

I'm using the code that JoeAtWork provided earlier;(Thanks again JOe!) just the line that begins with "Nz". the rest is my hack job.

The event procedure opens a form "frm ADD to WARD" in hidden mode, which in turn opens a parameter query "qry ADD to WARD. This query prompts for an ID field value (number field). This ID field is named "MPI".

What I want to happen is IF the MPI number typed in response to the parameter query returns a record, then make the "frm ADD TO WARD" visible so it can be used for input; if the query does not return a record, then open "form ADD NEW PATIENT".

What's happening is after entering an MPI number that I know exists, I get "Run-time error '3076': Syntax error (missing operator) in query expression 'MPI='

The code I am using is:
Private Sub AddPatientToWard_Click()

DoCmd.OpenForm "frm ADD TO WARD", , , , , acHidden
If Nz(DLookup("mpi", "qry Add to Ward", "MPI=" & txtMPI), 0) <> 0 Then
Me.Visible = True
Else
DoCmd.Close , "frm ADD TO WARD"
DoCdm.OpenForm "frm ADD NEW PATIENT", , , , , acNormal
End If

End Sub

I have also used the "frm ADD TO WARD" for the domain instead of "qry ADD TO WARD" with the same result.

Thanks again!!

 

[Remou]

How about:

Nz(DLookup("mpi", "qry Add to Ward", "MPI='" & txtMPI & "'"), 0)

 

[dmon000]

Thanks Remou. I just used your line and I'm getting "Run-time error '2001. You canceled the previous operation."

Here's the code:

DoCmd.OpenForm "frm ADD TO WARD", , , , , acHidden
If Nz(DLookup("mpi", "qry Add to Ward", "MPI='" & txtMPI & "'"), 0) Then
Me.Visible = True
Else
DoCmd.Close , "frm ADD TO WARD"
DoCdm.OpenForm "frm ADD NEW PATIENT", , , , , acNormal
End If


Thanks for your help!

 

[Remou]

Check the names of everything, in case of misspelling.

 

[dmon000]

The name of the field is "MPI" and the name of the query is
"qry ADD TO WARD". Everything in that line seems to be spelled ok. Have I typed the & txtMPI & "'" part correctly? Here is the line copied from the code:

If Nz(DLookup("MPI", "qry ADD TO WARD", "MPI='" & txtMPI & "'"), 0) Then

Thanks again!

 

[TheAceMan1]

Howdy Remou . . .

[blue]This query prompts for an ID field value ([purple]number field[/purple]). This ID field is named "[purple]MPI[/purple]".[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

How are ya dmon000 . . .

Post the [blue]SQL[/blue] of the query! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

TheAceMan1, perhaps you missed the line below in the original post:

[tt]Run-time error '3076': Syntax error (missing operator) in query expression 'MPI='[/tt]

An indicator that MPI is, in fact, a text field (people frequently refer to mixed letter / number combinations as numbers). That this is likely to be the case is shown by the fact that the original error has now gone and the OP is dealing with a new error, one which indicates a problem with the syntax, rather than a data type mismatch error, which is what I would have expected if MPI was a numeric field.

I agree that the error could have occured with a null value for txtmpi and a numeric field, but I would have expected an error 3464, rather that 2001 when the quotes were added.

I note that the OP also quotes the error number as 3076, rather that 3075, the usual number for the above error, but I have taken this as a typo.

I wonder if txtMPI contains anything? It seems that the next step would be to try this with a know value of MPI.