Need help with recordsets

[TasCat09]

Hi All

I am hoping to present the user with a msgbox at the end of an sql update query that advises which records were added. In order to do it I need to be able to add each row in a recordset to an array variable. Can anybody explain why the first piece of code works for me (msgbox each recipe individullly) but the second is a type mismatch:

Working
Set db = CurrentDb
Set rs = db.OpenRecordset("RECIPES")
For i = 0 To rs.RecordCount - 1
MsgBox rs.Fields("RecipeName")
rs.MoveNext
Next i

Type mismatch
Set db = CurrentDb
Set rs = db.OpenRecordset("RECIPES")
For i = 0 To rs.RecordCount - 1
arry(1) = rs.Fields("RecipeName")
rs.MoveNext
Next i

 

[Frothingslosh]

Type mismatch means you're doing something like trying to assign text to a date or number field.

How is arry declared?

 

[sneuberg]

Also I suggest using a do while rather than for next. For one thing you can't count on rs.RecordCount being correct unless you do a rs.MoveLast which you better not do before you make sure the recordset isn't empty . So unless you need i for something it's just easier this way.

Set db = CurrentDb
Set rs = db.OpenRecordset("RECIPES")
Do While Not rs.EOF
    MsgBox rs.Fields("RecipeName")
    rs.MoveNext
Loop

 

[Frothingslosh]

Also I suggest using a do while rather than for next. For one thing you can't count on rs.RecordCount being correct unless you do a rs.MoveLast which you better not do before you make sure the recordset isn't empty . So unless you need i for something it's just easier this way.

Set db = CurrentDb
Set rs = db.OpenRecordset("RECIPES")
Do While Not rs.EOF
    MsgBox rs.Fields("RecipeName")
    rs.MoveNext
Loop

In the second one, he's using i as an array element. In that one, he'd be best off hiding the loop inside an 'If Not rs.EOF' or the like.

EDIT: Actually, he's using 1, but I would wager he actually meant to use i.

 

[jdraw]

A slightly different thought to the issue.

If you do a Select query (using the same criteria as your update query) you'll get a list of records to be "updated".
Then you change the Select sql to Update, to do the actual update.

You could save some fields of the select for display after the update.

Your code sample shows you reading the entire Recipe table? I'm not sure how that deals with only updated records?

 

[TasCat09]

Hi All

Thank you all for your advice on this problem. I ended up resolving it and the out of range issue that I got next by adding the number of elements in the array in parentheses.

In regard to the question from jdraw, I was just testing my ability to deal with recordsets originally. I have now used a more selective select query as the basis of my recordset.

I did stick with for i loop as I am using i for other purposes (ie. assigning recordset fields to arrays and adding to msgbox at the end.

Thanks again everyone.