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

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

Post by Shaw » Thu, 10 Mar 2011 07:26:44



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

 
 
 

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

Post by ralp » Thu, 10 Mar 2011 10:39:46




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

 
 
 

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

Hi.

I want to be able to move to particular records in a recordset using
the

        Recordset .FindFirst
        Recordset .FindLast
        Recordset .Seek
        Etc

methods, instead of using the 'DoCmd .....' code that the Access 97
wizards generate when I use navagation buttons on a Form.

My code finds the records I am interested in OK but I can't  get my
Form to move to this record.

Is the "RecordSource" property of a Form the same as a Recordset?
If so, is there a similar .Find etc set of methods? I can't find them.

If not, how do I get the Form to move to the record that my VBA code
has found?

Regards

John

2. date-format in SQL-builder ??

3. Looping through table to add records to other table

4. adodc1.recordset.addnew disables my check boxes??

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

6. pgsql/src/bin/pgaccess copyright

7. VB5 - Access Using Multiple Tables with 1 Form

8. SQL sort on text field

9. Problem adding record to blank Access Table using DAO

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

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

12. Access form won't close using VBA