auto-populate multiple records in a sub form

[tim johnson]

I am NOT double posting but posted this question to
Queries when I intended to post it under this forum.

*******************

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

tim

 

[Bryan Reich [MSFT]]

Tim,
Well, the short answer if I understand where you're getting stuck is code
something as follows:

Sub PopulateNewDayDetails( day as Date, dayID as Integer )
dim t as date
dim sql as string
dim newTime as date
t = #8:00:00 AM#

while t <= #7:00:00 PM#
newTime = day + t
sql = "insert into appointmentdays values (" & dayID & ",,,'" &
newTime & "')"
' or as appropriate for your situation, you may want to format the
newTime.
Application.CurrentDb.Execute(sql)
t = t + #12:10:00 AM#
wend
end sub

If you need help getting to the point above (where you have the day value
and the dayID from teh database), let me know what exactly is hanging you up
and I'll see if I can help there.

 

[Bryan Reich [MSFT]]

I should have put
null
instead of empty commas in my SQL statement for my previous post...

 

[Bryan Reich [MSFT]]

ok, well my SQL has gotten crappy, so let me try again so that it actually
might WORK this time.

sql = "insert into appointmentdays (AppointmentDayID, Comments, PatientID,
AppointmentTime) values (" & dayID & ", null, null,'" & newTime & "')"
--
Bryan Reich
Microsoft Office
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm