Cycling trhough all records in a form

[billheath]

I have a command button on a form that performs a set of calculations and copies the result to one of the fields. I would like to perform the same functions on all of the records. Right now I have to manually page through each record and click the command button. I tried using the following Command to go to the next record but I must have the syntax incorrect:

DoCmd(GoToRecord, acDataForm, Stock_Datafrm, acNextRec) = 0

I have also tried:

DoCmd.GoToRecord( acDataForm, Stock_Datafrm, acNextRec)

and several other variations. Please, help and thank you

Bill

 

[dhookom]

I think you should use a recordset clone that you can loop through and perform the updates:

Private Sub cmdEditAll_Click()
    Dim rs As DAO.Recordset
    Dim strFieldName As String
    Dim strNewValue As String
    
    strFieldName = "Cell"
    strNewValue = "asdf"

    Set rs = Me.RecordsetClone
    rs.MoveFirst
    With rs
        Do Until .EOF
            .Edit
                .Fields(strFieldName) = strNewValue
            .Update
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

Thanks dhookom, I've Gotten intrigued by the Feature to move between records. I finally figured out that the correct syntax is:
DoCmd.GoToRecord acDataForm, "Stock_Datafrm", acNext, 1

I also tried a "wait" function that I found on another site to allow all of the data to be performed on before going to another record. However, the wait function that I used seems to have stopped all functioning in the VBA, so it doesn't work.

If you know of a way to pause one module while allowing the form and other modules to do their thing, I would appreciate you sharing it.

Thanks, Bill

 

[dhookom]

Please share what you mean by "set of calculations and copies the result to one of the fields". I think you are working to hard by using GoToRecord. Did you even consider the code I suggested?

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

yes, I did try your suggestion. The form runs 7 subroutines using a number of recordsets and importing data from 2 different excel files when a current form is opened using several tables. I'm sure an experienced Access programmer could figure it out in a simple fashion, but it is mindboggling to me. Since everything is working, I was hoping to simply page through the records and have the one subroutine work automatically.

Thanks

 

[dhookom]

I probably wouldn't do this by moving through a form's records. You might be able to use the form's timer event.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I agree with Duane that you probably should do this through recordsets. But if you want to cycle a form

Private Sub Form_Timer()
  If Not Me.NewRecord Then
    'so if you have a bunch of subroutines, maybe you can check
    'a field to see if the subs were complete.  In this example I just check a textbox
       If Me.txtChg = "Y" Then DoCmd.GoToRecord acDataForm, Me.Name, acNext
       Me.txtChg = ""
  End If
End Sub

 

[dhookom]

Regarding MajP's reply, you might want to set the timer interval to a value that allows the "set of calculations and copies the result to one of the fields" to run.

Duane
Hook'D on Access
MS Access MVP