Array values - would like to know position of new entry

[tbassngal]

What if I want to assign each value (the array??) to it's own new field? Say y(1) - which would be ProdQty to it's own field? And the next value using that same ProjectPOId would be y(2)... until the next ProjectPOId is visited. Maybe that's not the way to go. I have an unknown number of entries per string and I was thinking it might be nice to know which position the text value was:

50 (05/01/05), 100 (05/02/05), 150 (05/03/05), 175 (05/04/05)

Would be nice to have:

Position1 Position1a Position2 Position2a
ProdQty1 ProdDate1 ProdQty2 ProdDate2 etc.
50 05/01/05 100 05/02/05

Maybe I don't need that, maybe I can assign a number to each value in a new field as it is written to the new table...

Public Function fblnMakeTable_F_ProdSched() ' Tina 05/18/05 added function
MakeTbl_F_Data_ProductionSchedule
Dim a, i, t, x, y, z, strProdSched As String
Set DB = CurrentDb
strSQL = "Select * from R_DeliveryStatus"
Set RS = DB.OpenRecordset(strSQL, dbOpenDynaset)

RS.MoveFirst
Do While Not RS.EOF
strProdSched = RS.[prodnschedule].Value
x = RS.[FirstOfProjectPOID].Value
t = RS.[ProjectID].Value
If Not IsNull(strProdSched) Then
If Not (strProdSched Like "No New*") Then
If Not (strProdSched Like "Schedule*") Then

a = Split(Replace(strProdSched, " ", ""), ",")
'Debug.Print a
For Each i In a
y = Val(Left(i, InStr(i, "(") - 1))
'Debug.Print x
z = CDate(Mid(Left(i, Len(i) - 1), 1 + InStr(i, "(")))
'''z = CDate(Mid(i, 1 + InStr(i, "("), 6))
'Debug.Print x

'MsgBox "ProductionPOId=" & x & ", ProdQty=" & y & ", ProdDate=" & z
CurrentDb.Execute "Insert Into F_Data_ProductionSchedule (FirstOfProjectPOID, ProdQty, ProdDate, ProjectID) VALUES (" & x & ", '" & y & "', '" & z & "', '" & t & "');"
Next
End If
End If
End If
RS.MoveNext
Loop
RS.Close
End Function

 

[Zion7]

Using this method, would you not need an indeterminate amount of fields?
Maybe a list box?