Changing rec.AddNew process to add multiple records

[RSW]

Hello,

I have a database form that currently adds a new revision into a table for a unique part number and serial number combination, like this:

Private Sub cmd_Click()
            Dim rec as Recordset
            Set rec = CurrentDb.OpenRecordset("tblPartRevisionHistory", dbOpenDynaset)
            rec.AddNew
                rec("RevisionNumber") = Me![RevisionNumber]
                rec("Part") = Me![Part]
                rec("SerialNumber") = Me![SerialNumber]
            rec.Update
            Set rec = Nothing
End Sub

What I need the ability to do is add records for the revision for *all* of that part number, any serial number. But at this point, I don't know what serial numbers to add; the part/serial combinations are stored in a different table.

Does anyone know of an easy way I could accomplish this, preferably all in VBA, without making any changes to the current database structure?


Thanks in advance!

 

[jzwp22]

It sounds like we need to understand your current table structure first. Why would you need to add records for serial numbers that do not yet exist? Can there be only 1 serial number associated with a part/revision or many?

 

[RSW]

It sounds like we need to understand your current table structure first. Why would you need to add records for serial numbers that do not yet exist? Can there be only 1 serial number associated with a part/revision or many?

Hi there,

I want to create records in tblPartRevisionHistory (a more accurate name would be SerialRevisionHistory) for serial numbers that *do* exist...in the parts table.

There can be many serial numbers associated with a part/revision. For example, the parts table might look like this...

Part/Serial
AAA/1001
AAA/1002
AAA/1003
BBB/1001/
BBB/1002/
(There are a lot more columns than this, I am just simplifying as much as I can)

And tblPartRevisionHistory might look like this...

Part/Serial/Revision
AAA/1001/2
AAA/1001/1
AAA/1002/1

So, what I'd like the option to do, is have the option to add records to tblPartRevisionHistory for all serial numbers part AAA at revision 3. I have to somehow look at the first table to know which serial numbers (e.g. 1003, which is not in the second table at all) to add.

Does that make sense?

 

[jzwp22]

Logically speaking, your tables are not structured properly. From your post, you say that a part can have many revisions (one-to-many relationship) and each revision can have many serial numbers (another one-to-many relationship). If I were to structure that, this is how I would do it:


tblParts
-pkPartID primary key, autonumber
-PartNo

tblPartRevisions
-pkPartRevID primary key, autonumber
-fkPartID foreign key to tblParts
-rev

tblPartRevSerialNos
-pkPartRevSerialNoID primary key, autonumber
-fkPartRevID foreign key to tblPartRevisions
-SerialNo


In your first post, you mentioned not wanting to change the structure, so if I was forced to use the structure, then I would probably your code within a loop to populate the revision history table. You would have to provide the revision and the part number

dim rev as long
rev= YouProvideThisValue

Set rec = CurrentDb.OpenRecordset("tblPartRevisionHistory", dbOpenDynaset)

mySQL="Select serialno from tblPart where part=" & youprovidethepart#

open a recordset based on the above query (rec2)

do until rec2.eof

rec.AddNew
rec("RevisionNumber") = me!rev
rec("Part") = Me![Part]
rec("SerialNumber") = rec2!serialno
rec.Update


move next
Loop

 

[RSW]

Yep, I agree the tables aren't structured properly, but this database has been around for years and switching everything up would not be an easy task. I'm not against some quick and dirty programming for the time being

I understand your logic, but I'm not sure I get this part:

Set rec = CurrentDb.OpenRecordset("tblPartRevisionHistory", dbOpenDynaset)

mySQL="Select serialno from tblPart where part=" & youprovidethepart#

open a recordset based on the above query (rec2)

Is "mySQL" just a regular variable, or is that some special command?

Can I open a recordset using something like

Set rec2 = CurrentDb.OpenRecordset("mySQL", dbOpenDynaset)

or is it something different? How does it know where it is looking for the records...a table, query, etc?

Thanks for your help!

 

[jzwp22]

This command is as you originally had it; it opens the tblPartRevisionHistory that will accept the new records

Set rec = CurrentDb.OpenRecordset("tblPartRevisionHistory", dbOpenDynaset)

The mySQL is just a string variable to hold the SQL text of a query on which the second recordset will be based. The FROM clause indicates that the serial numbers will be pulled from the tblParts (you'll have to substitute your own table and field names). I assumed that you had a parts table based on your statement:

...There can be many serial numbers associated with a part/revision. For example, the parts table might look like this...

I think the command structure is similar to this: Set rec2 = CurrentDb.OpenRecordset("mySQL", dbOpenDynaset)

I usually use ADO not DAO, so I am not as familiar with the DAO command structure.

 

[RSW]

Thanks very much for your help. I'll try and get this working today.

 

[jzwp22]

You're welcome, glad to help out.