Insert multiple records into child table using VBA (1 Viewer)

[brharrii]

I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept. The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.

This is what I have so far:

Private Sub cmdAddNotes_Click()
    Dim strSQL As String
    Dim RevisionDate As String
    Dim RevisionRevisedBy As String
    Dim RevisionDesc As String
    Dim RevisionAuthorizedBy As String
    Dim RevisionLabel As String
    
    RevisionDate = txtDateAdded
    RevisionRevisedBy = cboRecordCreator
    RevisionAuthorizedBy = cboRecordRequester
    RevisionDesc = txtNotes
    
    strSQL = "UPDATE (tblLabels LEFT JOIN tblRevisions ON tblLabels.LabelID = tblRevisions.RevLabel)" & _
            "SET tblRevisions.RevDesc ='" & RevisionDesc & "'" & _
            "WHERE (((tblRevisions.RevLabel) Is Null));"

When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.

Can anyone give me a nudge in the right direction?

Thanks!

 

[Isskint]

Hi brharrii

I might be missing something here, but think about what your SQL is saying/doing.

You are saying Update tblRevisions.RevDesc where tblRevisions.RevLabel is blank. When do you add the records to tblRevisions that would make RevLabel blank?

 

[brharrii]

Yeah, I was wondering if the problem might be the query itself. Since I'm creating a new record in the child table, would I need to Change this to an INSERT INTO query?

 

[Isskint]

Yes, change it to an INERT query. In general along these lines;

INSERT INTO [tblRevisions] (FIELDS LIST) VALUES (VALUE LIST);

 

[brharrii]

Ok, I think I can do that. I'm a little unsure how to handle the setting of the foreign key though. Do you know how I could set the Foreign Key field value on the child table to the primary key value of the parent table? Keeping in mind that I only want to add revision history notes for records that haven't had any yet. Does that make sense?

Thanks again for your help

 

[Isskint]

I would use a separate table for the import operation. Import the data into tblLabelsImport. Run an append query to add the data to tblLabels. Then run an append query to add data to tblRevisions where PK in tblLabels is not in ( use DCOUNT() ) FK of tblRevisions.

 

[brharrii]

oooh, I like the sound of that. I'll play with that idea. Thank you for the suggestion!

 

[brharrii]

Hi Isskint,

I've been playing with this Dcount thing you suggested, but I'm having troubles fleshing it out. Could you help me out with the syntax? I've tried a number of combinations but i keep getting an error message 424 (Object Required) when i run the code.

    Dim rs As DAO.Recordset
 
    Set rs = DCount("LabelID", "tblRevisions", RevLabel Is Null)

Parent Table:
tblLabels
- LabelID - Primary Key


Child table:
tblRevisions
- RevID - Primary Key
- RevLabel - Foreign Key to tblLabels



Thanks!

 

[Isskint]

You can not assign a number (from DCOUNT) to a recordset. Recordsets are sets of records.
In this context you could do something like

set rs = Currentdb.OpenRecordset("tblRevisions", dbOpenSnapshot)

the Dlookup is slightly wrong, try using

 DCount("LabelID", "tblRevisions", "IsNull(RevLabel)")

 

[vbaInet]

I'm not following this thread because I can see you're in capable hands. Just giving some suggestions re counting records:

Recordset:

set rs = Currentdb.OpenRecordset("SELECT Count(*) " & _
                                 "FROM [COLOR="Blue"]tblRevisions [/COLOR]" & _
                                 "WHERE [COLOR="blue"]LabelID [/COLOR]Is Null", dbOpenSnapshot)

Debug.Print rs(0)

DCount:

DCount("[COLOR="blue"]LabelID[/COLOR]", "[COLOR="blue"]tblRevisions[/COLOR]", "[COLOR="blue"]RevLabel[/COLOR] Is Null")

 

[brharrii]

Thanks for both of your responses.... I'm hoping I'm not too far in over my head.


When I run:

Private Sub Command8_Click()
    Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset("SELECT Count(*)" & _
                                     "FROM tblRevisions " & _
                                     "WHERE LabelID Is Null", dbOpenSnapshot)
 
    Debug.Print rs(0)
End Sub

I get an error message:

Too Few Parameters: Expected 1

Also, Where should I be putting the DCount statement? When I set it before or after the recordset like this:

Private Sub Command8_Click()
    Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset("tblRevisions", dbOpenSnapshot)
 
    [COLOR=red]DCount("LabelID", "tblRevisions", "IsNull(RevLabel)")[/COLOR]
 End Sub

