• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Linking field with query

I have a form called “Scheduling-Add” used to add a new schedule, while the user is filling the blank fields, when they enter the date in the “DateOfAppt” field I want the user to be able to double click in that “DateOfAppt” field and use that date in running a query called “qSchedulingchk”  that displays the schedules for that date in question. For example if the user is entering a new schedule and wanted to schedule that patient for 10-5-11 after entering that date they decide they want to check to see what rooms are available on that day, they double click the “DateOfAppt” field which should prompt the “qSchedulingchk”  query to run for that date of 10-5-11. How can I accomplish this goal. Attached is my database with the forms and query
Invoice-7-7-2001-91611.accdb
0
Chrisjack001
Asked:
Chrisjack001
  • 10
  • 7
1 Solution
 
mbizupCommented:
Change the criteria for the date field in your query.  It is currently set to [Date] for user input.

Change the criteria to read from the form:

Forms![Scheduling-Add]!DateOfAppt

Then in the double-click event:

DoCmd.OpenQuery "qSchedulingchk"

Open in new window

0
 
mbizupCommented:
That said, I would recommend rather than opening the query directly to use that query as the recordsource for a popup form and open a form instead.
0
 
Chrisjack001Author Commented:
Hi mbizup, using your theory how will that affect my query because that query is also ran through my switchboard under scheduling / Daily Query
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
Hmm... if it is being used elsewhere, then don't change it, but rather use it's SQL with the change I suggested as the record source of a pop-up form.
0
 
Chrisjack001Author Commented:
Sorry. I am resending the database with the updated Switchboard form
Invoice-7-7-2001-91611.accdb
0
 
mbizupCommented:
That way your be leaving the query as is so there are no effects elsewhere.  You'd just be using a separate amended version of it as a pop up form's recordsource.
0
 
mbizupCommented:
Looks like we cross posted.  Anyhow, do my previous two comments make sense?
0
 
Chrisjack001Author Commented:
Hi mbizup, I am confused now. Where should I insert this code "DoCmd.OpenQuery "qSchedulingchk" and will this action automatically use the date the user will have in that “DateOfAppt” field?
0
 
mbizupCommented:
Okay - what I'm suggesting is to create a seperate form (it can be in datasheet view) with the modifed query as it's recorsource.  Then in the double-click event of your date field, instead of opening the query, open your new popup form:

docmd.openform "frmYourNewPopup"
0
 
Chrisjack001Author Commented:
okay so what modified query should I use for this new form?
0
 
mbizupCommented:
This is the SQL to your original query, with the modification I suggested to read the value from the DateOfAppt on your form:

SELECT Scheduling.PatientName, Scheduling.RoomNumber, Scheduling.DateOfAppt, Scheduling.ApptTime, Scheduling.DateOut, Scheduling.ApptTimeOut, Scheduling.StudyID, Scheduling.Notes, Scheduling.SchedulingID
FROM Scheduling
WHERE (((Scheduling.DateOfAppt)=[Forms]![Scheduling-Add]![DateOfAppt]) AND ((Scheduling.Cancel)=False));

Open in new window


Create a new popup form (you can set its default view to Datasheet).  Copy this SQL into the RecordSource property of your form.


Then add the following code to the double-click event of your DateOfAppt textbox:

docmd.openform "frmYourNewPopup"

0
 
Chrisjack001Author Commented:
Hi mbizup, I followed all your instructions and created another form called "Scheduling Check2". I am encountering a couple of problems. When I run the form directly from the form it will display in datsheet view which is what I want displayed but when I run it through double clicking the "DateOfAppt" field I get a totally different format. Attached are the 2 different formats I get. I also noticed that the query picks up records that are a day early including the date queried. Thanks for your help
Scheduling-1.JPG
Scheduling-2.JPG
0
 
mbizupCommented:
Change your code to explicitly open it as a datasheet:

docmd.openform "frmYourNewPopup", acformDS
0
 
mbizupCommented:
Regarding the filtering on the dates, go ahead and post your DB with the changes you've made and I'll take a look at it.
0
 
Chrisjack001Author Commented:
Attached is a copy of the database
Invoice-7-7-2001-91611.accdb
0
 
mbizupCommented:
The reason for the discrepancy between your original query and the new datasheet form is that the criteria for the new form uses the date exactly as it is seen in the field you are double-clicking, so there is only one sheduled appointment for that exact day and time.  Your original query just uses date (not date and time) as criteria.

If you just want to filter by the date (not the time), change the recordsource of your new form to this:

SELECT Scheduling.PatientName, Scheduling.RoomNumber, Scheduling.DateOfAppt, Scheduling.ApptTime, Scheduling.DateOut, Scheduling.ApptTimeOut, Scheduling.StudyID, Scheduling.Notes, Scheduling.SchedulingID
FROM Scheduling
WHERE (((Scheduling.DateOfAppt)=cdate(Format([Forms]![Scheduling-Add]![DateOfAppt],"mm/dd/yyyy"))) AND ((Scheduling.Cancel)=False));
0
 
Chrisjack001Author Commented:
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now