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. Updating current record used by a Form?- VBA in Access 97

2. Looping through table to add records to other table

3. How to Avoid adding BLANK Records to a table using a FORM

4. VB5 - Access Using Multiple Tables with 1 Form

5. Problem adding record to blank Access Table using DAO

6. Using ADO VC++ to add a new record to an Access table with one autonumber field

7. Adding record in one table from form with two tables

8. Access form won't close using VBA

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

10. deleting a record(dynaset) using vba in access

11. adding multiple rows in portal, using web form?


 
Powered by phpBB® Forum Software