ms access - Create Multiple Records in One Form. Only One Field Changes per Record

Question

Where I work we receive electronic meters from customers and try to solve the errors they have. We will receive 4-8 meters at a time that all have the same problem, same specs, same everything, the only thing different between each meter is the Serial Number. I want to be able to enter every serial number, and the common specs all in one form to create multiple records.

Here's a pic of what I have for the form. I was able to create records for just one serial number at a time, but I would like to do it all at once to make data entry quicker and easier.

Meter Entry Form

So summary, Multiple Meters, all identical specs, different serial numbers. I want to enter it all into a form and have multiple records created. Thanks for any help or insight you can provide me.

-Chris

Answer 1

You could bind a subform to the table that stores your meter records and then have some unbound fields on your main form that allows you to enter the information that would be repeated in your batch of records. You could also put another unbound text box on the main form to specify the number of records you want that will have this repeated information.

So in the mock-up below, you'd specify how many records you want (red box), e.g. 10 records:

Then you'd supply the data that would be repeated for these 10 records (blue boxes):

You'd then click a button that would create the number of records specified with the repeated information given:

It would then just be a case completing the unique serial number for each of the records in the batch you have generated.

Here's the VBA I used on the Add button:

Private Sub cmdAddRecords_Click()

    batchAdd Me.txtRecords
    Me.tblMeters_sub.Requery

End Sub

...and the batchAdd sub routine it calls:

Public Sub batchAdd(records As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblMeters")

    i = 1

    Do While i <= records

        rs.AddNew
        rs!SerialNumber = ""
        rs!MeterFirmware = Me.MeterFirmware
        rs!MeterCatalog = Me.MeterCatalog
        rs!Customer = Me.Customer
        rs!MeterKh = Me.MeterKh
        rs!MeterForm = Me.MeterForm
        rs!MeterType = Me.MeterType
        rs!MeterVoltage = Me.MeterVoltage
        rs.Update

        i = i + 1

    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Here's a link to the mock-up (if you want a closer look).

Update

In response to your query about whether the subform could be filtered so it doesn't just become a big list of all meters, you could add another field to the tblMeters table that will take the date and time that you added records to the table:

You'd then need to add another line to the batchAdd sub that will put the system time and date in this new field:

...

Do While i <= records

    rs.AddNew
    rs!SerialNumber = ""
    rs!MeterFirmware = Me.MeterFirmware
    rs!MeterCatalog = Me.MeterCatalog
    rs!Customer = Me.Customer
    rs!MeterKh = Me.MeterKh
    rs!MeterForm = Me.MeterForm
    rs!MeterType = Me.MeterType
    rs!MeterVoltage = Me.MeterVoltage
    rs!DateAdded = Now                  ' <-- HERE!
    rs.Update

    i = i + 1

Loop

...

You'll then need to change the subform's Record Source property (Design View > select subform > Property Sheet > Data tab > Record Source):

Put the following SQL in there:

SELECT TOP 15 tblMeters.SerialNumber, tblMeters.MeterFirmware, tblMeters.MeterCatalog, 
tblMeters.Customer, tblMeters.MeterType, tblMeters.MeterForm, tblMeters.MeterKh, 
tblMeters.MeterVoltage, tblMeters.DateAdded 
FROM tblMeters 
ORDER BY tblMeters.DateAdded DESC;

... which will order the records by the date/time field (most recent at the top) and then show only the first 15 of these records. If you want a different number of records change the TOP 15 bit to a different number of your choosing.

When you click "Add", your new batch of records should be added to the top of the list and the list should stay at a maximum of 15 records (or whatever number you specify in TOP ...)

Be aware that when I was testing this, clicking the "Add" button rapidly a few times seemed to cause the sql to not bother with the TOP ... filter, but as long there's like a second or more between each "Add" click it seemed to work fine.

Hope this helps.