loop query VBA

[Arrowx7]

Hi,
How do I loop over elements in a query?
what would the loop look like?

thanks in advance.

 

[Liv Manto]

dim db as dao.database
dim rst rst as dao.recordset

Set db = CurrentDb()
Set Rst = db.opentable("Select * from Mytable")



If Rst.RecordCount > 0 Then

Rst.MoveFirst

Do Until Rst.EOF
msgbox rst("MyFieldname")
Rst.MoveNext
Loop

else
Msgbox "THere are no records"
end if

Set Rst = nothing
Set db = nothing

 

[Arrowx7]

thanks a lot
one question:
how do I define the Query?
is it CurrentDb()

lets say my query is in [Queries]![myqueryname] or does it have to be a table?

It is an access query in the access file.

 

[Liv Manto]

cut and paste the sql syntax

 

[Arrowx7]

SELECT table2.name, table2.weight, table2.age, table2.height
FROM table2
WHERE (((table2.name)=[Forms]![myform]![name]) AND ((table2.Active)=-1));

-----------------
name is a text field in the form.
Thanks a lot for your help!

 

[Liv Manto]

I am not sure what you are trying to do but

say you have a form with a command button called command2
and a text box called TextboxwiththeName

You input a name on the textbox, then on click of the command button,
this will go through the records if there is a same name, and then it will display the name through a msgbox.



Private Sub Command2_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim currentnameonform As String
Dim sql As String

currentnameonform = Me.TextboxwiththeName


Set db = CurrentDb()

sql = "SELECT table2.name, table2.weight, table2.age, table2.height FROM table2 WHERE table2.name = '"
sql = sql & currentnameonform & "' AND table2.Active=-1"
MsgBox sql ' to check syntax


Set rst = db.OpenRecordset(sql)


If rst.RecordCount > 0 Then

rst.MoveFirst

Do Until rst.EOF
MsgBox rst("name")
rst.MoveNext
Loop

Else
MsgBox "THere are no records"
End If


End Sub

 

[Arrowx7]

great thanks!!
I don't know how to thank you man.
Thanks so much for your time and generous help.