Do Loop Code

  • Thread starter Thread starter Rosco
  • Start date Start date
R

Rosco

I hope someone can help me here. I have used versions of the code
below many times without a problem. In my current project, for some
reason, I can not get this to work.

The goal of this code is to build an include statement for a delete
query, based on all records in a given table (tblWLKey). The table
only contains 1 field.

I am getting hung up on the "dblWLKey = [WLKEY]" statement. I get a
Runtime error 2465 - "Microsoft Access can't find the field '|'
refered to in your expression".

The table opens, and the record count is correct, so I know the sub is
seeing the record set.

I can't seem to find my error.

Sub Test1()

Dim db As Database, rs As Recordset, dblWLKey() As Double,
Dim StrWLKey As String, Count As Integer, i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblWL_Keys", dbOpenTable)

Count = rs.RecordCount
i = 1

ReDim dblWLKey(Count)

Do While i <= Count
With rs
dblWLKey = [WLKEY]
StrWLKey = "IN(" & StrWLKey & "," & dblWLKey & ")"
Debug.Print StrWLKey
.MoveNext
i = i + 1
End With
Loop

End Sub


Any help is appreciated

Rosco
 
Assuming WLKEY is the name of a field in your table, try:

dblWLKey = ![WLKEY]

(in case it's not evident, I added an exclamation mark before the field
name)
 
Douglas J. Steele said:
Assuming WLKEY is the name of a field in your table, try:

dblWLKey = ![WLKEY]

(in case it's not evident, I added an exclamation mark before the field
name)

--
Doug Steele, Microsoft Access MVP



Rosco said:
I hope someone can help me here. I have used versions of the code
below many times without a problem. In my current project, for some
reason, I can not get this to work.

The goal of this code is to build an include statement for a delete
query, based on all records in a given table (tblWLKey). The table
only contains 1 field.

I am getting hung up on the "dblWLKey = [WLKEY]" statement. I get a
Runtime error 2465 - "Microsoft Access can't find the field '|'
refered to in your expression".

The table opens, and the record count is correct, so I know the sub is
seeing the record set.

I can't seem to find my error.

Sub Test1()

Dim db As Database, rs As Recordset, dblWLKey() As Double,
Dim StrWLKey As String, Count As Integer, i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblWL_Keys", dbOpenTable)

Count = rs.RecordCount
i = 1

ReDim dblWLKey(Count)

Do While i <= Count
With rs
dblWLKey = [WLKEY]
StrWLKey = "IN(" & StrWLKey & "," & dblWLKey & ")"
Debug.Print StrWLKey
.MoveNext
i = i + 1
End With
Loop

End Sub

Any help is appreciated

Rosco
Click to expand...
Click to expand...


Ron and Doug,

Thank you. Worked like a charm once I added dblWLKey's array reference
in the do loop.

I am curious though. I've never had to add the "!" before the field
name in this code before. Why in this instance do I need to?

Thanks

Rosco
 
Rosco said:
I hope someone can help me here. I have used versions of the code
below many times without a problem. In my current project, for some
reason, I can not get this to work.

The goal of this code is to build an include statement for a delete
query, based on all records in a given table (tblWLKey). The table
only contains 1 field.

I am getting hung up on the "dblWLKey = [WLKEY]" statement. I get a
Runtime error 2465 - "Microsoft Access can't find the field '|'
refered to in your expression".

The table opens, and the record count is correct, so I know the sub is
seeing the record set.

I can't seem to find my error.

Sub Test1()

Dim db As Database, rs As Recordset, dblWLKey() As Double,
Dim StrWLKey As String, Count As Integer, i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblWL_Keys", dbOpenTable)

Count = rs.RecordCount
i = 1

ReDim dblWLKey(Count)

Do While i <= Count
With rs
dblWLKey = [WLKEY]
StrWLKey = "IN(" & StrWLKey & "," & dblWLKey & ")"
Debug.Print StrWLKey
.MoveNext
i = i + 1
End With
Loop

End Sub
Click to expand...

Your next problem is going to be the constructed IN clause.
The way you have it now, it will end up with something like:
IN(IN(IN(,1.2),2.3),3.4)

To get it right, try it this way:
. . .
StrWLKey = StrWLKey & "," & dblWLKey
. . .
Loop
StrWLKey = "IN(" & Mid(StrWLKey, 2) & ")"
Debug.Print StrWLKey
Click to expand...

Ya, I know, I already took care of that.
Thanks for the post
Rosco
 
Back
Top