Only the first record it finds is being updated

[jwill]

Hello everyone,



I have an unbound form that adds code to my assoc_prod table. This code loops through the controls and takes part of the control name and adds it as a code in the table with other values from the form. This codes works great when adding new rows if not match is found but it only edits the first row when match is found, the rest of the rows it leaves untouched even though it acknowledges that it has found a match. Here is the code:

Set db = CurrentDb
    Set rs = db.OpenRecordset("assoc_prod_qry")
    Set rs_clone = rs.Clone
    var_dsu = Me.cred_dsu_cmb
    var_ap_date = Me.ap_date_txt

    For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox And ctl.Name <> "ap_date_txt" And ctl.Name <> "auditor_ID_txt" 'exclude these 2 controls
            If Right(ctl.Name, 4) = "_txt" And Not IsNull(ctl) Then '"_txt" must be @ the end of the name of the text box and the control can't be null
                var_act_control = Left((ctl.Name), Len(ctl.Name) - 4) 'trims the last 4 characters
                var_first_search = "assoc_id = '" & Me.assoc_ID_cmb & "' and code_id  = '" & var_act_control & "' and credited_dsu_id = '" & var_dsu & "' and ap_date = #" & Me.ap_date_txt & "#" 
        With rs_clone
        .FindFirst var_first_search 'search for existing record in assoc_prod_qry
        If .NoMatch Then 'add new record
            rs.AddNew
            rs!code_id = var_act_control
            rs!ap_date = Me.ap_date_txt
            rs!assoc_id = Me.assoc_ID_cmb
            rs!credited_dsu_id = Me.cred_dsu_cmb
            rs!qty_of_prod = ctl
            rs!assoc_entered = Forms!navigation_form.assoc_id_txt
            rs!entry_timestamp = Now()
            rs.Update
        Else
            'If found, update the qty of the unit of production on the row where the match was found
            rs.Edit
            rs!qty_of_prod = ctl.Value
            rs!assoc_modified = Forms!navigation_form.assoc_id_txt
            rs!modified_timestamp = Now()
            rs.Update
        End If
    End With
                
                ctl = Null
            End If
    End Select
    Next ctl
    rs_clone.Close
    rs.Close
    db.Close
    Set rs = Nothing
    Set rs_clone = Nothing
    MsgBox "Your entry has been submitted.", vbInformation

I'm curious as to why only the first line gets updated when it finds a match and leaves the other rows unchanged. Thanks in advance

[ItsMe]

This code looks familiar. Did you post this previously?

.Findfirst will locate the first record in the recordset. After you find the first matching record you will need to .Findnext. I am not sure why you are creating rs.clone.

Maybe
Set rs = db.OpenRecordset("assoc_prod_qry", dbopendynaset)

and forget the second recordset. No need for rs_clone = rs.Clone

with a dynaset type recordset, the .findfirst statement should move to the last record and then move to the first on its own.

You will then need to loop through the remaining records using .FindNext.

[jwill]

Hey ItsMe,

"Did you post this previously?" I did post this code before but had to tweak it a little because my form design changed, coincidentally you helped me on that one too .

"I am not sure why you are creating rs.clone." I was planning on using it then changed my mind and forgot to take it out

I have a question. Instead of using .findnext, do you think it would cause any unforeseen consequences if I just moved to the beginning of the recordset (checking to see if not already at the beginning of recordset) after every loop. It seems to have worked but I'm not sure if I have overlooked something. Thanks for the quick response too.

[jwill]

Here is the code now:

Dim db As dao.Database
Dim rs As dao.Recordset
Dim var_dsu
Dim var_ap_date
Dim var_first_search As String
Dim var_act_control As String
Dim ctl As Object

If IsNull(Me.assoc_ID_cmb) Then
    MsgBox "Please enter an associate name.", vbExclamation
    Exit Sub
ElseIf IsNull(Me.cred_dsu_cmb) Then
    MsgBox "Please enter a DSU.", vbExclamation
    Exit Sub
ElseIf IsNull(Me.ap_date_txt) Then
    MsgBox "Please enter a date.", vbExclamation
    Exit Sub
