asked on

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

mbizup

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

mbizup

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.

Chrisjack001

ASKER

Hi mbizup, using your theory how will that affect my query because that query is also ran through my switchboard under scheduling / Daily Query

mbizup

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.

Chrisjack001

ASKER

Sorry. I am resending the database with the updated Switchboard form
Invoice-7-7-2001-91611.accdb

mbizup

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.

mbizup

Looks like we cross posted. Anyhow, do my previous two comments make sense?

Chrisjack001

ASKER

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?

mbizup

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"

Chrisjack001

ASKER

okay so what modified query should I use for this new form?

mbizup

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"

Chrisjack001

ASKER

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

mbizup

Change your code to explicitly open it as a datasheet:

docmd.openform "frmYourNewPopup", acformDS

mbizup

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.

Chrisjack001

ASKER

Attached is a copy of the database
Invoice-7-7-2001-91611.accdb

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chrisjack001

ASKER

Thank you very much