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
Chrisjack001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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.
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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.
Chrisjack001Author Commented:
Sorry. I am resending the database with the updated Switchboard form
Invoice-7-7-2001-91611.accdb
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.
mbizupCommented:
Looks like we cross posted.  Anyhow, do my previous two comments make sense?
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?
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"
Chrisjack001Author Commented:
okay so what modified query should I use for this new form?
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"

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
mbizupCommented:
Change your code to explicitly open it as a datasheet:

docmd.openform "frmYourNewPopup", acformDS
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.
Chrisjack001Author Commented:
Attached is a copy of the database
Invoice-7-7-2001-91611.accdb
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));

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chrisjack001Author Commented:
Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.