Update Data in Subform from an unbound text box in a tab control on the main form (1 Viewer)

[Pauline123]

Hi, hope someone can help.
I have a main form with a tab control.
In the tab control I have a subform.
I wish to update the data in the subform with values entered into unbound text boxes in the tab control on the main form.

Main Form Name: VehicleDetailsFormUPDATE - Primary Key: VehicleID
Tab Control Name: TabCtl152
Unbound Text Box Names: TxttxtCLStartDate / txtDriver / txtMileageTD
Subform Name:VehicleChecklistSubTB subform (source:VehicleChecklistSubTB)
Subform Fields: VehicleID, CLStartDate, Driver, MileageTD.

I have a command button on the tab control with the following code in the On Click Event:

Private Sub Command161_Click()

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from VehicleChecklistSubTB")

rec.AddNew
rec("CLStartDate") = Me.txtCLStartDate
rec("Driver") = Me.txtDriver
rec("MileageTD") = Me.txtMileageTD

rec.Update

Set rec = Nothing
Set db = Nothing


End Sub

This code just does not work - I believe its because I have not referenced the subform in the coding but so far I have not figured it out. Can someone help - feeling frazzled Pauline

 

[arnelgp]

do it in the subforms recordset:

Private Sub Command161_Click()

Dim rec As Recordset

Set rec = Me.Parent!VehicleChecklistSubTB!Form.RecordsetClone

rec.AddNew
rec("CLStartDate") = Me.txtCLStartDate
rec("Driver") = Me.txtDriver
rec("MileageTD") = Me.txtMileageTD


rec.Update
rec.Close
Set rec = Nothing


End Sub

 

[Pauline123]

Hi thanks for the quick response - just a quick question, when you say use the subform record set are you referring to the Record Source on the subform or would I have to put the command button onto the subform - sorry if I am a little bit slow to grasp this

 

[arnelgp]

i just edited my previous post but "e" is separating from RecodSetClone!?
sorry for the confusion, just leave your command button where it is right now, and replace your Click event with mine.

 

[Pauline123]

Thanks have tried the code out and I keep getting "Compile Error Method or Data Member not found" do you have any other suggestions !

 

[arnelgp]

ok, well use refer to the main form instead:

set rec = Forms!VehicleDetailsFormUPDATE!VehicleChecklistSubTB!Form.RecordsetClone

please check the above subform name, it is breaking should be:

VehicleChecklistSubTB

 

[Pauline123]

Hi have updated as you said but I am still getting the error message :-(

 

[Pauline123]

Sorry found a typo - updated and now get a new message " Run time error 2465 cant find field 'vehiclechecklistsubtb' referred to your the expression"

 

[arnelgp]

sorry,

set rec = Forms!VehicleDetailsFormUPDATE!VehicleChecklistSub TB.Form.RecordsetClone

 

[Pauline123]

Sorry still getting the runtime error

 

[arnelgp]

i am running out of options, here. can't reference a subform. is your main form a navigation form? can this be done:

set rec = [VehicleChecklistSubTB].Form.RecordsetClone

 

[Pauline123]

Hi Arnelgp sorry for the late reply but I managed to find a way around the problem, just had to keep it simple. Many thanks for all your help Pauline