Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 2nd, 2004, 02:10 PM
Authorized User
Join Date: Jun 2003
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default auto-update multiple records in subform

I posted a question about an appointment system I am building and the response I got told me my design was flawed.

I restarted with a new design but I run into one problem so I am posting all new:

I am building an appointment calendar. I have a main form called frmAppointmentDay and a subform called frmAppointmentDetails in 1 to Many relationship.

Thes subform DefaultView is continuous Form. The main form has these fields:
AppointmentDayID - AutoNumber PK
AppointmentDate, datatype Date/Time.

The subform has the following fields:
AppointmentDetailsID - AutoNumber and PK
AppointmentDayID - Number
Comments - memo
PatientID - Number (lookup from tblPatient)
AppointmentTime - Text

Since this is an appointment program each time a new appointment day is selected and saved in the main form I would like to automatically populate 67 records in frmAppointmentDetails. This 67 records arise because appointments are in increment of 10 minute intervals starting form 8:00 (am) to 7:00 (pm)

The AppointmentTime field of the first record record would be 8:00, the second record 8:10, third record 8:20; fourth record 8:30 etc. This would continue in increment of 10 minutes to 7:00 (pm).

I want it this way so that the user would have a visual layout of all the appointment slots for that day. So if an appointment is for 8:30 to 10:00 then the user would select the patient name for 8:30, 9:30 and 10:00, the patient name is displayed three times (three different records)in the subform.

If I can autopoputate the AppointmentTime slots each time a new Appointment date is selected then it would display all appointment slots and the user can simply assign patients to any vacany time slot.

How can I auto populate these 67 records as explained.

Note also I realise that is easier to make the AppointmentTime a text field rather than a Date/Time as I would not have to format the date to ShortTime.
I tried to be a clear as possible and hope I was able to do so.

Thanks for any help

Old February 8th, 2004, 12:19 AM
Authorized User
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

   Here is a possible solution. Use a cartesian product query when a new day is selected to auto populate the records. Here is the SQL statement I created and it works like a champ.

INSERT INTO tblAppointmentDateDetails ( AppointmentDayID, AppointmentTime )
SELECT tblAppointmentDates.AppointmentDayID, tblTime.Time
FROM tblTime, tblAppointmentDates
WHERE (((tblAppointmentDates.AppointmentDate)=#2/6/2004#));

tblTime is a table that contains a single text field. each record contains a time. Notice that in the from clause, there is no join. This is what makes it a cartesian product. Make sure you use the where clause to specify a date. Otherwise you will be creating 67 records for each day in the database.

Hope this helps,

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update field on all selected records in a subform S300570 Access 1 January 14th, 2008 02:19 PM
Update Multiple Records cancer10 Classic ASP Databases 0 October 25th, 2006 01:01 AM
update multiple records mateenmohd Classic ASP Basics 4 June 28th, 2004 03:38 AM
How Can I Update Multiple Records Lucy SQL Server ASP 3 March 18th, 2004 03:19 PM
update multiple records (solved) dhaywirex Classic ASP Databases 2 February 24th, 2004 12:23 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.