DoCmd OpenForm - with WHERE clause not working ? (1 Viewer)

liamfitz

Registered User.
Local time
Today, 09:05
Joined
May 17, 2012
Messages
240
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 )
Code:
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.:eek:
 

liamfitz

Registered User.
Local time
Today, 09:05
Joined
May 17, 2012
Messages
240
That's terrific pbaldy. Thank you, it works perfectly now.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

markzaal

Registered User.
Local time
Today, 10:05
Joined
Jan 15, 2013
Messages
50
And what would I do if I wanted a second Where clause?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
You would use a single Where clause, with the 2 conditions separated be AND or OR as appropriate.
 

lcp298

New member
Local time
Today, 09:05
Joined
Feb 28, 2013
Messages
7
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.

Code:
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

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
Is the form's Data Entry property set to Yes?
 

lcp298

New member
Local time
Today, 09:05
Joined
Feb 28, 2013
Messages
7
Yes, it is the original form the record was entered onto.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
That overrides your wherecondition. Use the arguments within OpenForm instead if you want to use the same form for entry and edit.
 

lcp298

New member
Local time
Today, 09:05
Joined
Feb 28, 2013
Messages
7
Ah, thanks, I'll try that!
Do the arguments take the same format as the where condition?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
If you look in help, you should get a good explanation of the available arguments and their syntax.
 

gashokkumar

New member
Local time
Today, 14:35
Joined
Apr 18, 2017
Messages
7
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

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
The double quotes on both sides of And need to be deleted.
 

gashokkumar

New member
Local time
Today, 14:35
Joined
Apr 18, 2017
Messages
7
Thank you Paul for the quick response. You made my day:).

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

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
Happy to help and welcome to the site by the way!
 

gashokkumar

New member
Local time
Today, 14:35
Joined
Apr 18, 2017
Messages
7
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

Wino Moderator
Staff member
Local time
Today, 02:05
Joined
Aug 30, 2003
Messages
36,118
Perhaps

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

gashokkumar

New member
Local time
Today, 14:35
Joined
Apr 18, 2017
Messages
7
Vowwww!

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

You are expertise in access.
 

Users who are viewing this thread

Top Bottom