Stuck in Loop

[msjeb007]

My loop code is stuck. It only returns the first record. What am I missing?

:banghead:

Dim rs As DAO.Recordset
Dim asReturn As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Parts A")
'rs.MoveFirst
If Not (rs.EOF) = True Then
    rs.MoveFirst
    Do Until rs.EOF
        asReturn = Mid(DLookup("doc_num_cd", "Parts A"), 2, 5)
        MsgBox "Substring is " & asReturn
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

 

[plog]

Not 100%, but I think your SQL is bad. When you use non-alphanumeric characters in names you must add brackets around them. If you have a table named "Parts A", to reference it in SQL, you need brackets like so:

[Parts A]

It would be best to omit that space. Further, do you have a Parts B and Parts C tables? That smells like a structure issue if so.

 

[msjeb007]

No there are no "Parts B" or "Parts C" tables. "Parts A" is a modification of the "Parts" table.
I tried the brackets and locked the database. Are you suggesting changing the table name to "Parts_A"?

 

[plog]

That would be a better name, otherwise you must use brackets.

 

[msjeb007]

No change in the result. Still only returns on the first record.

 

[MarkK]

These two lines of code do not use data from the recordset...

        asReturn = Mid(DLookup("doc_num_cd", "Parts A"), 2, 5)
        MsgBox "Substring is " & asReturn

That will return the same result for every iteration of the loop. You're just running the same DLookup() for each row in the recordset.
hth
Mark

 

[msjeb007]

Should I reference the recordset in my asReturn line?

 

[MarkK]

Should I reference the recordset in my asReturn line?

I don't know what you are trying to do. Are you trying to get data out of the recordset? If so, then yes, you should reference the recordset.
Mark

 

[msjeb007]

Referencing a recordset in dlookup

How do you reference a recordset within dlookup?

Here is my line

asReturn = Mid(DLookup("doc_num_cd", "Parts_A"), 2, 5)

I've already set the recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Parts_A")

 

[isladogs]

Re: Referencing a recordset in dlookup

AFAIK you can't - table or query only
See https://support.office.com/en-gb/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

Create and save a query with SQL "SELECT * FROM Parts_A"

 

[Mark_]

@OP,

You should look at how you use DLookup because you are not telling it WHICH record to look up. You are simply saying "Get me the first record you find". You are entirely omitting a criteria for DLookup.

 

[Cronk]

Re: Referencing a recordset in dlookup

How many records do you have in the recordset? If it is only one then

asReturn = Mid(DLookup("doc_num_cd", rst!Parts_A), 2, 5)

If there are many records what are you expecting in asReturn? Or is asReturn an array where you would loop through the recordset, storing the string for each record in each array element.

Not enough information provided as to what you want

 

[Cronk]

We have 2 threads on the same code from msjeb007.

Moderators informed.

 

[Acropolis]

You need to move to last record, then back to the first record to get it to work, below is how I normally do a loop on mine. As you're getting the data in the record set, then so long as the data you want is in there, you don't need to use a DLookup and can user rs("FIELDNAME")

Dim rs As DAO.Recordset
Dim asReturn As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Parts A")
'rs.MoveFirst
If Not rs.RecordCount = 0 Then
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        asReturn = Mid(rs("doc_num_cd"), 2, 5)
        MsgBox "Substring is " & asReturn
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

 

[jdraw]

Cronk,
I have attempted to merge the threads. Thanks.