Else
    Set db = CurrentDb
    Set rs = db.OpenRecordset("assoc_prod_qry", dbOpenDynaset)
    var_dsu = Me.cred_dsu_cmb
    var_ap_date = Me.ap_date_txt
    For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox And ctl.Name <> "ap_date_txt" And ctl.Name <> "auditor_ID_txt"
            If Right(ctl.Name, 4) = "_txt" And Not IsNull(ctl) Then '"_txt" must be @ the end of the name of the text box and can't be null
                var_act_control = Left((ctl.Name), Len(ctl.Name) - 4)
                var_first_search = "assoc_id = '" & Me.assoc_ID_cmb & "' and code_id  = '" & var_act_control & "' and credited_dsu_id = '" & var_dsu & "' and ap_date = #" & Me.ap_date_txt & "#"
                If Not rs.BOF Then
                    rs.MoveFirst
                End If
                rs.FindFirst var_first_search
                    If rs.NoMatch Then 'add new record
                            rs.AddNew
                            rs!code_id = var_act_control
                            rs!ap_date = Me.ap_date_txt
                            rs!assoc_id = Me.assoc_ID_cmb
                            rs!credited_dsu_id = Me.cred_dsu_cmb
                            rs!qty_of_prod = ctl
                            rs!assoc_entered = Forms!navigation_form.assoc_id_txt
                            rs!entry_timestamp = Now()
                            rs.Update
                        Else
                            'If found, update the qty of the unit of production on the row where the match was found
                            rs.Edit
                            rs!qty_of_prod = ctl.Value
                            rs!assoc_modified = Forms!navigation_form.assoc_id_txt
                            rs!modified_timestamp = Now()
                            rs.Update
                        End If
               ctl = Null
                End If
            
    End Select
    Next ctl
    rs.Close
    db.Close
    Set rs = Nothing
    MsgBox "Your entry has been submitted.", vbInformation
End If

Exit_submit_cmd_Click:
    Exit Sub

err_handler:
    MsgBox "Error number " & Err.Number & ": " & Err.Description
    rs.Close
    db.Close
    Set rs = Nothing
    Resume Exit_submit_cmd_Click
End Sub

[ItsMe]

.........
I have a question. Instead of using .findnext, do you think it would cause any unforeseen consequences if I just moved to the beginning of the recordset (checking to see if not already at the beginning of recordset) after every loop. It seems to have worked but I'm not sure if I have overlooked something. Thanks for the quick response too.

If you are going to do it that way, you might as well use Dlookup. They way you are proposing would cause a trip to the data for each control you are looping through.

I noticed you switched from a table to a query in your DAO. Maybe you could take it a step further and place that query's SQL in a VBA string and then add the where criteria defined in your If Then Else statement. You could then take two trips to the data. Just add the var_first_search as a WHERE clause to the SQL string. Then open the DAO recordset using your concatenated string. No If then statement needed. Everything in the recordset gets a new record.

Then open another recorset based on the else clause. Same thing, only concatenate your SQL string with the appropriate WHERE clause.

THis will eliminate the FindFirst Findnext thing. Just use .Movefirst and .movenext until EOF

Another option may be to use the form's existing recordset and clone it. As long as the form has all the appropriate records....

[ssanfu]

I like looking at code, so i reviewed and made some changes to the code:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim var_dsu             '<<<  this is a variant?? Why isn't it a string??
    Dim var_ap_date As Date    '<<<< I added "As Date"
    Dim var_first_search As String
    Dim var_act_control As String
    Dim ctl As Control   '<<< changed from Object to Control

    If IsNull(Me.assoc_ID_cmb) Then
        MsgBox "Please enter an associate name.", vbExclamation
        Exit Sub
    ElseIf IsNull(Me.cred_dsu_cmb) Then
        MsgBox "Please enter a DSU.", vbExclamation
        Exit Sub
    ElseIf IsNull(Me.ap_date_txt) Then
        MsgBox "Please enter a date.", vbExclamation
        Exit Sub
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("assoc_prod_qry", dbOpenDynaset)
        var_dsu = Me.cred_dsu_cmb
        var_ap_date = Me.ap_date_txt
        For Each ctl In Me.Controls
            Select Case ctl.ControlType
                '   Case acTextBox And ctl.Name <> "ap_date_txt" And ctl.Name <> "auditor_ID_txt"   '<<< cannot do this See Help for proper syntax
                Case acTextBox
                    If ctl.Name <> "ap_date_txt" And ctl.Name <> "auditor_ID_txt" Then
                        If Right(ctl.Name, 4) = "_txt" And Not IsNull(ctl) Then    '"_txt" must be @ the end of the name of the text box and can't be null
                            var_act_control = Left((ctl.Name), Len(ctl.Name) - 4)
                            var_first_search = "assoc_id = '" & Me.assoc_ID_cmb & "' and code_id  = '" & var_act_control & "' and credited_dsu_id = '" & var_dsu & "' and ap_date = #" & var_ap_date & "#"
                            
                            'findfirst ALWAYS starts from the beginning of the rs
                            '                            If Not rs.BOF Then
                            '                                rs.MoveFirst
                            '                            End If
                            
                            rs.FindFirst var_first_search
                            If rs.NoMatch Then    'add new record
                                rs.AddNew
                                rs!code_id = var_act_control
                                rs!ap_date = Me.ap_date_txt
                                rs!assoc_id = Me.assoc_ID_cmb
                                rs!credited_dsu_id = Me.cred_dsu_cmb
                                rs!qty_of_prod = ctl.Value
                                rs!assoc_entered = Forms!navigation_form.assoc_id_txt
                                rs!entry_timestamp = Now()
                                rs.Update
                            Else
                                'If found, update the qty of the unit of production on the row where the match was found
                                rs.Edit
                                rs!qty_of_prod = ctl.Value
                                rs!assoc_modified = Forms!navigation_form.assoc_id_txt
                                rs!modified_timestamp = Now()
                                rs.Update
                            End If
                            '              ctl = Null
                        End If
                    End If
            End Select
        Next ctl
        
        rs.Close
        Set rs = Nothing
        '        db.Close    '<<< didn't open, so can't close it
        Set db = Nothing   '  <<< but you can destroy it
        
        MsgBox "Your entry has been submitted.", vbInformation
    End If

