Update two fields on Subform (1 Viewer)

D

detrie

Registered User.

Local time

Yesterday, 21:34

Joined

Feb 9, 2006

Messages

113

• May 20, 2018

• #1

I'm trying to update two fields (NewJF and NewDe) of all records in a subform based on two comboboxes (cmbSenior and cmbDe)
Works fine with one field but I cant seem to get the syntax right for the second one.

Code:Copy to clipboard

Private Sub UPdate_Click()
Dim rst As DAO.Recordset
   Set rst = Me.fSubClassification.Form.RecordsetClone
   rst.MoveFirst
   Do While Not (rst.BOF Or rst.EOF)
      rst.Edit
      rst![NewJF] = Me![cmbSenior],[NewDe] = Me.[cmbDe]
      rst.UPdate
      rst.MoveNext
   Loop
   Me.Requery
   Set rst = Nothing

End Sub

 

June7

AWF VIP

Local time

Yesterday, 18:34

Joined

Mar 9, 2014

Messages

5,465

• May 20, 2018

• #2

rst!NewJF = Me.cmbSenior
rst!NewDe = Me.cmbDe

or instead of RecordsetClone and looping:

CurrentDb.Execute "UPDATE tablename SET NewJF = '" & Me.cmbSenior & "', NewDe='" & Me.cmbDe & "' WHERE <some criteria here>"

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 19:34

Joined

Aug 30, 2003

Messages

36,124

• May 20, 2018

• #3

Separate them:

rst![NewJF] = Me![cmbSenior]
rst![NewDe] = Me.[cmbDe]

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 10:34

Joined

May 7, 2009

Messages

19,231

• May 20, 2018

• #4

In addition to the above answers, you should first test if there are records in the subform as in the case where you are in blank recordset, before issuing .Movefirst:

If rst.RecordCount <> 0 Then rst.MoveFirst


2nd you need not Requery the Form since you are directly editing the Form's recordset. Any changes you made are immediately reflected.