It throws a compile error:

Expected: =

thank you again for your assistance with this.

 

[brharrii]

I don't know if it will be useful or not, but I thought I would include a stripped down version of the database I'm working on.


Thanks again folks!

 

[brharrii]

I had another thought on how I might go about this by calling a stored INSERT INTO procedure from vba that already had all of the parameters spelled out. This is how i've written the query:

INSERT INTO tblRevisions ( LabelID, RevDate, RevisedBy, RevAuthorizedBy, RevDesc )
 
SELECT tblLabels.LabelID
FROM tblLabels LEFT JOIN tblRevisions ON tblLabels.LabelID = tblRevisions.[LabelID]
WHERE (((tblRevisions.LabelID) Is Null)), 
[Forms]![frmRevisionUpdate].[txtDateAdded] AS Expr1, 
[Forms]![frmRevisionUpdate].[cboRecordCreator] AS Expr1, 
[Forms]![frmRevisionUpdate].[cboRecordRequester] AS Expr1, 
[Forms]![frmRevisionUpdate].[txtNotes] AS Expr1
 
FROM tblRevisions INNER JOIN tblLabels ON tblRevisions.LabelID = tblLabels.LabelID;

The SELECT statement returns the Primary Key values from the parent table that don't have a corresponding record in the child table (to be used as the foreign key in the child table (labelID)).

The form frmRevisionUpdate has 2 text boxes and 2 comboboxes so the user can specify the date the labels were added, who added them, who requested the addition, and a revision note (something like: "Label record created").

Once those 4 fields are populated i was hoping a query like this would be able to insert the primary keys needed from the parent table into the child table and add the user entered data to each of the newly created records.

The query as it is won't run though, it gives an error:

Number of query values and destination fields are not the same.

I've spent a few hours this morning already trying to flesh this idea out. My guess is that it doesn't like the complex SELECT statement I used for LabelID value. I was wondering if someone might be able to tell me if I'm barking up the wrong tree with this approach or something like this might work.

Thanks!

 

[vbaInet]

Like I said I wasn't following your thread, but since Isskint hasn't been around to reply yet, I'll give you some pointers. This would mean that I'll have to take you back to your first post

Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.

As soon as the record in the parent form is saved, you want to add revision history for that record right?

Create an APPEND query that will take the relevant parameters (i.e. a full reference to the controls), and execute the query using Currentdb.Execute. You'll need to execute it in the After Update event of the parent form.

By the way, since it's just a revision history, the subform containing the history is read-only right?

 

[TJPoorman]

One thing to be cautious about when using DCOUNT is when you start getting into multi-user environments, this become unstable. A better approach is to use currentdb.execute on the parent then retrieve the ID from that. Once you have the parent ID, you can run the INSERT statement on the child records. Like this:

Dim strQuery As String
Dim lngNewID as Long
Dim db as DAO.Database

strQuery = "INSERT INTO ParentTable(Field List) VALUES(Value List);"
Set db = CurrentDB
db.Execute(strQuery)
lngNewID = db.OpenRecordset("SELECT @@IDENTITY")(0)
Set db = Nothing

 

[brharrii]

Thanks vbaInet,

As soon as the record in the parent form is saved, you want to add revision history for that record right?

Correct

Create an APPEND query that will take the relevant parameters (i.e. a full reference to the controls), and execute the query using Currentdb.Execute. You'll need to execute it in the After Update event of the parent form.

I thought that was what I did with the query in my last post. Perhaps I'm confused on what an append query is. Is that any different between an Append and INSERT INTO query?

When I create an append query in access and then view the SQL code it gives me something like:

INSERT INTO
SELECT
FROM

That was the format I followed in my last post.

By the way, since it's just a revision history, the subform containing the history is read-only right?

It isn't currently, but I see why it makes sense to set it that way.

 

[vbaInet]

Append Query is an INSERT statement yes but the syntax is incorrect. The correct syntax is what TJPoorman gave.

Basically, do it directly in the query, don't add/join any other table, just add the Revisions table. All the values you need to insert into the Revisions table are on the parent form so make referenct to fields/textboxes holding the value. Keep the parent form open and run the query to see that it works. No need doing it in code yet.

 

[brharrii]

Hi TJPoorMan,

