Form to Table with SQL Query

[Ellpee]

Okay, so, working on a form -- think of it as an order form -- where the header has a couple relevant items, serial number and item ID, and a subform contains a couple more items, which customers get the items and how many. EXAMPLE: buying 100 widgets, customer A gets 50, customer B gets 50. So subform MAY OR MAY NOT contain multiple rows. Next step is to move data from the form to a working table, using an SQL INSERT INTO query. So far so good, but in my test version (two header items, two subform items) the query asks me at two points for the "parameter value." Puzzling, because the other two items transfer fine. Only difference I can see is that they are numerical. And it's likely not a main form vs. subform thing, as of the two "rejected" items, one is header and one is subform. So not understanding why the query handles one item from the header just fine, and one from the subform just fine, but barfs at the other two. Mind you, if I answer the parameter prompts with the appropriate values everything goes as intended, but as they're already available from the form I don't get why I have to do that. SQL follows, and this is literally all there is, click a button, run this SQL, no other code involved. The items that fail are #2 and #3. All help welcome.

"INSERT INTO Table1 (Serial, MFName, MMTAcct, Qty) SELECT " & Me.txtSerial.Value & "," & Me.txtMFName.Value & "," & Me.[Test1Subform].Form!txtMMTAcct.Value & "," & Me.[Test1Subform].Form!txtQty.Value & ";"

[June7]

Why code and not BOUND form?

[Welshgasman]

Missed a paragraph

[Welshgasman]

Strings need single quotes?

[ssanfu]

From your description, it sounds like you should have 3 tables:
tblCustomers
tblOrders
tblOrderDetails

Then you would have frmOrders and sfrmOrderDetails.


Care to post your test version dB for analysis?


To me, it seems you have several issues to resolve........

[Ellpee]

So, many answers. First, I do not "own" the real table involved in this. It's part of a complex Access DB my client has been using for years before I ever showed up on the scene. So I don't want to muck around too much with their form and table designs, just building a workaround for what should be a simple problem: grab relevant data items off a form, export to an Excel file.
That being said, my question was really more about WTFO with the SQL. It grabs two data items just fine, but gives me the "Parameter Value?" popup for two others. In each case, as I said, in each instance one is from the header and one from the subform, and the only difference I can see is that numeric values are going thru fine, text values are not. Maybe when the item being grabbed is text my "," needs to be ",'"??????

[Micron]

Always assign your sql to a variable, then Debug.Print that variable. Then check the output in the immediate window (I put a break on the line after the debug so that code stops when the debug is output). If it looks correct, paste it into a new query in sql view and switch to datasheet view (that will prevent action query from actually running). If it doesn't balk, the syntax should be correct, otherwise it usually highlights the offending part in sql view.

If the datasheet view shows blank records, it still may be OK. This view sometimes shows the number of rows to be affected or added, not the actual changes that will be made. In your case I suspect it's because you have string values that are not delimited ( 'string', or "string"). Maybe turn the debug off when finished with it.

[June7]

As already advised in post 4, text field parameters need apostrophe delimiters (date/time would use # character). Assuming MFName and MMTAcct are text fields:

"INSERT INTO Table1 (Serial, MFName, MMTAcct, Qty) SELECT " & Me.txtSerial.Value & ",'" & Me.txtMFName.Value & "','" & Me.[Test1Subform].Form!txtMMTAcct.Value & "'," & Me.[Test1Subform].Form!txtQty.Value

[Ellpee]

Welshgasman nailed it. Adding single quotes got it working. Thanks to all.