Concatenation in Update Query

[johndohnal]

I would like to use concatenation of a form field with a string to be used as the Field and part of the Update To expressions of an update query. I can enter the Field name and the Update To manually, but I want them to be able to change when the drop-down selections in my form change.
For example, If “Quarantine” is selected in the “To” drop-down of my Receipt form, I’d like for the first Field name in the update query to be “QuarantineInv” which would be something like [forms]![frmReceipt]![To]&”Inv” and the Update To field would be [tblMaterialSpecifications].[QuarantineInv]+[forms]![frmReceipt]![Quantity]
But instead of [tblMaterialSpecifications].[QuarantineInv] in the Update To field, I want it to be something like
[tblMaterialSpecifications].[([forms]![frmReceipt]![To]&”Inv”)]
This doesn’t work, but can anyone give me a solution that will work? Any help will be greatly appreciated.

 

[David R]

What is the purpose behind storing the calculated value, versus just putting it in a query?

 

[johndohnal]

The calculated value must be stored to give a running inventory. The only significance of the calculated value is that it will be stored under a field name. I'm trying to use a concatenated field name to select another field name in which the calculated value will be stored.
Thanks,
John

 

[JHB]

Can't you create query/SQL string and execute it, in the after update event in the combo box in the form, (or do I misunderstand something)?

SqlString = ".... [tblMaterialSpecifications].[" & [forms]![frmReceipt]![To] & "Inv"] ...."

 

[johndohnal]

I tried the last solution and got no luck. I believe my problem is with syntax and incorporating a form field name concatenated with a literal string, and then using that as a field name in my update query. If I need to do that in two or more steps, that's fine, but I don't know what that would look like. Any help would be greatly appreciated.
Thanks
John D

 

[David R]

Post the SQL of the update query you tried.

 

[johndohnal]

Here is my original update query. This works, but I have to have an update query and hard code for each combination of "To" and "From". I'd like to simplify it by using the "To" and "From" selection on the Receipt Form to select the fields to be updated: in this example, QuarantineInv, by concatenating the "To" Form Field with the literal "Inv".

UPDATE tblMaterialSpecifications SET tblMaterialSpecifications.QuarantineInv = [tblMaterialSpecifications].[QuarantineInv]+[forms]![frmReceipt]![Quantity]
WHERE (((tblMaterialSpecifications.ID)=forms!frmReceipt!MaterialSupplyID) And ((forms!frmReceipt!From)="Vendor") And ((forms!frmReceipt!To)="Quarantine"));

Again, thanks for your time and effort.
John D