Loop using a command button

[dref]

I have a form with a combo for selecting years.
The form has a command button which if cliched should update table A with the year selected in the combo box.
Updates are for fields where there is no year in table A.
Any help?


Thanks

[pbaldy]

No loop necessary. Create an update query that uses the combo and has the appropriate criteria and run it from the button.

[Missinglinq]

Or, if you're not comfortable with writing Queries, you can loop through the RecordSet and fill in Records that have no year.

Here's a step-by-step, modified for your particular case, for doing this.

• Create a Command Button.
• Name it cmdUpdateYearField
• Place the code below in the button's OnClick event

Private Sub cmdUpdateYearField_Click()

If Nz(Me.cboYears, "") <> "" Then

Dim rs As DAO.Recordset
Dim UF_Rec As String

Set db = CurrentDb

Set rs = db.OpenRecordset("YourTableName")
   
   UF_Rec = Me!UniqueField

  Do While Not rs.EOF
  rs.Edit
  If Nz(rs!YearField, "") = "" Then
   rs!YearField = Me.cboYears
  End If
  rs.Update
  rs.MoveNext
  Loop
  rs.Close
  Set rs = Nothing
  
  Me.Requery

  Me.Recordset.FindFirst "[UniqueField] = '" & UF_Rec & "'"
 
Else
  MsgBox "You Must First Select a Year!"
  cboYears.SetFocus
  cboYears.Dropdown
  
End If

End Sub

You'll need to replace

• cboYears with the actual name of your 'years' Combobox
• YourTableName with the actual name of your underlying Table
• YearField with the actual name of this Field in your Table
• UniqueField with just that; a Field that is unique to a given Record, such as a Primary Key Field

If your UniqueField is defined as a Text Datatype, the above code will work.

If, on the other hand, your UniqueField is defined as a Number Datatype or an AutoNumber Datatype, the above code will not work!

You'll need to replace

Dim UF_Rec As String

with

Dim UF_Rec As Integer

and replace

Me.Recordset.FindFirst "[UniqueField] = '" & UF_Rec & "'"

with

Me.Recordset.FindFirst "[UniqueField] = " & UF_Rec

Linq ;0)>

[pbaldy]

The bigger the table the more horribly inefficient that method would be.

[Missinglinq]

That's probably true, but most people coming here for help, especially those using names like TableA, are not working on a level where they're dealing tens of thousands or millions of records! And it does work!

Linq ;0)>

[mediaad]

Thanks!!!!!!!!!!!!!!!!!!!!!!!!!