以一种形式创建多条记录.每条记录只有一个字段更改 [英] Create Multiple Records in One Form. Only One Field Changes per Record

问题描述

在我工作的地方，我们从客户那里收到电子仪表，并尝试解决他们遇到的错误.我们将一次收到 4-8 个仪表，它们都有相同的问题、相同的规格、相同的一切，每个仪表之间唯一不同的是序列号.我希望能够以一种形式输入每个序列号和通用规格以创建多个记录.

这是我为表格准备的图片.我一次只能为一个序列号创建记录，但我想一次性完成所有操作，以便更快、更轻松地输入数据.

然后，您需要为这 10 条记录(蓝色框)提供重复的数据:

然后单击一个按钮，该按钮将创建指定数量的记录，并使用给定的重复信息:

然后就可以为您生成的批次中的每条记录填写唯一的序列号.

这是我在添加"按钮上使用的 VBA:

Private Sub cmdAddRecords_Click()批量添加我.txt记录Me.tblMeters_sub.Requery结束子

...以及它调用的 batchAdd 子例程:

Public Sub batchAdd(记录为整数)Dim db As DAO.DatabaseDim rs As DAO.RecordsetDim i 作为整数设置 db = CurrentDbSet rs = db.OpenRecordset("tblMeters")我 = 1做 While i <= 记录rs.AddNewrs!SerialNumber = "";rs!MeterFirmware = Me.MeterFirmwarers!MeterCatalog = Me.MeterCatalogrs!Customer = Me.Customerrs!MeterKh = Me.MeterKhrs!MeterForm = Me.MeterFormrs!MeterType = Me.MeterTypers!MeterVoltage = Me.MeterVoltagers.更新我 = 我 + 1环形rs.关闭设置 rs = 无设置 db = 无结束子

然后您需要在 batchAdd 子项中添加另一行，将系统时间和日期放在这个新字段中:

...做 While i <= 记录rs.AddNewrs!SerialNumber = "";rs!MeterFirmware = Me.MeterFirmwarers!MeterCatalog = Me.MeterCatalogrs!Customer = Me.Customerrs!MeterKh = Me.MeterKhrs!MeterForm = Me.MeterFormrs!MeterType = Me.MeterTypers!MeterVoltage = Me.MeterVoltagers!DateAdded = Now ' <-- 这里！rs.更新我 = 我 + 1环形...

然后您需要更改子窗体的记录源属性(设计视图 > 选择子窗体 > 属性表 > 数据选项卡 > 记录源):

将以下 SQL 放入其中:

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

... 它将按日期/时间字段(最近的在顶部)对记录进行排序，然后仅显示这些记录的前 15 条.如果您想要不同数量的记录，请将 TOP 15 位更改为您选择的不同数量.

当您单击添加"时，您的新记录批次应添加到列表顶部，并且列表应最多保留 15 条记录(或您在 TOP ...)

请注意，当我进行测试时，单击添加"按钮快速几次似乎会导致 sql 不理会 TOP ... 过滤器，但只要每个添加"过滤器之间有一秒或更长时间.单击它似乎工作正常.

希望这会有所帮助.

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

解决方案

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.

这篇关于以一种形式创建多条记录.每条记录只有一个字段更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！