Using Access VBA to add multiple records in a loop to a table from a form 
Author Message
 Using Access VBA to add multiple records in a loop to a table from a form

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



Sun, 25 Aug 2013 06:26:44 GMT  
 Using Access VBA to add multiple records in a loop to a table from a form


Quote:
>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.

Complete qualify the reference to the "Me" stuff using .Value, .Text,
etc, as appropriate for the control.

   RS!XXXX = Me.XXXX.Value
   RS!XXXX = Me.XXXX.Text

Probably because it is only a snippet - but I see you advancing 'x',
but not the object reference to to 'me'.

-ralph



Sun, 25 Aug 2013 09:39:46 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. add multiple records to a subform using VBA

2. Copy fields from record in table A to record in table B using VBA

3. newbie - adding records to a table using VBA

4. How to add a record to a table using VBA

5. word VBA won't let me add a new record to an access table

6. VB5 - Access Using Multiple Tables with 1 Form

7. ADO2.5, can't add new record to an empty table using record binding

8. Updating current record used by a Form?- VBA in Access 97

9. Add new Record across multiple tables??

10. need something like foreach record in table - add record to another table

11. asp-add records to access w/ loop

12. Cannot add record to Visual Foxpro table after deleting all records in table

 

 
Powered by phpBB® Forum Software