Populating tables from an Array

M

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.
 
M

Marshall Barton

Max said:
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]
Click to expand...

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
 
M

Max

Thanks very much for your help.

Max

Marshall Barton said:
Max said:
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]
Click to expand...

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Follow up to array question 4
User input from excel form not populating Access Database 3
Getting fields from DAO database and placing them in variables 2
Garbage in a string variable loaded from a field 2
returning an array from a function 3
Stubborn Array won't Populate from single ADODB.Connection Query 4
Object Variable or With Block Variable Not Set 3
Transfer data in a record from access to excel 1

Top