Add records from form

[Guest]

I want to add records through a form to two related tables (related one to
many on fldDateID) -- the primary table = tblDates (fldID, fldDate,
fldDescrip, fldFormat) -- the related table tblSchedule (fldDateID, fldTime,
fldPlayer ...).

I am using a form to create daily schedules (adding records to the two
tables). After setting a series of parameters that determines the values to
be written to the primary table to fldDate, fldDescrip, fldFormat other
selections determine how many records need to be created in the related
table. Referential Integrity is enforced between the two tables (Cascading
Updates and Deletes). It appears that the records get added to each table as
intended but there is no data being written to the fldDateID field in the
related table and of course without this data there is no relationship
between the data in the two tables.

I am usin a query that includes the necessary fields from both tables and
used the query as the recordset to open and addNew ... Update. How can I pass
the fldID value from the primary table to the related table?

Current code:

'Add record to the Dates Table (primary table)
rst.AddNew
rst("fldDate") = datAddDate
rst("fldDescription") = strDescrip
rst("fldFormat") = "Regular"
'Add records to the TeeTimes Table for the chosen date (related table)
Do While datFirstTee <= datLastTee 'That is 7:00 AM or 10:00 AM to 8:00 PM
rst.AddNew
rst("fldTeeTime") = datFirstTee
rst("fldStartHole") = 1
rst("fldDateID") = rst("fldID")
'the above line doesn't work - but it doesn't generate an error
rst.Update
datFirstTee = DateAdd("n", 7, datFirstTee)
If Minute(datFirstTee) = 14 Then
datFirstTee = DateAdd("n", 1, datFirstTee)
ElseIf Minute(datFirstTee) = 29 Then
datFirstTee = DateAdd("n", 1, datFirstTee)
ElseIf Minute(datFirstTee) = 44 Then
datFirstTee = DateAdd("n", 1, datFirstTee)
ElseIf Minute(datFirstTee) = 59 Then
datFirstTee = DateAdd("n", 1, datFirstTee)
End If
Loop

 

[PC Datasheet]

Rather than combining your two tables in a query, use a form/subform where
the main form is based on Tbldates and the subform is based on TblSchedule.
Be sure the LinkMaster and LinkChild properties are FldDateID. After you
create or select a record in the main form and then create a new record in
the subform, FldDateID from the main form will automatically be added to
TblSchedule.

 

[Guest]

Thanks for the help. Actually this form is really an administrative form to
generate the records that will be accessed and completed in a form-subform as
you have described. As I continued playing around with the code I got this to
work. Would you comment on this approach.

Dim intID As Integer 'Autonumber field value from Primary table

'Add record to the Dates Table
rst.AddNew
rst("fldDate") = datAddDate
rst("fldDescription") = strDescrip
rst("fldFormat") = "Regular"
intID = rst("fldID")
rst.Update
'Add records to the TeeTimes Table for the chosen date
'this loop executes only once to create a 7:00 AM slot then generates an
unspecified error

Do While datFirstTee <= datLastTee 'That is 7:00 AM or 10:00 AM to 8:00 PM
rst.AddNew
rst("fldTeeTime") = datFirstTee
rst("fldStartHole") = 1
rst("fldDateID") = intID
rst.Update
datFirstTee = DateAdd("n", 7, datFirstTee)
Loop


Thanks again,
Bob Mullen