auto populate sub datasheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DSR - 28 Jan 2006 21:00 GMT

Can anyone tell me how to do this? I have a table (TableBuildings) that
I want to auto create data to its sub datasheet (TableDaysOfWeek)
whenever a new record is added to the parent table (one to many). The
data that I want to auto create is each day of the week (Mon - 1st
record, Tue - 2nd record, Wed 3rd record ... etc) each as its own
record. Each of these records will be unique based on an auto number ID
field in that table. Therefore each day will have its own sub datasheet
(in a one to many relationship). The reason I want to automate the
values in this table is because I don't want the user to have to
enter the days for every new record since there will be many entries. I
am thinking that I will accomplish all of this with an event on my form
the first table is populated with. Could someone please put me on the
right track on how to approach this?

Thanks Dave

Reply to this Message

cjb_kjb - 30 Jan 2006 23:55 GMT

Haven't tested this and it's off the top of my head but:-

It would seem the best place to do this is the after insert event on
the main form.

Dim DoWeek as adodb.recordset
Dim i as Integer

Dim DayArray(7) = as string

DayArray(1) = "Sunday"
DayArray(2) = "Monday"
' you can fill in the rest

set DoWeek = new adodb.recordset
DoWeek.open "Select * from TableDaysOfWeek",
currentproject.connection,adOpenKeyset, adLockOptimistic

with DoWeek
for i = 1 to 7
.Addnew
' dont have to wory about the autonumber key of DaysOfWeek Table
!BuildingId = me!BuildingId '(whatever is the key of
TableBuildings)
!Day = DayArray(i)
' fill in values of any other fields
.Update
next i
end with

'If you have days of week show in a subform you might need
SubformName.Requery (not sure about this without testing)

Reply to this Message