Trying to use DCount to verify a record is there...

[fralippolippi]

Hello,

I am trying to create a 'start page' form - from which you will launch into other forms as needed. In this case, you will either create a new Purchase Request (PR), Edit or View an existing PR, or Approve a PR.

While for the Edit/View an existing PR I have this code working wonderfully:

If DCount("*", "tblPRSubmit", "[PRSubmitID] = " & Me.txtPRNum) = 0 Then
MsgBox "No Records Found with that PR Submit ID", vbInformation, "Search Failed"
Else
DoCmd.OpenForm "frmPRSubmit", , , "PRSubmitID = " & Me.txtPRNum


This all falls apart at the next step - the Approver's Form only "sees" PR's that have been submitted (there is hidden check box on the frmPRSubmit that gets checked when they click the "submit" button). So, the frmApprover has criteria that Submitted = True in order for any data to be visible.

So, what happens is, if for instance on PRSubmitID 5, I have not yet submitted it, I can type that number in the "Approve a PR" text box and hit the button and the Approver Form will pop up with blank data - what I'd like to have happen is that it doesn't pop up at all!!

I've tried this:

If DCount("*", "tblPRSubmit", "[PRSubmitID] = " & Me.txtPRNumAppr AND "Submitted = True") = 0 Then
MsgBox "No Records Found with that PR Submit ID", vbInformation, "Search Failed"
Else
DoCmd.OpenForm "frmPRApproval", , , "PRSubmitID = " & Me.txtPRNumAppr

and also...

If DCount("*", "tblPRSubmit", "[PRSubmitID] = " & Me.txtPRNumAppr AND "Submitted = False") = 0 Then
MsgBox "No Records Found with that PR Submit ID", vbInformation, "Search Failed"
Else
DoCmd.OpenForm "frmPRApproval", , , "PRSubmitID = " & Me.txtPRNumAppr


But either case it just always errors....

Is it possible to get this to work? Is something else better like maybe Select Case or something? I am not a very skilled scripter so...any help would be appreciated.

 

[pbaldy]

Close; try

If DCount("*", "tblPRSubmit", "[PRSubmitID] = " & Me.txtPRNumAppr & " AND Submitted = True") = 0

 

[fralippolippi]

Worked like a charm. It was those quotation marks....Thank you so much!

 

[pbaldy]

Happy to help!