Updating table with code (1 Viewer)

[anb001]

On a form I have below code to update records in a table. It is the input from two checkboxes and two combos, which are shown 10 times each (each have same names, but with af 1-10 in the end, in order to use Counter). I use the For...Next method to go through each "row" of the controls, up to ten times. All that works fine.

What I'm wondering is, can I include some code in the same event procedure, that updates a few textboxes. They will not be named the same way as the other controls mentioned in the code, and as there is only one of each, I assume they can't be part of the For...Next method.

Private Sub cmdUpdateCovers_Click()
On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim answer As String
Dim answer2 As String
Dim counter
Dim covers As String

If IsNull(Me.cboTankID.Value) Then
    
    answer2 = MsgBox("Please select a tank before updating!", vbOKOnly)
    Exit Sub
    
Else

For counter = 1 To 10

If Not IsNull(Me("txtCoverID" & counter).Value) Then

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblType WHERE CoverID = '" & Me("txtCoverID" & counter) & "'")

With rs
        .Edit
        .Fields("OpenCovers") = Me("chkOpenCovers" & counter).Value
        .Fields("Blower") = Me("chkBlower" & counter).Value
        .Fields("Magnet") = Me("cboMagnet" & counter).Value
        .Fields("Reason") = Me("cboReason" & counter).Value
        .Update
    
End With

Set db = Nothing
Set rs = Nothing

End If

Next counter
End If
covers = MsgBox("Covers has been added to the 'Open Covers' list.", vbOKOnly, "Covers Added")
 
    Me.cboTankID.SetFocus

ErrorHandler:
Select Case Err
    Case 3021
        answer = MsgBox("No current record. No record updated.", vbOKOnly + vbApplicationModal, "Error deleting record")
        Exit Sub
End Select
    
    
    
End Sub

Thanks.
Anders

 

[Pat Hartman]

Your table seems to be almost properly normalized (one row for each of the "10" items) but your interface is not normalized and because of that, you are limited to 10 rows. I don't quite understand the schema but it seems to me that some fields need to move elsewhere.
But to answer your question, if the fields you are talking about go into the record that is being updated, they are in the wrong table. If all the values in all the rows of a "set" of data are the same, they don't belong there. They belong in a "parent" table.