Insert into Statement, using data from form control

[Cowboy_BeBa]

Hi

Ive got a form, user inputs a whole bunch of data and the data is stored into 2 different tables (using a form and subform), however i also need to copy a few fields from the subform into another table (as a new record)
the table i intend to insert this data into (tblAdjustments) has four fields (that im interested in)
Type, Reason, Quantity and Product (not the exact names)
Type and reason are both just straight strings and will be the same each time, regardless of what the other two variables are, so i need type to equal "Finished Goods" and Reason to equal Produced
further to that, the subfrom has 2 fields, batchPackedQTY (which will be quantity in the new table) and fgID (product in the new table

so i need an insert into statement that does this, ive tried
INSERT INTO tblAdjustment (adjType, adjReason, fgID, adjQTY)
("Finished Goods", "Produced", Forms!SubFormBatches.fgID, Forms!SubformBatches.batchPackedQTY)

however this did not work, anyone see what im doing wrong?

 

[Cowboy_BeBa]

actually this is the exact code im using

DoCmd.RunSQL INSERT INTO tblAdjustment (adjType, adjReason, fgID, adjQTY) ("Finished Goods", "Produced", Forms!SubFormBatches.fgID, Forms!SubformBatches.batchPackedQTY);

keeps returning a compiler error

 

[Cronk]

To refer to a control on a sub form the syntax is

Forms!YourFormName!YourSubFormControlName.Form.YourSubformControlName

 

[Cowboy_BeBa]

sorry cronk (and thanks as well ), i should have mentioned, the code is being executed from within the subform itself (it starts out on the after update event of one of the subforms fields), so wouldnt need that extended syntax (but thanks for the suggestion)

 

[DavidAtWork]

try this

DoCmd.RunSQL (INSERT INTO tblAdjustment (adjType, adjReason, fgID, adjQTY) VALUES('Finished Goods', 'Produced', '" + Str(Forms!SubFormBatches.fgID) + '", '" + Str(Forms!SubformBatches.batchPackedQTY) + "')")

This should work when both fgID and batchPackedQTY are Integer/Long data types
David

 

[Cronk]

If your code is being triggered from an event in your subform the right syntax then would be
Me. controlname.

The subform does not exist in the forms collection in its own right. The only way of accessing the value of the control, is by this, or as per my previous post.

 

[Cowboy_BeBa]

thanks guys, david i tried your code and cronk i incorporated me.controlname into it, however still not quite working, kept giving me compile errors, edned up rewriting the code a couple of times and managed to ditch the compiler errors, however getting
run time error 3075 syntax error in query expression
no matter what i seem to try i keep getting that error, heres the latest line im working on

<code>
DoCmd.RunSQL ("INSERT INTO tblAdjustment(adjType, adjReason, fgID, adjQTY) Values('Finished Goods', 'Produced', '" & Str(Me.fgID.Value) & "', " & Str(Me.batchPackQTY.Value) & "'):")

</code>

 

[BlueIshDan]

thanks guys, david i tried your code and cronk i incorporated me.controlname into it, however still not quite working, kept giving me compile errors, edned up rewriting the code a couple of times and managed to ditch the compiler errors, however getting
run time error 3075 syntax error in query expression
no matter what i seem to try i keep getting that error, heres the latest line im working on

<code>
DoCmd.RunSQL ("INSERT INTO tblAdjustment(adjType, adjReason, fgID, adjQTY) Values('Finished Goods', 'Produced', '" & Str(Me.fgID.Value) & "', '" & Str(Me.batchPackQTY.Value) & "'):")

</code>

This is all I could see wrong.

CurrentDb.Execute ("INSERT INTO tblAdjustment(adjType, adjReason, fgID, adjQTY) Values('Finished Goods', 'Produced', '" & Str(Me.fgID.Value) & "', [COLOR="Red"]'[/COLOR]" & Str(Me.batchPackQTY.Value) & "')[COLOR="Red"];[/COLOR]")

 

[pr2-eugin]

If Qty is a Number you do not have to cast it to String using the Str function, Try this.

CurrentDb.Execute "INSERT INTO tblAdjustment(adjType, adjReason, fgID, adjQTY) Values " & _
                  "('Finished Goods', 'Produced', " & Forms!SubFormBatches.Form!fgID & ", " & Forms!SubformBatches.Form!batchPackedQTY & ");"

 

[BlueIshDan]

A lot of people will store their numbers as string because "It's easier to deal with"... lol

 

[Cowboy_BeBa]

awesome, thanks for the help guys, dan your code was the winner