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

问题描述

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

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.

仪表输入表

因此，摘要，万用表，所有相同的规格，不同的序列号.我想将其全部输入一个表单并创建多个记录.感谢您提供的帮助或见识.

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.

-克里斯

推荐答案

您可以将子表单绑定到存储电表记录的表格，然后在主表单上具有一些未绑定的字段，以便您输入在您的记录批次中重复.您还可以在主窗体上放置另一个未绑定的文本框，以指定要包含此重复信息的记录数.

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.

因此，在下面的模型中，您可以指定所需的记录数(红色框)，例如10条记录:

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

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

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.

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

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

Private Sub cmdAddRecords_Click()

    batchAdd Me.txtRecords
    Me.tblMeters_sub.Requery

End Sub

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

...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).

更新

为响应有关是否可以过滤子表格的查询，它不只是成为所有仪表的大列表，您可以在 tblMeters 表中添加另一个字段，该字段将使用日期将记录添加到表的时间和时间:

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:

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

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):

在其中放置以下SQL:

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;

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

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

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

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 ...)

请注意，当我进行测试时，快速单击添加"按钮几次似乎会导致sql不再受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.

希望这会有所帮助.

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