DoCmd OpenForm - with WHERE clause not working ?

[liamfitz]

The following line of code is triggered in a click_event on a subform. I know the syntax is correct, but it prompts me for a parameter value ( the value I pass in the 'WHERE' clause i.e. Crefid )

DoCmd.OpenForm "tblReferrals", , , "Referral_ID = " & Crefid

When I type this in, it opens as expected. I know this value is correct, in my VBA code, and I'd like it to open automatically, without prompting. Any suggestions ? Thanks.

 

[pbaldy]

The prompt is Access telling you it can't find that value. Is it a variable? See if this helps:

http://www.baldyweb.com/wherecondition.htm

 

[liamfitz]

That's terrific pbaldy. Thank you, it works perfectly now.

 

[pbaldy]

Happy to help!

 

[markzaal]

And what would I do if I wanted a second Where clause?

 

[pbaldy]

You would use a single Where clause, with the 2 conditions separated be AND or OR as appropriate.

 

[lcp298]

I have an OpenForm on the Click event of a button on a report (I've tried it on a form as well), but the form is not acting on the WHERE clause.

Private Sub btnOpenBooking_Click()
On Error GoTo Err_btnOpenBooking_Click

Dim strDocName As String
Dim strCriteria As String

strDocName = "Make an Outreach Booking"
   
strCriteria = "SCHOOLID =" & Me.SCHOOLID
MsgBox "" & strCriteria
DoCmd.OpenForm strDocName, , , strCriteria

Exit_btnOpenBooking_Click:
    Exit Sub

Err_btnOpenBooking_Click:
    MsgBox Err.Description
    Resume Exit_btnOpenBooking_Click
End Sub

I know the correct SCHOOLID is being passed, as it is shown in the MsgBox, and if I go to Design View on the form it shows SCHOOLID = whatever the number is in the Filter property.
The form opens and 'filtered' is shown on the bar at the bottom but it always opens to a blank record.

I have also had trouble with the criteria of a DCount not working, could the two be related?

Does anyone have any ideas why this doesn't work?

 

[pbaldy]

Is the form's Data Entry property set to Yes?

 

[lcp298]

Yes, it is the original form the record was entered onto.

 

[pbaldy]

That overrides your wherecondition. Use the arguments within OpenForm instead if you want to use the same form for entry and edit.

 

[lcp298]

Ah, thanks, I'll try that!
Do the arguments take the same format as the where condition?

 

[pbaldy]

If you look in help, you should get a good explanation of the available arguments and their syntax.

 

[gashokkumar]

Hello All,

Can anyone help me on the below code, I'm stuck here :banghead:and not able to go to next and getting below error. It is working for single condition when I used it for multiple conditions I'm getting Type Mismatch 13 error.

Code:
DoCmd.OpenForm "Process_F", , , "processor='Alex'" And "status='Pending'"

Type of error:
Run time error "13"
Type Mismatch

 

[pbaldy]

The double quotes on both sides of And need to be deleted.

 

[gashokkumar]

Thank you Paul for the quick response. You made my day.

Thanks a ton again!!!!!!!!!!

 

[pbaldy]

Happy to help and welcome to the site by the way!

 

[gashokkumar]

Thank you, Paul!

This time I'm trying to pull a record details in form for a particular processor when the status is blank. When I am trying to us the below code, I'm not getting an error but the output is coming with new blank/NULL record which I do not required. Your help is much appreciable on this, thank you very much in advance.


Private Sub Command3_Click()

DoCmd.OpenForm "Processor Form", , , "processor='ALEX' And status=' '"

End Sub

Regards,
Ashu

 

[pbaldy]

Perhaps

DoCmd.OpenForm "Processor Form", , , "processor='ALEX' And status Is Null"

 

[gashokkumar]

Vowwww!

Really appreciate on your quick response. Thank you very much.

You are expertise in access.

 

[pbaldy]

No problemo!