Looping Through Recordset

[namu]

Hello Guys,

I need some help on my vba code. What I am trying to do is add a new record to a subform. If there is only one record then the value is 100%. But if you add another record, then it should split the value depending on whatever percentage you assigned them. The value will be taken on another form basically.

This is the code i have at the moment. The thing is it applies the 100% on the first record and when i add another record it will assign the split value on the 2nd record but not the 1st one. Obviously the value of the 1st record remain to 100%.

Can please somebody help or guide me through. Thank you.

Private Sub Form_AfterInsert()
Dim myDB As Database
Set myDB = CurrentDb
Dim rst As Recordset
Set rst = myDB.OpenRecordset("tblLaborCost8")


If Me.Recordset.RecordCount = 1 Then
    Me.SplitPercent = 1
    
ElseIf Me.Recordset.RecordCount >= 2 Then
    
    rst.MoveFirst
    While Not rst.EOF
        If Me.JobTitleID = "Qualified CS Fitter - Expat" Or Me.JobTitleID = "Qualified CS Fitter - Local" Then
        Me.SplitPercent = Forms![ProjectDetails].Title08Split1

        ElseIf Me.JobTitleID = "Qualified CS Welder - Expat" Or Me.JobTitleID = "Qualified CS Welder - Local" Then
        Me.SplitPercent = Forms![ProjectDetails].Title08Split2
        End If
        
    rst.MoveNext
    Wend
End If
               
rst.Close

Set rst = Nothing
Set myDB = Nothing
End Sub

 

[stopher]

I'll explain what your code is doing.

You have assigned rst to tblLaborCost8. So when your loop starts, it will loop through every record in this table regardless of the records in your form.

Inside the loop you have statements that are updating values of the current record in the form. The current record is not changing (and is presumably the record just inserted), so this same form record will just keep getting updated with the same value for as many times as the loop runs.

To help you on your way, I think you need to set rst to the form's recordset, not a table:
Set rst = Me.Recordset

From then on you can replace me.recordset with rst which will look neater. But importantly the loop will now loop through the forms recordset.

Next you have to refer to values in the recordset as you loop through it. You can do it like this:
IF rst![my field name]="some value" then
rst![some other field name]="some other value" (or some form field value)
endif

hth

 

[namu]

Hi,

Thank you for pointing me in the right direction. I made some changes on my code.

1. If there's only 1 record, SplitPercent = 1 or 100%. It works fine.
2. If there's a additional record, assign the SplitPercent to whatever value based on Title08Split1 & Title08Split2. Note: There could be no more than 2 records that's why it is only splits into 2 values.

The code below works but only if i select the "Qualified CS Fitter - Expat" or "Qualified CS Fitter - Local" as the first record. And the second record would either be "Qualified CS Welder - Expat" Or "Qualified CS Welder - Local". That also works fine.

But if I reverse and i chose the "Qualified CS Welder - Expat" Or "Qualified CS Welder - Local" as the first record. The value of the SplitPercent remains 100% and the second record will not update the value of SplitPercent and becomes NULL.

Here's the code:

Private Sub Form_AfterInsert()
Dim myDB As Database
Set myDB = CurrentDb
Dim rst As Recordset
Set rst = Me.Recordset

If rst.RecordCount = 1 Then
    Me.SplitPercent = 1

ElseIf rst.RecordCount >= 2 Then

    rst.MoveFirst
    While Not rst.EOF
        If rst!JobTitleID = "Qualified CS Fitter - Expat" Or rst!JobTitleID = "Qualified CS Fitter - Local" Then
            If rst!SplitPercent = 1 Then
            rst.Edit
            rst!SplitPercent = Forms![ProjectDetails].Title08Split1
            rst.Update
            End If
        ElseIf rst!JobTitleID = "Qualified CS Welder - Expat" Or rst!JobTitleID = "Qualified CS Welder - Local" Then
            If IsNull(rst!SplitPercent) Then
            rst.Edit
            rst!SplitPercent = Forms![ProjectDetails].Title08Split2
            rst.Update
            End If
        End If

    rst.MoveNext
    Wend
End If
               
'rst.Close

Set rst = Nothing
Set myDB = Nothing
End Sub

Ok i think i'm good to go from here. I just figure out the right if statement.

Thank you so much for the help! Cheers.

 

[static]

Private Sub Form_AfterInsert()

   s = "Qualified CS Fitter - Local" '<--- replace with control value
   
   Const s1  As String = "fitter"
   Const s2  As String = "welder"
   
   'check which order to pass values and update record(s)
   
   IIf InStr(1, s, s1), update_record(s1, s2), update_record(s2, s1)

End Sub

Private Function update_record(s1 As String, s2 As String)

    Dim db As DAO.Database, sql As String
    Set db = CurrentDb
    
    sql = "update {0} set {1} = {2} where {3} like '*{4}*'"
    
    'update the other record first (s2) to see if it exists...
    
    db.Execute strfmt(sql, "tblLaborCost8", "SplitPercent", Forms![ProjectDetails].Title08Split2, "JobTitleID", s2)
    
    'if it was updated (RecordsAffected) it exists so update s1 to Title08Split1 , otherwise update it to 100%
    
    If db.RecordsAffected Then
        db.Execute strfmt(sql, "tblLaborCost8", "SplitPercent", Forms![ProjectDetails].Title08Split1, "JobTitleID", s1)
    Else
        db.Execute strfmt(sql, "tblLaborCost8", "SplitPercent", 100, "JobTitleID", s1)
    End If
    
End Function

Private Function strfmt(ByVal s As String, ParamArray rep())
    For i = 0 To UBound(rep)
        s = Replace(s, "{" & i & "}", rep(i))
    Next
    strfmt = s
End Function