Help needed: Run function for each record in continuous form

[Saisaku]

Hi all

I hope one of you great people can help me with this. I searched this forum and google for days now, without finding a helpfull sollution.

The situation:

I have a main form where I have 3 sections. The form itself, a listbox and a subform (continuous form).

The form displays the data of a intervention, like date, begin hour (intHRStart) and end hour (intHRStop).
When I select firefighters from the listbox (multiple select) and verify with a command button, these people are stored in a different table. It also copies the begin and end hour to this table. This table (tblFF) is showed in the subform.

Because the firefighters work in diffrent shifts, this has to be filled in on the form. With a after update I call a function wich will calculate if the firefighter has worked in his shift or not. It also calculates how many minutes he has worked in or out of his shift, and rounds it to 15min parts ...

The problem

All this code works perfect. But I need two other things:
- When you verify the people, it have to run the mentioned code for each firefighter.
- When you change the data in the main form, tblFF is updated with the new data. but it have to calculate the worked hours again.

So to put is simply, I need a manner to run a function (code) for each record in this subform.

I hope someone can help me with this, I'm starting to pull out my hairs from frustration!
Thank you in advance
Kurt

 

[Fuse3k]

Hi Kurt,

You can do something like this. Just use a button on the main form or use the AfterUpdate event on the main form if you want to do this every time the mainform record is saved.

Dim rs As DAO.Recordset
Dim rsCount As Integer
Dim i As Integer

'replace "Subform" with the name of your subform
Set rs = Me.SubForm.Form.RecordsetClone

rsCount = rs.RecordCount

For i = 1 To rsCount
    'execute code for each record in the subform
Next

Hope this helps.

 

[Saisaku]

Hi

After a week on training, I finally could try your suggestion.

As soon as I converted the code to use this option, I get the error '7878' (Data has changed). I wished the data in my table has changed, but it isn't ...

The way the procedure follows ...
- After update event calls update query, saves record and calls code 1
- Code 1 figures which shift people are working and calls the specific code
- Specific code changes the data ...

I get the error in step 2 (Code 1) when I 'reset' the previous data ... This is the syntacs I use:

Forms![frmInterventies]![frmShift].Form![vrgUren0] = "0"

I guess that the problem start with the update query, but I can't find a proper solution. I searched the web for a bit, but I can't find this specific problem

Well, I'm back to searching ... Many thanks!

 

[Saisaku]

Stupid mistake of myself. I forgot to refresh the form first ...

Well, back to the first problem. With this the first record chaged itself correctly, but the other records do nothing ...

This is what I have:

Public Sub Update()

On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery "qryAanwezigUpdate"
DoCmd.RunCommand acCmdSaveRecord
Me.Form.Refresh

Dim rs As DAO.Recordset
Dim rsCount As Integer
Dim i As Integer

Set rs = Me.frmShift.Form.RecordsetClone

rsCount = rs.RecordCount
    Call Shift
For i = 1 To rsCount
    
Next

Me.Form.Refresh
DoCmd.SetWarnings True

ExitHandler:
    Exit Sub

ErrorHandler:
    Select Case Err
    Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
    End Select

End Sub

Many thanks!

 

[eyeseven]

UP same problem here

I need to update a recordset, because my table1 copying data from table2, and i need run an update for all record. so any changes from table2 will reflect to table 1.

please help

thanks!

Stupid mistake of myself. I forgot to refresh the form first ...

Well, back to the first problem. With this the first record chaged itself correctly, but the other records do nothing ...

This is what I have:

Public Sub Update()

On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery "qryAanwezigUpdate"
DoCmd.RunCommand acCmdSaveRecord
Me.Form.Refresh

Dim rs As DAO.Recordset
Dim rsCount As Integer
Dim i As Integer

Set rs = Me.frmShift.Form.RecordsetClone

rsCount = rs.RecordCount
    Call Shift
For i = 1 To rsCount
    
Next

Me.Form.Refresh
DoCmd.SetWarnings True

ExitHandler:
    Exit Sub

ErrorHandler:
    Select Case Err
    Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
    End Select

End Sub

Many thanks!