Open Specified records

[dan-cat]

Hello,
I have a form (frmCalender) with two fields - Date and DoctorId. I would like to be able a to open a second form (frmSlot) and only display the record that has a matching Date and DoctorId. In the underlying table for frmSlot - I have made the Date and DoctorId both primary keys and when this form is opened the Date and DoctorId data from frmCalender is pasted into frmSlot.
I am just struggling on linking the two forms by two fields as opposed to one. Is this possible at all?

 

[jimbrooking]

First of all, please note that Date is an Access reserved word. You should change the field name in your table(s). You might want to bookmark

http://support.microsoft.com/default.aspx?scid=KB;en-us;q109312

which enumerates all the reserved words. Assuming it's now called MyDate:

In the DoCmd.OpenForm you could include a Where clause:

DoCmd.OpenForm "frmSlot",acNormal,,"MyDate=#" & Me.MyDate _
& "# AND DoctorID = " & Me.DoctorID

This will restrict the data on frmSlot to those records where the date (MyDate) and DoctorID are as selected on the form where the code is executed. That's all you have to do - no pasting values in fields or anything else.

Note the "#" delimiter: this is normally what one would use in SQL to signify that the quantity is a date. You might have to fool with this if your date fields are strings.

Jim