DAO Dummy Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I just need to do a simple thing, look at the value of a field from a
recordset, run some SQL using that value, move to the next and repeat.
Here's what I've got so far.

Set rsZips = CurrentDb.OpenRecordset("SELECT ZIP from tblRTD GROUP BY ZIP")

Set fldZip = rsZips.Fields(0)
rsZips.MoveFirst
Do While rsZips.EOF = False
MsgBox fldZip
rsZips.MoveNext
If rsZips.EOF Then
Exit Do
End If
Loop

I know this is a piece of cake, but I'm getting nowhere on my own.

Thanks,

Paul
 
Sample code:

Dim dbs As DAO.Database
Dim rsZips As DAO.Recordset
Set dbs = CurrentDb
Set rsZips = dbs.OpenRecordset("SELECT ZIP from tblRTD GROUP BY ZIP")
With rsZips
If .EOF = False And .BOF = False Then
.MoveFirst
Do While .EOF = False
MsgBox .Fields(0).Value
.MoveNext
Loop
End If
End With
rsZips.Close
Set rsZips = Nothing
dbs.Close
Set dbs = Nothing
 
Well, I'd do it like this:

Dim db as DAO.Database
Dim rsZips as DAO. Recordset
Set rsZips = db.OpenRecordset("SELECT ZIP from tblRTD GROUP BY ZIP")

Do While Not rsZips.EOF
MsgBox rsZips.Fields(0)
rsZips.MoveNext
Loop

Although exactly what use this is, I don't know.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I'm getting the same error I have been getting all along. "Invalid use of
null" at the msgbox function.

Paul
--
 
Paul said:
I just need to do a simple thing, look at the value of a field from a
recordset, run some SQL using that value, move to the next and repeat.
Here's what I've got so far.

Set rsZips = CurrentDb.OpenRecordset("SELECT ZIP from tblRTD GROUP BY ZIP")

Set fldZip = rsZips.Fields(0)
rsZips.MoveFirst
Do While rsZips.EOF = False
MsgBox fldZip
rsZips.MoveNext
If rsZips.EOF Then
Exit Do
End If
Loop
Click to expand...


I think you want something more like:

Set rsZips = CurrentDb.OpenRecordset("SELECT ZIP from tblRTD
GROUP BY ZIP")

Do Until rsZips.EOF
fldZip = rsZips!ZIP
MsgBox fldZip
rsZips.MoveNext
Loop
 
Although exactly what use this is, I don't know.
Click to expand...

Actually I am going to build a script and loop through it using that value.
I'll dump the msgbox once I know I can capture it.

Paul
--
 
Barking up the wrong tree again. There actually was a null value in my
table which I didn't account for and caused the msgbox function to fail.

It's working now, even my clumsy original version.

Thanks for your help.

Paul
 
Barking up the wrong tree again. There actually was a null value in my
table which I didn't account for and caused the msgbox function to fail.

It's working now, even my clumsy original version.

Thanks to ALL for your help.

Paul
 
I'm getting the same error I have been getting all along. "Invalid use of
null" at the msgbox function.
Click to expand...

Well... if you'ld explained what the error was, perhaps someone could have
helped. <g>

You can't pass MsgBox a NULL value; it must be a text string. Try

Do While .EOF = False
MsgBox NZ(.Fields(0).Value, "Null Value in record")
.MoveNext
Loop


John W. Vinson [MVP]
 
Back
Top