Trouble With Dcount returning all the records

[harleyskater]

I am trying to use Dcount to check to see if a Purchase Order Number has been used before.

Private Sub po_num_AfterUpdate()
Dim LTotal As Long

LTotal = DCount("po_id", "dbo_purchase_orders", po_num = Me.po_num)
      
        If LTotal >= 2 Then
                MsgBox "This PO# may already exist!", _
                vbExclamation, "Possible Duplicate PO #"
        End If
End Sub

I have 249 Purchase Orders in the DB and DCount returns 249!

I have played with the code for about an hour now and I have made queries as the domain and tables as the domain. I do not know what I am doing wrong. I have put a breakpoint in that code like 100 times! !!!!​

 

[pbaldy]

Try

LTotal = DCount("po_id", "dbo_purchase_orders", "po_num = " & Me.po_num)

 

[CraigDolphin]

And (just in case) if the po_num column contains alpha-numeric values (text), you might need to add some single quotes too:

LTotal = DCount("po_id", "dbo_purchase_orders", "po_num = '" & Me.po_num & "'")

if the values are strictly numeric, then Paul's solution is correct

 

[harleyskater]

yes it does! i dont' have time to try it now! time to clock out, but the single quotes was not part of my vba coding vocabulary yet hahah maybe i bet thats it! <3~!@ thanks to both of you guys.

And just in case the po_num column contains alpha-numeric values (text), you might need to add some single quotes too:

LTotal = DCount("po_id", "dbo_purchase_orders", "po_num = '" & Me.po_num & "'")

 

[pbaldy]

Glad we got it sorted with Craig's help. Here's a handy reference:

http://www.mvps.org/access/general/gen0018.htm

 

[harleyskater]

I am still lost on this. I am going to check out your link but the first 2 DCount's return 0 and the third Dcount returns 249! It should return 3. I am going to take a look at your link. Pbaldy

LTotal = DCount("po_id", "dbo_purchase_orders", "po_num = '" & Me.po_num & "'")
LTotal = DCount("po_id", "dbo_purchase_orders", "po_num = " & Me.po_num)

LTotal = DCount("po_id", "dbo_purchase_orders", po_num = Me.po_num)

 

[harleyskater]

LTotal = DCount("po_num", "dbo_purchase_orders", "po_num = '" & Forms![purchase orders]!po_num & "'")

That works!

I am still lost on this. I am going to check out your link but the first 2 DCount's return 0 and the third Dcount returns 249! It should return 3. I am going to take a look at your link. Pbaldy

LTotal = DCount("po_id", "dbo_purchase_orders", "po_num = '" & Me.po_num & "'")
LTotal = DCount("po_id", "dbo_purchase_orders", "po_num = " & Me.po_num)

LTotal = DCount("po_id", "dbo_purchase_orders", po_num = Me.po_num)