How to move to next record in a select query

[lawdy]

I have to following code on a button:

pubLngProposalNo = [tbLngProposalNo] ' get the current proposal number
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT tblProposal.fLngProposalNo, tblProposal.fTxtCompany " _
& "From tblProposal " _


& "WHERE (((tblProposal.fLngProposalNo)>= " & pubLngProposalNo & "));"
Set r = CurrentDb.OpenRecordset(strSQL)
Debug.Print "fTxtCompany " & fTxtCompany

I can only reference the first record in the query results. How do I reference the other records in the query. I push a button on the form which it should get several proposal numbers and company names?

[ssanfu]

What are you doing with the code?
To go to the next record, use

r.MoveNext

Usually there is a loop of some kind to loop through the recordset.


If "fTxtCompany" is a field in the recordset "r", you would use

Debug.Print "fTxtCompany " & r!fTxtCompany

or

Debug.Print "fTxtCompany " & r.("fTxtCompany")

(this is from memory.....)

[orange]

You have to build a loop

pubLngProposalNo = [tbLngProposalNo]    '   get the current proposal number
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT tblProposal.fLngProposalNo, tblProposal.fTxtCompany " _
        & "From tblProposal " _
        & "WHERE (((tblProposal.fLngProposalNo)>= " & pubLngProposalNo & "));"
    Set r = CurrentDb.OpenRecordset(strSQL)

do while not rs.eof
    Debug.Print "fTxtCompany " &   rs!fTxtCompany
''''do stuff in here
rs.movenext
Loop

[lawdy]

That works great. Thanks very much