Reference field from user input

[Aerowolf]

The code I have is below. The Shipment form has the field [LOC] which is one of a few set values (ie. RAW, SV, NB, or FB).These values represent the names of fields in the Parts table. Once the form is updated I want to use the value of [LOC] to reference that field in the Parts table. How do I do this?


Private Sub LOC_Enter()
If Left([PARTID], 8) = "WM-1601-" Or Left([PARTID], 8) = "WM-1540-" Then
If [PARTID] <> "WM-1601-NC" Then
[LOC] = "SV"
End If
Else
[LOC] = "RAW"
End If
End Sub

Private Sub Form_AfterUpdate()
DoCmd.OpenForm "frmParts", , , , acFormEdit
DoCmd.FindRecord [Forms]![frmShipment]![PARTID]

[Forms]![frmParts]![ value from LOC ] = [Forms]![frmParts]![ value from LOC ] - [Forms]![frmShipment]![QTY]
.
.
.
End Sub

 

[Remou]

I think this should work:
[Forms] = [Forms] - [Forms]![frmShipment]![QTY]

 

[MajP]

Here is the reason why. When you use brackets and pound notation no matter what goes in there it treats as a name. Therefore you can put reserved words, and spaces and VB can resolve it. Using () and . notation you can put in int index, string index, or a variable. What Remou shows should work, but I personally do not recommend mixing Bang and dot notations.

Forms("frmParts").controls(Me.LOC) = Forms("frmParts").controls(me.LOC) - forms("frmShipment").QTY