Combining Two INSERT INTO Statements

[WyattR]

Hey guys,

First of all thanks for any help you can offer. I've searched this topic a little and found similar posts, but whatever I do always seems to yield an error.

I have two INSERT INTO statements that I would like to combine so that they insert into the same line on the same table. However, one uses a slightly different syntax than the other one and it is giving me trouble.

 DoCmd.RunSQL " INSERT INTO tblSubAssemblyInfo( PartID ) SELECT tblPartMain.ID FROM tblPartMain WHERE (((tblPartMain.[Select]) = True))"
DoCmd.RunSQL " INSERT INTO tblSubAssemblyInfo(ParentAssemblyID, Qty, IsAnAssembly, MyNotes) VALUES( '" & [Forms]![frmAssemblyParts]![Magic3] & "','1', 'False', 'Notes') "

.

Both lines run independantly, but insert onto two seperate lines in tblSubAssemblyInfo. Clearly I need to only run DoCmd.RunSQL once, but I am not sure how to phrase the proper code.



Thanks for any help that you can provide - I really appreciate it.

[JeroenMioch]

First off i think you need to Dim the strSQL as string.
Second of all you need to execute the strSQL in this way : docmd.RunSQL strSQL
And last but not least i think you need to execute both lines seperately.

This is an example from one of my db's :

Dim strSql As String
   Dim strPnummer As String
   strPnummer = Me.Pnummer.Value
   strSql = "update parkeerbeheer set Naam = Forms!Parkeerbeheer!naam.value where Parkeerbeheer.[Pnummer] ='" & strPnummer & "'"
   DoCmd.RunSQL strSql
   strSql = "update parkeerbeheer set letters = Forms!Parkeerbeheer!letters.value where Parkeerbeheer.[Pnummer] ='" & strPnummer & "'"
   DoCmd.RunSQL strSql

Let me know how that works for you

[WyattR]

Thanks for the reply!

I actually already had the line "Dim strSQL As String", I just forgot to include it in my sample code.

As for your second suggestion, it appears that you do not need to store the string you are inserting into strSQL and then do DoCmd.RunSQL - you can skip a line and just code DoCmd.RunSQL "update parkeerbeheer set Naam..." and it will work fine. Whether this is good coding practice or not, I'm not sure, but it works.

Unfortunately, executing both lines seperatly using the technique above makes the information added to the destination table appear on two lines. I need it to be only on one so that the rest of the program can read it from there and generate nice things. Although I've been told I can't have nice things....

Anyways, thanks for your suggestions - if anyone knows how to insert the data into tblSubAssemblyInfo on one line, that would be much appreciated .

[June7]

Try:

DoCmd.RunSQL "INSERT INTO tblSubAssemblyInfo(PartID, ParentAssemblyID, Qty, IsAnAssembly, MyNotes) SELECT ID, '" & [Forms]![frmAssemblyParts]![Magic3] & "' AS ParentAssemblyID, 1 AS Qty, 0 AS IsAnAssembly, 'Notes' AS MyNotes FROM tblPartMain WHERE [Select] = True)"

If the field ParentAssemblyID is a number type, remove the apostrophe delimiters, just as I did for the number 1 in Qty and I used 0 for the False value in IsAnAssembly, assuming it is a Yes/No field. If it is actually a text field then revert to the text 'False'.


@JeroenMioch, I am not sure how your code can work if the references to form are not concatenated. Looks like your UPDATE could be a single action:

DoCmd.RunSQL "UPDATE parkeerbeheer SET Naam='" & Me.naam & "', letters='" & Me.letters & "' WHERE Pnummer='" & Me.Pnummer & "'"

Is Pnummer a number or text field? I have no idea what kind of data letters could be for.


Using a variable to hold the sql string can help debug errors with the sql syntax, especially with very long, complex sql statements.

[WyattR]

Thanks June7!

The code you posted worked perfectly. Here is the final form, in case anyone has a similar situation:

DoCmd.RunSQL "INSERT INTO tblSubAssemblyInfo(PartID, ParentAssemblyID, Qty, IsAnAssembly, MyNotes) SELECT ID, " & [Forms]![frmAssemblyParts]![Magic3] & " AS ParentAssemblyID, 1 AS Qty, 'False' AS IsAnAssembly, 'Notes' AS MyNotes FROM tblPartMain WHERE [Select] = True"

ParentAssemblyID is a number, and IsAnAssembly is either true or false (I wrote that bit earlier on, so I have other parts of the database searching for the string "True" or "False" instead of a "yes/no" one or zero).

For anyone interested, this essentially combines code where a user selects which parts they want to add to a subTable based on check-boxes in the main table, and also adds more information to the line in the sub-table to classify it and make the rest of the program run smoothly.

Anyways, I really appreciate your help June7, because this was the last major hurdle in the project I'm working on. Have a good day!