Working with recordset 1

[TTThio]

Hi...

I have a button in the form when I click, I want to display a message box with all the names listed which data comes from a table field.

What I'm doing:
set db = current db
set rs = db.openrecordset ("table&quot

I'm checking if it returns correct number of records through msgbox

msgbox rs.recordcount

It does return correctly, exp. 15

Yet, when trying to display the whole names list

msgbox rs![Name]

It only returns one record (first one as I set it to rs.movefirst)

The record navigation in the form also shows only one record available.

Can anybody help me how make the code work so that I can see the whole records of that name field?

Thanks ahead!

Tin Tin

 

[RickSpr]

I'm not really sure what it is you want to do. But I can tell you that any recordset you create in code isn't going to be linked to the form's record source in a meaningful way. When you open your recordset, it is always positioned to the first record.

Are you using this message box as some kind of debugging aid? I hope so; this is a poor way of displaying ordinary data to a user.

If it's a debugging aid, I guess you're trying to display the name field from the record that's currently being shown on the form, right? You can make this work by changing the first argument to OpenRecordset. Replace "table" with a SQL statement that retrieves only the current record. Your form should have the primary key on it somewhere. Suppose it's called txtKey. Your SQL statement would then be:
"SELECT Name FROM table WHERE KeyField = '" & txtKey & "'"
(If your primary key is a number, change that to "Keyfield = " & txtKey.)
You would need to place your code in the Form_Current event procedure so that it executes for each record.

Really, though, it would be a lot simpler if you just include the Name field in your form's record source, temporarily, for debugging. You could then just display it on the form, temporarily.

I may have the idea all wrong, but this is the best I could do trying to understand what you want. Rick Sprague

 

[braindead2]

I think what he is trying to do is

dim rs as recordset, db as database
dim namestr as string
set db = currentdb
set rs = db.openrecordset("table&quot
rs.movefirst
do until rs.eof
namestr = rs!name & vbcrlf
rs.movenext
loop
rs.close
msgbox(namestr)

 

[braindead2]

correction to above should read
namestr = namestr & rs!name & vbcrlf
Sorry for error!

 

[TTThio]

Thanks a lot guys.

I'm not really using msgbox to deal with my app. I was just kind of playing around with a sample just to make sure later on in the real app, my recordset with return the right records.

Rick's suggestion for strSQl did return rs as filtered, exp 15 records.
And braindead2's code with the correction made it work. It's able to display the 15 records in one message box.

Thanks again.

Tin Tin

P.S. : Anyway, it's she...