Populating tables from an Array

[Max]

Hi,
I am trying to populate fields in a table labeled Proc1 to Proc10. These
contain long integer values and the source can contain from 1 to 10
elements. The code to fill the array works fine.
I do not want 0 in the field. When I try the following it runs without
errors, but the fields are not filled. Any ideas.

Thanks in advance.

Max

Private Sub OpNote_Click()

Dim R As Recordset, prcId(10) As Long, InitI As Integer, rc As Integer,
i As Integer
Set R = Me.EntryProcedureBAPSSub.Form.RecordsetClone
If R.RecordCount = 0 Then
Exit Sub
End If
rc = R.RecordCount
R.MoveFirst

'Populate recordset to store procedure IDs. prcId is an array with 10
elements
For InitI = 0 To rc - 1
prcId(InitI) = R.[ProcedureID]
R.MoveNext
Next InitI

'Build procedure code variables and fill table.
Dim MyField As String
Dim rs As Recordset, dbs As Database

'Save data to PatientOpNote table

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("PatientOpNote")
With rs
.AddNew
.Update
.MoveLast
.Edit
.[PatientID] = Me.PatientID
.[EpisodeID] = Me.EpisodeID
.[OpDate] = Me.ProcedureDate
For i = 1 To rc
MyField = ".[Proc" & i & "]"
Debug.Print "Field = " & MyField
MyField = prcId(i - 1)
Next i
.Update
End With

The Immediate window displays:
Field = .[Proc1]
Field = .[Proc2]
Field = .[Proc3]
Field = .[Proc4]

If I use:
..[Proc1] = prcId(0)

then the filed is filled correctly.

 

[Marshall Barton]

I am trying to populate fields in a table labeled Proc1 to Proc10. These
contain long integer values and the source can contain from 1 to 10
elements. The code to fill the array works fine.
I do not want 0 in the field. When I try the following it runs without
errors, but the fields are not filled. Any ideas.

Private Sub OpNote_Click()

Dim R As Recordset, prcId(10) As Long, InitI As Integer, rc As Integer,
i As Integer
Set R = Me.EntryProcedureBAPSSub.Form.RecordsetClone
If R.RecordCount = 0 Then
Exit Sub
End If
rc = R.RecordCount
R.MoveFirst

'Populate recordset to store procedure IDs. prcId is an array with 10
elements
For InitI = 0 To rc - 1
prcId(InitI) = R.[ProcedureID]
R.MoveNext
Next InitI

'Build procedure code variables and fill table.
Dim MyField As String
Dim rs As Recordset, dbs As Database

'Save data to PatientOpNote table

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("PatientOpNote")
With rs
.AddNew
.Update
.MoveLast
.Edit
.[PatientID] = Me.PatientID
.[EpisodeID] = Me.EpisodeID
.[OpDate] = Me.ProcedureDate
For i = 1 To rc
MyField = ".[Proc" & i & "]"
Debug.Print "Field = " & MyField
MyField = prcId(i - 1)
Next i
.Update
End With

The Immediate window displays:
Field = .[Proc1]
Field = .[Proc2]
Field = .[Proc3]
Field = .[Proc4]

You've got some syntax wrong, since a field in the recordset
is a member of the recordset's Fields collection, it is
referenced with a !, not a dot.

To assign a value to a field named in a string, you can not
just use the string name by itself, the string has to be
used as an index into the Fields collection.

You also have several extra statements that may be confusing
things.

This should do what you want:

With rs
.AddNew
![PatientID] = Me.PatientID
![EpisodeID] = Me.EpisodeID
![OpDate] = Me.ProcedureDate
For i = 1 To rc
MyField = "Proc" & i
Debug.Print "Field = " & MyField
.Fields(MyField) = prcId(i - 1)
Next i
.Update
End With

 

[Max]

Thanks very much for your help.

Max

I am trying to populate fields in a table labeled Proc1 to Proc10. These
contain long integer values and the source can contain from 1 to 10
elements. The code to fill the array works fine.
I do not want 0 in the field. When I try the following it runs without
errors, but the fields are not filled. Any ideas.

Private Sub OpNote_Click()

Dim R As Recordset, prcId(10) As Long, InitI As Integer, rc As Integer,
i As Integer
Set R = Me.EntryProcedureBAPSSub.Form.RecordsetClone
If R.RecordCount = 0 Then
Exit Sub
End If
rc = R.RecordCount
R.MoveFirst

'Populate recordset to store procedure IDs. prcId is an array with 10
elements
For InitI = 0 To rc - 1
prcId(InitI) = R.[ProcedureID]
R.MoveNext
Next InitI

'Build procedure code variables and fill table.
Dim MyField As String
Dim rs As Recordset, dbs As Database

'Save data to PatientOpNote table

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("PatientOpNote")
With rs
.AddNew
.Update
.MoveLast
.Edit
.[PatientID] = Me.PatientID
.[EpisodeID] = Me.EpisodeID
.[OpDate] = Me.ProcedureDate
For i = 1 To rc
MyField = ".[Proc" & i & "]"
Debug.Print "Field = " & MyField
MyField = prcId(i - 1)
Next i
.Update
End With

The Immediate window displays:
Field = .[Proc1]
Field = .[Proc2]
Field = .[Proc3]
Field = .[Proc4]

You've got some syntax wrong, since a field in the recordset
is a member of the recordset's Fields collection, it is
referenced with a !, not a dot.

To assign a value to a field named in a string, you can not
just use the string name by itself, the string has to be
used as an index into the Fields collection.

You also have several extra statements that may be confusing
things.

This should do what you want:

With rs
.AddNew
![PatientID] = Me.PatientID
![EpisodeID] = Me.EpisodeID
![OpDate] = Me.ProcedureDate
For i = 1 To rc
MyField = "Proc" & i
Debug.Print "Field = " & MyField
.Fields(MyField) = prcId(i - 1)
Next i
.Update
End With