Updating Records in Access Subform

[simkessy]

I have a form/subform. Im trying to set up the button code to add/update records in the subform. Add works fine but when I update it always updates the first record.

Here's my code for update:

Private Sub cmdAdd_Click()
If Me.roomName.Tag & "" = "" Then
    CurrentDb.Execute "INSERT INTO ROOMS(room)" & _
        " VALUES('" & Me.roomName & "')"
          subRoom.Form.Requery
Else
    Dim strSql As String
strSql = "UPDATE ROOMS " & _
" SET room='" & Me.roomName & "'" & _
" WHERE ROOM_ID=" & Me.subRoom.Form.Recordset.Fields("ROOM_ID")
Debug.Print strSql ' <- prints to Immediate window
CurrentDb.Execute strSql, dbFailOnError
End If

cmdReset_Click

subRoom.Form.Requery
End Sub

The subform has two attributes Room_ID (autonumber) and Room (text). The form has one textfield used to add new room names and the ID number just updates automatically. Im not sure how to ensure that the code selects the correct record. I tried implementing the same code used in my delete button which selects the correct record.

Here's the code for that:

Private Sub cmdDelete_Click()
If Not (Me.subRoom.Form.Recordset.EOF And Me.subRoom.Form.Recordset.BOF) Then
    If MsgBox("Confirm Deletion?", vbYesNo) = vbYes Then
        Dim strSql As String
        strSql = "DELETE FROM ROOMS" & _
           [B] " WHERE ROOM_ID=" & Me.subRoom.Form.Recordset.Fields("ROOM_ID")[/B]
        Debug.Print strSql ' <- prints to Immediate window
        CurrentDb.Execute strSql, dbFailOnError
    End If
End If
Me.subRoom.Form.Requery
End Sub

The SQL I get from the Immediate window is this:
UPDATE ROOMS SET room='CAT' WHERE ROOM_ID=1
Room Id isn't suppose to be 1 however.

 

[Pat Hartman]

I can't figure out what you are trying to do but I will try to explain why the approach is wrong.
When you reference a control on a subform (Me.subRoom.Form!ROOM_ID, you are referencing the CURRENT record which will be the first record in the subform. There is no way to reference other records using this method. You could open a recordset using the RecordsetClone and loop through the subform's records but I'm not sure this would help.

Please tell us why you are trying to do a bulk update on the subform's records.