Good day everyone,
I am trying to set up a loop so that I can append records from a
form.
The form has the controls of Dept, Shift, Assoc, and a Yes/No box
labeled COMBO for each device from 101 to 119.
This is a form that management has decided would work best for them.
Short from writting a section of code for each of the 19 devices how
can I have it loop with a Next or similiar function? basically I need
the Rs! = lines to have the 101 change to the value of X. I posted
what I though would work below but I ended up getting Me.XXXX as the
actual value in the table and not the form text box values.
below is my code so far.
Private Sub CloseSave_Click()
On Error GoTo Err_CloseSave_Click
Dim X As Integer
X = 101
For X = 101 To 119
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Usage List", dbOpenDynaset)
Rs.AddNew
Rs![ID] = "RP" & X
Rs![User ID] = Me.ENTRYUSER
Rs![Date] = Me.ENTRYDATE
Rs![Time] = Me.ENTRYTIME
Rs![Department Given] = Me.RP101DEPT
Rs![Shift Given] = Me.RP101SFT
Rs![User Given] = Me.RP101ASSOC
Rs![In Out] = Me.RP101COMBO
Rs.Update
Rs.Close
Set Rs = Nothing
Next X
DoCmd.Close
Exit_CloseSave_Click:
Exit Sub
Err_CloseSave_Click:
MsgBox Err.Description
Resume Exit_CloseSave_Click
End Sub
I tried putting this in and it did not work.
Dim RPDEPT As String
Dim RPSFT As String
Dim RPASSOC As String
Dim RPCOMBO As String
RPDEPT = "Me!RP" & X & "DEPT"
RPSFT = "Me!RP" & X & "SFT"
RPASS = "Me!RP" & X & "ASSOC"
RPCOMBO = "Me!RP" & X & "COMBO"
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Usage List", dbOpenDynaset)
Rs.AddNew
Rs![ID] = "RP" & X
Rs![User ID] = Me.ENTRYUSER
Rs![Date] = Me.ENTRYDATE
Rs![Time] = Me.ENTRYTIME
Rs![Department Given] = RPDEPT
Rs![Shift Given] = RPSFT
Rs![User Given] = RPASSOC
Rs![In Out] = RPCOMBO