So I have a linked excel spreadsheet in my database which contains the records to be imported into the parent table plus records that are not ready to be imported. I have a form that uses the spreadsheet as a datasource and displays them in a listbox. I want the user to be able to multi select the records they want to upload from the excel spreadsheet and then push a button to have those records uploaded to my parent table tblLabels. How would I specify that in strQuery?

strQuery = "INSERT INTO tblLabels (LabelNumber, LabelDesc, LabelDesc2, LabelImg, LabelPlant, LabelCustomer, LabelTemplate, LabelGrade, LabelBaseProduct, LabelConservation, labelProductType)" & _

"VALUES [Selected records from me.List2?]"

Thanks!

 

[brharrii]

I think I found a way to import the records using rs.AddNew But db.Recordset("Select @@ IDENTITY")(0) is returning random columns from my listbox instead of the most recently created primary key in tblLabels. If I can store the primary key of the record I just created as a variable then I could use that in rs2 recordset to set the value of the foreign key.

Any thoughts?

Private Sub Command11_Click()
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim rs2         As DAO.Recordset
    Dim ctl         As Control
    Dim VarItem     As Variant
    Dim lngNewID    As Long
 
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblLabels", dbOpenDynaset, dbAppendOnly)
    Set rs2 = db.OpenRecordset("tblRevisions", dbOpenDynaset, dbAppendOnly)
    Set ctl = Me.List2
 
    For Each VarItem In ctl.ItemsSelected
        rs.AddNew
        rs!LabelNumber = ctl.Column(2, VarItem)
        rs!LabelDesc = ctl.Column(3, VarItem)
        rs!LabelDesc2 = ctl.Column(4, VarItem)
        rs!LabelImg = ctl.Column(5, VarItem)
        rs!LabelPlant = ctl.Column(6, VarItem)
        rs!LabelCustomer = ctl.Column(12, VarItem)
        rs!LabelTemplate = ctl.Column(7, VarItem)
        rs!LabelGrade = ctl.Column(11, VarItem)
        rs!LabelBaseProduct = ctl.Column(8, VarItem)
        rs!LabelConservation = ctl.Column(10, VarItem)
        rs!LabelProductType = ctl.Column(9, VarItem)
        rs.Update
 
 
        lngNewID = db.OpenRecordset("Select @@IDNETITY")(0)
 
        'rs2.AddNew
        'rs2!RevDate = Me.txtDateAdded
        'rs2!RevisedBy = Me.cboRecordCreator
        'rs2!RevDesc = Me.cboRecordRequester
        'rs2!RevAuthorizedBy = Me.txtNotes
        'rs2!LabelID = lngNewID
        'rs2.Update
 
        lngNewID = ""
 
    Next VarItem
End Sub

 

[brharrii]

Got it!

This is what I did:

Private Sub Command11_Click()
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim rs2         As DAO.Recordset
    Dim ctl         As Control
    Dim VarItem     As Variant
    Dim lngNewID    As Long
 
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblLabels", dbOpenDynaset, dbAppendOnly)
    Set rs2 = db.OpenRecordset("tblRevisions", dbOpenDynaset, dbAppendOnly)
    Set ctl = Me.List2
 
 
    For Each VarItem In ctl.ItemsSelected
        rs.AddNew
        rs!LabelNumber = Me.List2.Column(2, VarItem)
        rs!LabelDesc = Me.List2.Column(3, VarItem)
        rs!LabelDesc2 = Me.List2.Column(4, VarItem)
        rs!LabelImg = Me.List2.Column(5, VarItem)
        rs!LabelPlant = Me.List2.Column(6, VarItem)
        rs!LabelCustomer = Me.List2.Column(12, VarItem)
        rs!LabelTemplate = Me.List2.Column(7, VarItem)
        rs!LabelGrade = Me.List2.Column(11, VarItem)
        rs!LabelBaseProduct = Me.List2.Column(8, VarItem)
        rs!LabelConservation = Me.List2.Column(10, VarItem)
        rs!LabelProductType = Me.List2.Column(9, VarItem)
        lngNewID = rs("LabelID")
        rs.Update
        rs2.AddNew
        rs2!RevDate = Me.txtDateAdded
        rs2!RevisedBy = Me.cboRecordCreator
        rs2!RevDesc = Me.txtNotes
        rs2!RevAuthorizedBy = Me.cboRecordRequester
        rs2!LabelID = lngNewID
        rs2.Update
 
 
    Next VarItem
End Sub

Thank you all for your assistance!