Field names in For loop array??

[Guest]

My goal in the following code is to use the Array in the For Loop to generate
the field names by concatenate the numbers 1-4 at the end of the first part
of the field names
i.e. HiPathKey1, HiPathKey2, HiPathKey3... unfortunately the method I'm
using below does not seem to work...Error:"Item not found in this collection."
Is this possible?
If so, how?

Thank you in advance for you assistance.

CODE:

Set mydatabase = CurrentDb
Set rsttable = mydatabase.OpenRecordset("tbl_EworkforceEmployees")
Set rst = mydatabase.OpenRecordset("tbl_HiPathAgentKey")

Key = Array("1", "2", "3", "4")
rst.AddNew

For i = 0 To 3
With rst
rsttable.MoveFirst
Do While Not rsttable.EOF

Do While IsNull(rsttable!HiPathKey & Key(i))
rsttable.MoveNext
Loop

If rsttable.EOF = False Then
.AddNew
rst!EworkforceID = rsttable!EworkforceID
rst!HiPathAgentKey = rsttable!HiPathKey & Key(i)
rst!AgentIDSite = rsttable!HiPath_site_id_ & Key(i)
.Update
End If

If rsttable.EOF = False Then
rsttable.MoveNext
DoEvents
DoEvents
End If
Loop
End With
Next i

 

[Dirk Goldgar]

My goal in the following code is to use the Array in the For Loop to
generate the field names by concatenate the numbers 1-4 at the end of
the first part of the field names
i.e. HiPathKey1, HiPathKey2, HiPathKey3... unfortunately the method
I'm
using below does not seem to work...Error:"Item not found in this
collection." Is this possible?
If so, how?

Thank you in advance for you assistance.

CODE:

Set mydatabase = CurrentDb
Set rsttable = mydatabase.OpenRecordset("tbl_EworkforceEmployees")
Set rst = mydatabase.OpenRecordset("tbl_HiPathAgentKey")

Key = Array("1", "2", "3", "4")
rst.AddNew

For i = 0 To 3
With rst
rsttable.MoveFirst
Do While Not rsttable.EOF

Do While IsNull(rsttable!HiPathKey & Key(i))
rsttable.MoveNext
Loop

If rsttable.EOF = False Then
.AddNew
rst!EworkforceID = rsttable!EworkforceID
rst!HiPathAgentKey = rsttable!HiPathKey & Key(i)
rst!AgentIDSite = rsttable!HiPath_site_id_ & Key(i)
.Update
End If

If rsttable.EOF = False Then
rsttable.MoveNext
DoEvents
DoEvents
End If
Loop
End With
Next i

I'm not at all convinced that your overall logic is correct, but
ignoring that, the way to make the field reference you want is this:

rsttable.Fields("HiPathKey" & Key(i))

Incidentally, it doesn't look like you really need the Key() array. Why
not just loop i from 1 to 4:

For i = 1 To 4

.... and build your field references like this:

rsttable.Fields("HiPathKey" & i)

?

 

[Guest]

The Key array is not needed. You can just change your For statement to
For i = 1 to 4
And address the fields like this:
strKeyName = HiPathKey & Cstr(i)
Do While IsNull(rsttable.Fields(strKeyName))

Also, see some comments in your code below

My goal in the following code is to use the Array in the For Loop to generate
the field names by concatenate the numbers 1-4 at the end of the first part
of the field names
i.e. HiPathKey1, HiPathKey2, HiPathKey3... unfortunately the method I'm
using below does not seem to work...Error:"Item not found in this collection."
Is this possible?
If so, how?

Thank you in advance for you assistance.

CODE:

Set mydatabase = CurrentDb
Set rsttable = mydatabase.OpenRecordset("tbl_EworkforceEmployees")
Set rst = mydatabase.OpenRecordset("tbl_HiPathAgentKey")

Key = Array("1", "2", "3", "4")

You don't need the following line.

rst.AddNew

For i = 0 To 3
With rst
rsttable.MoveFirst
Do While Not rsttable.EOF

You should add a check for EOF here. It is possible to Hit EOF in ths loop
and cause an error

Do While IsNull(rsttable!HiPathKey & Key(i)) And Not rst.EOF
rsttable.MoveNext
Loop

If rsttable.EOF = False Then
.AddNew
rst!EworkforceID = rsttable!EworkforceID
rst!HiPathAgentKey = rsttable!HiPathKey & Key(i)
rst!AgentIDSite = rsttable!HiPath_site_id_ & Key(i)
.Update
End If

If rsttable.EOF = False Then
rsttable.MoveNext

Why 2 DoEvents? Seems unusual

 

[Guest]

Thank you.

The Key array is not needed. You can just change your For statement to
For i = 1 to 4
And address the fields like this:
strKeyName = HiPathKey & Cstr(i)
Do While IsNull(rsttable.Fields(strKeyName))

Also, see some comments in your code below



You don't need the following line.
You should add a check for EOF here. It is possible to Hit EOF in ths loop
and cause an error

Why 2 DoEvents? Seems unusual

 

[Guest]

I realize that my code is in poor condition which makes your criticizem that
much more valuable to me. Thanks again for your help I managed to make this
work for me.