Insert 2 new records in subform and autofill fields

[toast]

I would like to add 2 new records into a subform whenever a new record is created in the main form.

Ideally, I would also like to autofill some fields in those new records based on data entered into 2 fields of the main form (but I realise this may be a little too adventurous!).

The main form will have a date field entered and a day/night field set. I would like to use that information to autofill the start and end fields (in dd/mm/yy hh:nn format) of the subform as follows...
If day is set on main form:

• Subform start field = date 10:00
• Subform end field = date 22:00

If night is set on the main form:

• Subform start field = date 22:00
• Subform end field = date +1 day 10:00

I think I need to use VBA to achieve this, and from reading around I think I need to use the AfterInsert event for the first, and AfterUpdate for the second?

If anybody could provide any pointers for me to try I would be very grateful.

 

[PNGBill]

Your Sub form would be a Continous Form and the Data Source SQL / Query selects the data related to the controls on the main form.

When you Udate or Insert in the main form controls an Event just needs to Requery the Sub Form.

May just be one line of code.

 

[toast]

I've had a go at one part (when a new record is created in the main form, 2 new records are created in the subform and one field in each record filled with different data).

Although it appears to work, it does not seem elegant so I was wondering if there was a better way to have done this? I didn't try to loop because there are only 2 records and different data is required in each. There seems like a gratuitous use of .SetFocus but without them it kept overwriting the first record rather than adding a second.

[COLOR="Blue"]Private Sub[/COLOR] Form_AfterInsert()

[COLOR="Blue"]On Error GoTo[/COLOR] Err_Insert [COLOR="Green"]'initializes error handling[/COLOR]

Me.subformcontrol.Form.RecordSelectors = [COLOR="blue"]False[/COLOR] [COLOR="Green"]'hide record selector in subform[/COLOR]
Me.subformcontrol.Form.NavigationButtons = [COLOR="blue"]False[/COLOR] [COLOR="Green"]'disable navigation buttons[/COLOR]
Me.subformcontrol.Form.AllowAdditions = [COLOR="blue"]True[/COLOR] [COLOR="Green"]'enable adding records to subform[/COLOR]

Me.subformcontrol.SetFocus [COLOR="Green"]'sets the focus to subform[/COLOR]
Me.subformcontrol![Job].SetFocus [COLOR="Green"]'sets the focus to Job field in subform[/COLOR]
Me.subformcontrol![Job] = "Driver" [COLOR="Green"]'sets the first record's Job field as Driver[/COLOR]

Me.subformcontrol.SetFocus [COLOR="Green"]'sets the focus to subform[/COLOR]
Me.subformcontrol![Job].SetFocus [COLOR="Green"]'set the focus to Job field in subform[/COLOR]
DoCmd.GoToRecord , , acNewRec [COLOR="Green"]'creates new record in subform[/COLOR]
Me.subformcontrol![Job] = "Loader" [COLOR="Green"]'sets the second record's Job field as Loader[/COLOR]

Me.[ShiftDate].SetFocus [COLOR="Green"]'sets the focus to the main form's ShiftDate field[/COLOR]

Me.subformcontrol.Form.AllowAdditions = [COLOR="blue"]False[/COLOR] [COLOR="Green"]'disable adding records to subform[/COLOR]

Exit_Insert: [COLOR="Green"]'Label to resume after error[/COLOR]
[COLOR="blue"]Exit Sub[/COLOR] [COLOR="Green"]'exit before error handler[/COLOR]

Err_Insert: [COLOR="Green"]'Label to jump to on error[/COLOR]
MsgBox Err.Number & Err.Description [COLOR="Green"]'error box[/COLOR]
[COLOR="blue"]Resume[/COLOR] Exit_Insert [COLOR="Green"]'resumes after error[/COLOR]

[COLOR="blue"]End Sub[/COLOR]

 

[PNGBill]

If it works great but I haven't seen so many .SetFocus in my life

What is the code achieving apart from dancing around the screen ?

It is normal for a subform to look to the main form or even other subforms when deciding what data will be displayed.

If the task of the subform is to create two new records based on actions in the main form then you can have code that creates these records in the After Update event of one or more of the main form controls ie when you have entered data in the main form a record is created in a table.
The code could include a message box asking if the operator is happy to create such a record and wjen yes is clicked, the record is added.

At another point the subform will be visible or the data source of the subform will be assigned and the subform will appear wit the two records displayed.

If the records require different SQL, then have two subforms.

 

[toast]

Thanks for the reply.

The relevant part of the design for this form is:
tSHIFT = tracking the details of a workshift (the location, date, vehicle allocated)... entered on the main form
tEMPLOYEE = self explanatory
tCREW = the 2 people who worked each shift and the specific hours they worked individually... entered via the subform

Every entry in tSHIFT will have 2 employees, never more never less.

A best guess for the hours worked can be made from the tSHIFT data (the date, and whether it was a day shift or night shift). The plan is to use that to enter a standard shift duration, and the user will then have the ability to adjust those times individually in the subform.

If there is a better way to achieve those results, I'd love to hear any pointers. Or if there are any problems I may run into doing it this way.

 

[PNGBill]

If you have the data displayed as required - all good.

If you then want to be able to Edit Data in a sub form you would normally expect a Requery to be required have the main form and subform re display the data and reflect any changes.