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.
 
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!
Click to expand...

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?
 
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!
Click to expand...

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??
 
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
Click to expand...

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.
 
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
Click to expand...

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

Similar Threads

Insert into loop not working 3
Access 2007 - Cannot open current database 1
QpenRecordset 2
Counting of Records in a Before opening a Data Form 3
Deleting a recordset in Access database 13
Determine a recordcount from a query 2
Append loop working but causing duplicates - I'm lost 3
Definition of database variables 2

Top