DAO Dummy Question

P

Paul Hammond

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
 
K

Ken Snell \(MVP\)

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
 
R

Roger Carlson

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
 
P

Paul Hammond

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

Paul
--
 
M

Marshall Barton

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
 
P

Paul Hammond

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
--
 
P

Paul Hammond

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
 
P

Paul Hammond

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
 
J

John W. Vinson

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]
 
Top