?
Solved

Linking field with query

Posted on 2011-10-05
17
Medium Priority
?
358 Views
Last Modified: 2012-05-12
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
Comment
Question by:Chrisjack001
  • 10
  • 7
17 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36920379
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36920396
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
 

Author Comment

by:Chrisjack001
ID: 36920635
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 36920724
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
 

Author Comment

by:Chrisjack001
ID: 36920727
Sorry. I am resending the database with the updated Switchboard form
Invoice-7-7-2001-91611.accdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36920762
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36920791
Looks like we cross posted.  Anyhow, do my previous two comments make sense?
0
 

Author Comment

by:Chrisjack001
ID: 36920851
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36921003
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
 

Author Comment

by:Chrisjack001
ID: 36921210
okay so what modified query should I use for this new form?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36921365
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
 

Author Comment

by:Chrisjack001
ID: 36927673
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36927741
Change your code to explicitly open it as a datasheet:

docmd.openform "frmYourNewPopup", acformDS
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36927747
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
 

Author Comment

by:Chrisjack001
ID: 36927801
Attached is a copy of the database
Invoice-7-7-2001-91611.accdb
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36928545
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
 

Author Closing Comment

by:Chrisjack001
ID: 36930963
Thank you very much
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question