I have a form that has a field with Inventory On Hand called QOH. I have 30 fields, each one contains a daily requirement and there is one field for each day. The fields are D1 through D30. What I want to do is create a loop that deducts the requirements (D1 through D30) from the QOH until the QOH falls below zero. I am counting the number of days as it is looping y = y + 1 to come up with the number of days of coverage. I then will return the counted result into a field on the form
Private Sub PDS_AfterUpdate()
Dim w As Integer 'generate the appropriate field reference'
Dim y As Integer 'My counter (The Value) that I return to the form'
Dim z As Double 'Quantity On Hand QOH'
Dim a As String
Dim strFieldName As String
z = Me.P0 ' PO is the field that contains the QOH - Quantity On Hand
If z < 0 Then ' If the QOH is already below zero return a -1
y = -1
End If
If z >= 0 Then
Do Until z < 0
w = w + 1 'This is used to get the number to add to the field to get the correct field'
a = "D" & w ' I then add the number to the field which starts with D to get the field that I want
to pull the value from'
strFieldName = "me." & a
z = z - strFieldName '<--- This is where I am stuck how do I get the value from the field I am
referencing in order to subtract it from the QOH'
y = y + 1 'This is the counter that I return to the field in my form'
Loop
End If
Me.DOH = y ' the field that the result is passed to on the form'
End Sub
Try this:
Do Until z <= 0
w = w + 1
a = "D" & CStr(w)
z = z - Me(a).Value
y = y + 1
Loop