Recordset issues, get a good record count but no field data. worked till 365

[homelessvet1]

Private Sub GENERATE01_Click()
On Error GoTo Err_GENERATE01_Click
Stop
Dim rs As DAO.Recordset


SQL = "SELECT * FROM query1"
' SQL = "SELECT * FROM m1tclcropped"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)

Stop
If rs.RecordCount = 0 Then Exit Sub
rs.MoveFirst
For XX = 1 To rs.RecordCount


txt1 = rs!ImlJobID '<-------no field found






'GET NEXT UNUSED MASTER
rs.MoveNext

Next XX

Exit_GENERATE01_Click:
Exit Sub


Err_GENERATE01_Click:
MsgBox Err.Description


Resume Exit_GENERATE01_Click

End Sub

[Welshgasman]

So show the sql for query1?

Have you tried walking through the code?

[homelessvet1]

Yes, every line. rs opens but but has no field data.

[Welshgasman]

No option explicit I assume?

[homelessvet1]

win 10 access 365
explain "no option explicit"

[Welshgasman]

Without option explicit, you can make up any old name variable without it being declared.

Big mistake.

[homelessvet1]

Yes, but my issue is "Why no fields attached to record. It reads 37,818 records , then exits"

[davegri]

Yes, but my issue is "Why no fields attached to record. It reads 37,818 records , then exits"

You're missing the point.

The 'Field not found' might be txt1. Option Explicit would catch that error at compile time as a syntax error.

[Micron]

All you're doing is looping over the records, assigning a rs field value to a textbox, then rinse and repeat. If there is a value in the last record, that's what you'll get in the textbox. In addition, if you're trying to populate a recordset you have to put the rs in edit mode and update the rs in the loop. You're not doing that either.
Probably best to explain what you want to happen rather than show what's not working. Unless I'm mistaken in my analysis I can't see how it ever did anything before, regardless of which version you used.

This is your problem because the line raises an error? <-------no field found
Your thread makes no mention of an error so I don't know what to make of that.

[homelessvet1]

error 3265
I stripped out everything but the read section until the data fields populated.
Once I can see data elements I will add the output file back in and convert the data to a new file.
this is just a data conversion program.

[homelessvet1]

Error 3265 - Item cannot be found in the collection corresponding to the requested name or ordinal. This error is usually caused by a field missing from the database. This is typically caused by the database upgrade scripts being run out of order or by trying to connect the workstation to the wrong database.May 20, 2016

[Micron]

I'd start by ensuring that ImlJobID is actually a field in your recordset. You could copy the sql, open a new query in sql view, paste it in, run it and check the fields. I'd also make sure that the field list in in query design view contains that field name. Sometimes the use of alias field names can throw you off.

[homelessvet1]

I have done that. The fields exist. 37,818 records. imljobid is there.


SELECT m1tclcropped.lmlTimecardID, m1tclcropped.lmlJobID
FROM m1tclcropped;

[Welshgasman]

You are not using that domain though?
You are using query1??????

Besides just writing a simple sql statement does not prove field exists???

[homelessvet1]

Actually, it does. Querys won't report what isn't there.
I've been writing code since 1980 and still can't prove a negative.