Dcount problem

[ponneri]

Hello.

I'm having a lot of trouble with Dcount() on a form. I Googled a lot and tried so many options but feel helpless with the stupid ' " (quotes). In fact, it's irritating !

Can some one guide me with the exact syntax please ?

What I'm trying to do is as follows.

On a form, i have 3 unbound controls, 1 & 2 are combo boxes and 3 is a text box. Once user selects the two values in combos (both text strings) and enters a number (digit) in the text box, I use the Dcount () in the afterupdate event of the text box - to see if the 3 values exist as a record in the underlying table.

I get an error at runtime, that says ") expected " or "Type mismatch" for the last text box; even after I used a Val() to convert the user entered number in text box.

If DCount("ponum", "po_table", "[ponum]='" & Forms!poform!combo1 & "' , AND _
"[pocode]='" & Forms!poform!combo2 & "' , AND _
[itemno]= & val(Forms!poform!text1) ") > 0 Then
MsgBox "Record already exists"
End If

The same code with different fields works on another form perfectly. Why ??

 

[RuralGuy]

Put the qualifier in one string variable and then display it with a MsgBox or Debug.Print and you will see the issue.

 

[ponneri]

ok, thank you. I'll check that and see.

 

[arnelgp]

If Nz(DCount("ponum", "po_table", "[ponum]='" & Forms!poform!combo1 & "' AND " & _
"[pocode]='" & Forms!poform!combo2 & "' AND " & _
"[itemno]= " & val(Forms!poform!text1)), 0) > 0 Then

 

[ponneri]

Let me try that also and see ! The appl is on my PC at work.

 

[ponneri]

If Nz(DCount("ponum", "po_table", "[ponum]='" & Forms!poform!combo1 & "' AND " & _
"[pocode]='" & Forms!poform!combo2 & "' AND " & _
"[itemno]= " & val(Forms!poform!text1)), 0) > 0 Then

This does not give any error, as the event does not fire at all. Simply moves on to the next field. Why ?

 

[RuralGuy]

Put an Else in your code and display a different MsgBox.