Exit_submit_cmd_Click:
    Exit Sub

err_handler:
    MsgBox "Error number " & Err.Number & ": " & Err.Description
    rs.Close
    db.Close
    Set rs = Nothing
    Resume Exit_submit_cmd_Click
End Sub

The main change was the "SELECT CASE" syntax was wrong. See Help.

but it only edits the first row when match is found, the rest of the rows it leaves untouched

I'm curious as to why only the first line gets updated when it finds a match and leaves the other rows unchanged.

If .NoMatch indicates record(s) found using .FindFirst, will there be more than one record that meets the criteria? If so, then you will have to add code to move to the next match (.FindNext) and update the fields.

If you expect one or more records would meet the .FindFirst criteria, you might think about using an update query to change all of the records at once, rather than using looping thru the records using .FindNext.


My $0.02.....
No warranty implied or expressed....
Your mileage may vary...
Valid until I blink......

[ItsMe]

Steve,

nice catch on the Select Case! I don't think .movenext will get the OP what they need if they are trying to match criteria. That is why I was suggesting to include the criteria within the recordset. In other words, I do not use movenext with findfirst. I use findnext with findifirst. I could be wrong, but that is how I remember it.

[ssanfu]

@ItsMe,

You are right, it should be .FindNext (I corrected it)
I got stuck in VBA recordset code ..duh
Thanks

[jwill]

If you are going to do it that way, you might as well use Dlookup. They way you are proposing would cause a trip to the data for each control you are looping through.

I noticed you switched from a table to a query in your DAO. Maybe you could take it a step further and place that query's SQL in a VBA string and then add the where criteria defined in your If Then Else statement. You could then take two trips to the data. Just add the var_first_search as a WHERE clause to the SQL string. Then open the DAO recordset using your concatenated string. No If then statement needed. Everything in the recordset gets a new record.

Then open another recorset based on the else clause. Same thing, only concatenate your SQL string with the appropriate WHERE clause.

Remember I'm still learning VBA and recordsets so this may be wrong... with that said I'm thinking that I have to take a trip to the data every time anyway since the comparison value doesn't get populated until the program starts looping through the controls (the controls contain the value I need). I understand creating the sql in VBA but if I create it outside the loop, it doesn't yet have the values I need which is represented by var_act_control.

[jwill]

If .NoMatch indicates record(s) found using .FindFirst, will there be more than one record that meets the criteria? If so, then you will have to add code to move to the next match (.FindNext) and update the fields.

If you expect one or more records would meet the .FindFirst criteria, you might think about using an update query to change all of the records at once, rather than using looping thru the records using .FindNext.

I was trying to use the .edit statement to prevent more than 1 record that meets that criteria. BTW, thanks for cleaning up my code for me. The form is unbound so I'm trying to "manually" update the table by using part of the control name. So the program searches all of the text boxes (except for the ones I indicated in the case statement), if they are not null (lots of them may be null), it takes a piece of the control name (the control name contains the value I'm looking for) and updates the table. I couldn't think of a way to do this all @ once. Thanks for your help so far.

[jwill]

BTW, sorry for the slow responses. Between work and home, I'm swamped.

[ItsMe]

..... I'm trying to "manually" update the table by using part of the control name. .... are not null (lots of them may be null), it takes a piece of the control name...... and updates the table.......

This sounds like a bad way to manage your data. When I get a chance I will build a sample unbound form that updates a table via DAO.

[jwill]

A little background on the db. Its a production tracking database that has to track how many of a particular task an associate performs per day. Each task has a specific amount of time that the associate has to perform that task (task codes and timing for the tasks are store in prod_codes table). In order to keep the tables normalized, I made the form unbound and tried to find a way to add the data to the table (actual production data stored in assoc_prod). I have all of the possible task codes stored in a table already (prod_codes table) but I also used the codes in the naming convention for the controls (probably not the greatest idea). It seemed like it was easy to parse the control name for the code and put it in the table......anyway I'll look forward to the db and thanks for helping me.

[ItsMe]

Check this out. It is heavily commented. It should give you an idea, or the start of an idea how to validate your fields and update your table(s).

[jwill]

@Itsme thanks for the db! I looked @ it and it does give me some ideas but I'm not sure if it will help in this current situation. I'm looking @ the cmdSubmit_Click procedure and it looks like in order for this to work in my application, I would have to enter this on the afterupdate event up each control. I was trying to make this as admin friendly as possible. I think I will use the code that I posted earlier with some of the clean up suggestions posted by ssanfu. I will probably revisit this thread if I start to have peformance problems. Thanks for all your help