Selecting and inserting records into the same table

[vassa]

Hi!
I have a table with a few columns. I am trying to write a code which would select certain records from the table an then append all these same records into the same table with only one column values changed (column: StageNumber).

The reason for this: I have a form with several subforms based on the same table (each subform automatically assigns certain stage number to all its records to prevent manual input of stage number). If all the information is similar or the same for different stages (different subforms in my case), I want to have a button which will copy all the similar data into all the subforms.

I would appreciate any help with this! Thank you

 

[vbaInet]

Lookup INSERT INTO. Create a query that will select all the records from that table and let's say we call it Query1. And let's say the new value is in a variable called NewValue (nb: you can refer to a control instead). The syntax will look something like:

INSERT INTO Table1 (Field1, Field2, StageNumber)
      SELECT Field1, Field2, " & NewValue & " AS qStageNumber 
      FROM Query1
      WHERE ID = " & txtbox1.value & ";"

 

[vassa]

vbaInet, thank you for the fast reply, I will give it a try and report back.

Lookup INSERT INTO. Create a query that will select all the records from that table and let's say we call it Query1. And let's say the new value is in a variable called NewValue (nb: you can refer to a control instead). The syntax will look something like:

INSERT INTO Table1 (Field1, Field2, StageNumber)
      SELECT Field1, Field2, " & NewValue & " AS qStageNumber 
      FROM Query1
      WHERE ID = " & txtbox1.value & ";"

 

[vassa]

Here is what I have so far. I can't figure out how to pass the variable (& NewValue &)to be placed by Append Query into the stage number. PLus I need to pass another variable for tblRA.[ProposalID]), right now for simplicity it is set to 1. I am getting error: "Number of query values and distination fields are not the same". Please help me to correct the syntax, I really don't know how to insert a variable into the SQL line of append query.



Dim NewValue As Integer
Dim InsertSQL As String

NewValue = 2

InsertSQL = "INSERT INTO tblRA ( [ProposalID], [ProductID], [ProductConc], [Stage], [Description], [Volume])"

InsertSQL = InsertSQL + " SELECT tblRA.[ProposalID], tblRA.[ProductID], tblRA.[ProductConc], " & NewValue & " AS tblRA.[Stage],"

InsertSQL = InsertSQL + " tblRA.[Description], tblRA.[Volume] FROM qryRaSql"

InsertSQL = InsertSQL + " WHERE (((tblRA.[ProposalID])=1) AND ((tblRA.[Stage])=1))"

DoCmd.RunSQL InsertSQL

 

[vassa]

I figured it out! Thank you for pointing me in the right direction.

InsertSQL = "INSERT INTO tblRA ( [ProposalID], [ProductID], [ProductConc], [Stage], [Description], [Volume])"

InsertSQL = InsertSQL + " SELECT tblRA.[ProposalID], tblRA.[ProductID], tblRA.[ProductConc], " & NewValue & ""

InsertSQL = InsertSQL + ", tblRA.[Description], tblRA.[Volume] FROM qryRaSql"

InsertSQL = InsertSQL + " WHERE (((tblRA.[ProposalID])=" & ProposalID & ") AND ((tblRA.[Stage])=1))"

 

[vbaInet]

Great!! Glad you got it working.

Although, I would write it like this:

InsertSQL = "INSERT INTO tblRA ([ProposalID], [ProductID], [ProductConc], [Stage], [Description], [Volume]) " & _
                 "SELECT qryRaSql.[ProposalID], qryRaSql.[ProductID], qryRaSql.[ProductConc], " & NewValue & ", " & _
                      "qryRaSql.[Description], qryRaSql.[Volume] " & _
                 "FROM qryRaSql " & _
                 "WHERE qryRaSql.[ProposalID] = " & ProposalID & " AND qryRaSql.[Stage] = 1;"

Instead of reassigning to the InsertSQL string.

 

[vassa]

Yes, it looks a lot better. Far more concise. I'll be using a continuation character more now. Reassigning to a string I learnt from the book' VBA for dummies'. Thanks once again for the help, vbaInet.

 

[vbaInet]

You're most welcome!