Won't loop 1

[swtrader]

I'm trying to loop through all records on an open form until the last record "ZZZZZ", using a Select query and update query to update a different table based on fields on the selected record on a form. I am trying to run the module from a button on the form that lists the eSearchTerms. It won't loop.

???


Option Compare Database

'------------------------------------------------------------
' mcrGoToNextxSearchTerm
'
'------------------------------------------------------------
Function mcrGoToNextxSearchTerm()
Dim rs As DAO.Recordset
DoCmd.SetWarnings False
DoCmd.SelectObject acForm, "xSearchTerms", False


On Error GoTo mcrGoToNextxSearchTerm_Err
'DoCmd.SetWarnings False -- should this go here or above?
'DoCmd.SelectObject acForm, "xSearchTerms", False -- should this go here or above
'Go to next record on form
Forms![xSearchTerms]![txt_eSearchTerm].SetFocus
DoCmd.GoToRecord , "", acNext
'Does the value in txt_eSearchTerm = "ZZZZZZ"; if yes, exit; if not, proceed
Do Until Forms![xSearchTerms]![txt_eSearchTerm] = "ZZZZZ"
'Open query using select statement created from form field [eSearchTerm]
DoCmd.OpenQuery "Sheet3 Query1", acViewNormal, acReadOnly
DoCmd.OpenQuery "qryUPDATE_BankItems", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryUPDATE_BankItems"
DoCmd.Close acQuery, "Sheet3 Query1"
Loop
DoCmd.SetWarnings True

Exit Function

mcrGoToNextxSearchTerm_Exit:
Exit Function

mcrGoToNextxSearchTerm_Err:
MsgBox Error$
Resume mcrGoToNextxSearchTerm_Exit

End Function

 

[KenReay]

Do not understand if the form is bound the recordset you are trying to loop thru, but first tip, see the .recordsetclone property of the form, you probably need code something like

Dim Rs as Recordset

Set Rs = me.RecordsetClone
If rs.Recordcount > 0 then
do until rs.EOF
.... code in here to do whatever it is you are trying to do
rs.movenext
loop
end if

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[swtrader]

Thanks, Ken --

As you can tell, I haven't done much VBA coding. After a little reading, I began to see the error of my ways. Your input gets me closer to resolution.

thanks.

swt

swtrader
-- If you don't know where you're going, you'll always know when you're not there.