Weird one regarding append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a Bill of Material database. One of the functions in
this is the use of an append query that takes the parts that were checked-off
in a subform and add them to the kit number. The record set for the
selection from the part list is [selected (Y/N), partnumber (text), quantity
(integer)] and the kit number record set is [kitnumber (text), partnumber
(text), quantity (text)]. I wanted the entered kit number to be added to the
front of all the selected parts from the part list, but the append query only
allows for appending from other tables, not from text boxes on forms. Can
anyone recommend a way to associate the kit number with all of the selected
record sets before appending the data to the kitnumber table? Any advice
provided will be guaranteed to put you much closer to Santa's "nice" list.
 
You can build an Append query statement as a string in your code, including
the literal value you want from the form.

It will end up looking like this:

Dim strSql As String
strSql = "INSERT INTO Table2 (KitNumber, PartNumber, ... ) SELECT """ &
Me.[Textbox1] & """ AS KitNumber, Table1.PartNumber, ...
dbEngine(0)(0).Execute strSql, dbFailOnError

If you are not sure how the SQL string should look, you can mock up an
append query, and then switch it to SQL View.
 
Thank you, Alan! I'll give this a shot, probably going the mock-up route
since my SQL is about as rusty as it gets! :)

Allen Browne said:
You can build an Append query statement as a string in your code, including
the literal value you want from the form.

It will end up looking like this:

Dim strSql As String
strSql = "INSERT INTO Table2 (KitNumber, PartNumber, ... ) SELECT """ &
Me.[Textbox1] & """ AS KitNumber, Table1.PartNumber, ...
dbEngine(0)(0).Execute strSql, dbFailOnError

If you are not sure how the SQL string should look, you can mock up an
append query, and then switch it to SQL View.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

J. Keggerlord said:
I'm trying to create a Bill of Material database. One of the functions in
this is the use of an append query that takes the parts that were
checked-off
in a subform and add them to the kit number. The record set for the
selection from the part list is [selected (Y/N), partnumber (text),
quantity
(integer)] and the kit number record set is [kitnumber (text), partnumber
(text), quantity (text)]. I wanted the entered kit number to be added to
the
front of all the selected parts from the part list, but the append query
only
allows for appending from other tables, not from text boxes on forms. Can
anyone recommend a way to associate the kit number with all of the
selected
record sets before appending the data to the kitnumber table? Any advice
provided will be guaranteed to put you much closer to Santa's "nice" list.
Click to expand...
Click to expand...
 
Back
Top