Checking if records exist

[ChrisLeicester]

Hi All
I am writing vba to check if a record exists before I open a form and am going back to basics, doing one step at a time before adding the next step.

I have a query called [SearchPOQ] which lists all the purchase order numbers already entered.
I have a primary key field called [ProdOrderID]

For initial testing, my code just puts a message box to say record found or record not found.

Private Sub SearchBTN_Click()

'check if any records exist

    If DCount("[ProdOrderID]", "SearchPOQ") = 0 Then
    MsgBox "Purchase Number Not Found", vbOKOnly, "Incorrect P.O. Number"
    Else
    MsgBox "Purchase Number Found", vbOKOnly, "Correct P.O. Number"
    End If
    
End Sub

When i run the code it gives the same 'Purchase Number Found' message whether I put a number in that I know I have or know I do not have.

Am I not understanding the else command. I was assuming that if the count was 0 it would say not found and if the count was 1 it would say found.

Should I use count and not Dcount, is Dcount counting a null record as 1?

Thanks

 

[sonic8]

You are invoking DCount without any criteria. It is just counting *all* ProdOrderID in SearchPOQ.
Use the Criteria argument of DCount to filter for the currently entered PO#.

 

[GPGeorge]

Your DCount function has no parameter; therefore it always returns the total number of records in the query result, not any one specific Order.

To filter on any specific Order Number, DCount must include that filter as a parameter:

If DCount("[ProdOrderID]", "SearchPOQ" "ProdOrderID = " & SpecificOrderID) = 0 Then....

Where SpecificOrderID is the Order Number for which you are searching. It could be a variable or it could be a reference to a control on a form.

 

[ChrisLeicester]

Hi All
that makes sense, I will adjust the code now
thanks

 

[cheekybuddha]

If DCount("*", "SearchPOQ", "ProdOrderID = 123") = 0 Then
  ' ...

 

[Gasman]

Might want to indent a little better as well.?
Makes code much easier to read.

 

[Pat Hartman]

And you don't want to hard code the ID you're looking for.

Private Sub SearchBTN_Click()

'check if any records exist

    If DCount("[ProdOrderID]", "SearchPOQ  = " & Me.ProdOrderID) = 0 Then
        MsgBox "Purchase Number Not Found", vbOKOnly, "Incorrect P.O. Number"
    Else
        MsgBox "Purchase Number Found", vbOKOnly, "Correct P.O. Number"
    End If
    
End Sub

 

[cheekybuddha]

    If DCount("[ProdOrderID]", "SearchPOQ  = " & Me.ProdOrderID) = 0 Then

Whilst the intention is good, I'm not sure that will work as written.

 

[Pat Hartman]

I agree. I was simply providing syntax for using a non hard-coded value.

 

[cheekybuddha]

I agree. I was simply providing syntax for using a non hard-coded value.

Yes, but the syntax is DCount(Expr, Domain, [Criteria])

You appear to have omitted either expr or domain, and then used the domain as a field in the criteria.

 

[Pat Hartman]

Picky, picky, picky

 

[Isaac]

Might want to indent a little better as well.?
Makes code much easier to read.

+1 to that. Indent your code according to which pieces 'belong' to a parent statement

 

[cheekybuddha]

Picky, picky, picky

Well, it's the compiler that's picky.

I was just trying to help you out, but you take it whichever way you want.