date and string condition with docmd.openform (1 Viewer)

asadkhan said:

Re
Hi
Can somebody help me,
I am trying combine following two fields in ms access vba,

Private Sub Command6_Click()

DoCmd.OpenForm "frmRecordbetweenTwoDatesbyDoctorNameFormView" , , , "dtDate between #" & Format([ApptStartDate], "dd-mmm-yyyy") & "# And #" & Format([ApptEndDate], "dd-mmm-yyyy") & "# And [txtConsultantDoctorName] = '" & Me.CBODocName & "'"

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "frmSearchAppointmentsBetween2DatesBYDOCNAME", acSaveYes
End Sub
----------
I am actually trying to pass parameters from a form which contains start and end date and a combo box containing names. what actually I am trying is looking up doctors' appointments between two dates with their names (doctor name). I am able to search them separately.

I am new to vba

I get following error:
Run-Time error '2501':
The OpenFormaction was canceled.

and if I implement following

DoCmd.OpenForm "frmRecordbetweenTwoDatesbyDoctorNameFormView" , , , "dtDate between #" & Format([ApptStartDate], "dd-mmm-yyyy") & "# And #" & Format([ApptEndDate], "dd-mmm-yyyy") & "# " And " &[txtConsultantDoctorName] = '" & Me!CBODocName & "'"
code error becomes:
Run-Time error '13'


PS. I got this got from PBaldywebsite, I just added my frm and added date criteria with "between" and "And"

Click to expand...

asadkhan said:

yes it is, it is combo box on the form named "frmRecordbetweenTwoDatesbyDoctorNameFormView" and CBODocName is an unbound combo box on the search form and two date field are also on the search form

Click to expand...

theDBguy said:

Okay, to make sure, the problem is with this part, correct?

Code:Copy to clipboard

"dtDate between #" & Format([ApptStartDate], "dd-mmm-yyyy") & "#  And #" & Format([ApptEndDate], "dd-mmm-yyyy") & "# And  [txtConsultantDoctorName] = '" & Me.CBODocName & "'"

If so, the following names need to be of the specified object:


dtDate = Field (Table)

ApptStartDate = Textbox (Form)

ApptEndDate = Textbox (Form)

txtConsutantDoctorName = Field (Table)

CBODocName = Combobox (Form)


Please confirm above. Thank you.

Click to expand...

asadkhan said:

the above is right

Click to expand...

asadkhan said:

When I pass the parameters separately it works fine, if I only use date it works and show records between two dates. And when I only use consultant/doctor lookup box to pass parameter to the form it works fine as well. But problem arises when I combine date parameter with doctor name, combining both parameters. If you could tell how to join both parameters and pass them together to to the display form.

Click to expand...

asadkhan said:

The form is based on a simple select query. It has two button, one for closing the form and one to run the query again. There is no other code on the form. I can't send the whole db, because of nature of the dB.

Thank you very much for your time.

Click to expand...

asadkhan said:

Row Source - looking up from a group query
Bound Column - unbound
Column Count - 1
Column Widths - 1

Click to expand...

Gasman said:

Tip when doing stuff like this.
Build a string and use the string. That way you can debug.print the string, rather than slapping a bunch of code together and not sure what it actually looks like.

Click to expand...

asadkhan said:

I think you must learn ethics regarding use of language you are using and also I didn't particularly ask you anything... I better close my question and look somewhere else... As rudeness have no place in learning.

Click to expand...

asadkhan said:

I am not sure what part of my request is confusing.
Let explain to you again

I have two forms
Form one is used to pass following parameters to form two:
Consultant/Doctor Name (it's a combo box but we need to know how this combobox is built by asking what's in the properties I listed above)
Appointment start date
Appointment end date (both dates are from date of appointment as you may see Between and And are use)
The form one has got two buttons, one to run the code in question and other is to close the form one.
Now the second form contains doctors name and dtdate(which actually is appointment date) and sundry others fields. not being able to see the Record Source of the form can add to the confusion or mystery
The form two is base on a simple select query no criteria attached. Form two opens from form one with parameters passed to it to show only records which meet the criteria. based on what I understood so far, the code to do this looks fine. So, it means we might have to look elsewhere why what you want to happen is not happening
Furthermore, I am able to pass criteria when I only use dates. Form two shows data as required. Same in case of doctor consultation name, if I use it on its own it works fine. nothing in the way you combined the fields looks off; however, I agree with the other suggestion to examine the criteria by assigning it to a variable and then examining its content
Problem arises when I want to pass both parameters at the same time.
As far as attaching dB I can't do that because of nature of dB. This might put you off, it's okay to me if you can't help in this case. I am really thankful to you for kind help.

Click to expand...