open database query and loop thru records

R

Rhea

I want to open a database query and then loop thru the records when I click
on a command button. This what I have so far:

Dim dbsSecurity As Database
Dim qdfMailboxBB As QueryDef
Dim rstMissingSecurity As Recordset
Dim rstBBMailbox As Recordset
Dim strTechcode As String
Dim strMissingSL As String

Set dbsSecurity = OpenDatabase("Security.mdb")
Set qdfMailboxBB = dbsSecurity.QueryDefs("MailboxBB")
Set rstBBMailbox = qdfMailboxBB.OpenRecordset

Does that open the query and put the records into the recordset? Is that
what I should be doing or is there an easier way? I need to go thru each
record the query returns and then add a record to another table based on the
contents of each record. Thanks!
 
P

Pendragon

If you just need a recordset to loop through records and do something with
them, eliminate the QueryDefs part.

Dim dbsSecurity as Database
Dim rstMissingSecurity As Recordset
Dim rstBBMailbox As Recordset

Set dbsSecurity = OpenDatabase("Security.mdb")
Set rstBBMailbox = dbsSecurity.OpenRecordset("SELECT * FROM MailboxBB")

You can set your select statement as you would in building a query. If
MailboxBB is an existing query and gets the records you need, you can simply
have dbsSecurity.OpenRecordset("MailboxBB").

HTH.
 
J

John W. Vinson

I want to open a database query and then loop thru the records when I click
on a command button. This what I have so far:

Dim dbsSecurity As Database
Dim qdfMailboxBB As QueryDef
Dim rstMissingSecurity As Recordset
Dim rstBBMailbox As Recordset
Dim strTechcode As String
Dim strMissingSL As String

Set dbsSecurity = OpenDatabase("Security.mdb")
Set qdfMailboxBB = dbsSecurity.QueryDefs("MailboxBB")
Set rstBBMailbox = qdfMailboxBB.OpenRecordset

Does that open the query and put the records into the recordset? Is that
what I should be doing or is there an easier way? I need to go thru each
record the query returns and then add a record to another table based on the
contents of each record. Thanks!

If that's all you need to do, you probably don't need any code or recordsets
at all - just an Append query. But are you sure you aren't storing data
redundantly? What are the two tables, and what information are you adding?
 
R

Rhea

thanks. I guess the problem is really this error I get when I compile my
code as eliminating the querydef hasn't got rid of the error which is:
"Argument not optional" and then the cursor moves the word EOF, which I
thought was indicating where the error is occurring but I think not as I put
in a message box before that line and I don't get to it when I run. I have
this code on a command button on a form:

Private Sub Command0_Click()
Dim dbsSecurity As Database
Dim rstMissingSecurity As Recordset
Dim rstBBMailbox As Recordset
Dim strTechcode As String
Dim strMissingSL As String

Set dbsSecurity = OpenDatabase("Security.mdb")
Set rstBBMailbox = dbsSecurity.OpenRecordset("MailboxBB", dbOpenDynaset)
MsgBox (Str(rstBBMailbox.RecordCount))
With rstBBMailbox
Do While Not EOF
strTechcode = !techcode
etc,
etc,
etc
loop
end with
end sub

What is the problem??
 
J

John W. Vinson

thanks. I guess the problem is really this error I get when I compile my
code as eliminating the querydef hasn't got rid of the error which is:
"Argument not optional" and then the cursor moves the word EOF, which I
thought was indicating where the error is occurring but I think not as I put
in a message box before that line and I don't get to it when I run. I have
this code on a command button on a form:

Private Sub Command0_Click()
Dim dbsSecurity As Database
Dim rstMissingSecurity As Recordset
Dim rstBBMailbox As Recordset
Dim strTechcode As String
Dim strMissingSL As String

Set dbsSecurity = OpenDatabase("Security.mdb")
Set rstBBMailbox = dbsSecurity.OpenRecordset("MailboxBB", dbOpenDynaset)
MsgBox (Str(rstBBMailbox.RecordCount))
With rstBBMailbox
Do While Not EOF

EOF by itself is meaningless: what is it that you're at the end OF?

Make it either

Do While Not rstBBMailbox.EOF

or, since you're in a WITH block,

Do While Not .EOF

Note the dot.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top