Solved Form to add data to multiple records

[gojets1721]

See the attached DB. I'm trying to come up with a form that will update multiple records with a single line that the user inputs.

So in the example, I'm hoping to allow the user to put in multiple complaint numbers, and then type out whatever they want in the follow-up field. Then once the command is pressed, that follow-up field data will be inputted in the ComplaintFollowUp field in each of the complaint numbers (records) that the user inputted.

I'm lost on where to start. Any suggestions?

 

[bastanu]

Replace the textbox with a multiselect listbox to select the multiple complaints (allows you to show additional data such as complaint date or category) then loop through the selected items and run a Update statement for each selected item. If you want to keep your textbox use Split() to get the individual complaint numbers into an array and loop that instead of the ItemSelected of the listbox.
Cheers.

 

[jdraw]

I would recommend using a multiselect list box. The list box would hold the desc of the complaint text.
The user could select 1 or more complaints from the listbox and you would have some code to update the appropriate records with the id value of the complaint.
This way you get consistent text; user doesn't have to remember/key the number.

OOOops: I see vlad posted.

 

[gojets1721]

Replace the textbox with a multiselect listbox to select the multiple complaints (allows you to show additional data such as complaint date or category) then loop through the selected items and run a Update statement for each selected item. If you want to keep your textbox use Split() to get the individual complaint numbers into an array and loop that instead of the ItemSelected of the listbox.
Cheers.

There's thousands of complaints though. That's why I felt a simple textbox was easier

 

[GPGeorge]

There's thousands of complaints though. That's why I felt a simple textbox was easier

Whoa. You want to update thousands of complaints at the same time? Maybe there's more to the story than we've seen so far. Please tell that story.

 

[bastanu]

Having the listbox filtered in some way might help with that, either by a date range and\or complaint category, etc. The textbox method would probably involve the user to manually compile a list of numbers either on paper or another app then maybe paste it in. But the logic would be the same, loop (through the array or .ItemsSeleted collection) and run the Update SQL statement.

Cheers,

 

[jdraw]

1000's of complaint instances, but not a 1000 different types of complaint.Your sample shows only 2 different complaint types. But relayed/provided by 7 customers. Customer*ComplaintID 7 * 2= 14 complaint instances.

But as George pointed out, 1000's of complaints----perhaps there is more to the story.

 

[Pat Hartman]

If you don't want to pick from a multi-select listbox because the user KNOWS the IDs of the complaints, then you can use two unbound controls, one to hold the IDs separated by a comma and the other to hold the text. In the code, use the Split() function to turn the text in the textbox into an array. Then you would use the array to control your update loop. You'll need to trap errors because if the user misses a comma or uses some other delimiter, the value in the array will not find a match and therefore, the update will fail.

 

[gojets1721]

I do not want to update thousands at once. Yes, it has thousands in there but the user needs to specify want records to update. This would be used to update a few records at a time. The user knows what records they want to update and they want to update them all with the same thing.

 

[gojets1721]

If you don't want to pick from a multi-select listbox because the user KNOWS the IDs of the complaints, then you can use two unbound controls, one to hold the IDs separated by a comma and the other to hold the text. In the code, use the Split() function to turn the text in the textbox into an array. Then you would use the array to control your update loop. You'll need to trap errors because if the user misses a comma or uses some other delimiter, the value in the array will not find a match and therefore, the update will fail.

Gotcha. I'll give it try and circle back here if I run into issues. Thanks

 

[gojets1721]

Having the listbox filtered in some way might help with that, either by a date range and\or complaint category, etc. The textbox method would probably involve the user to manually compile a list of numbers either on paper or another app then maybe paste it in. But the logic would be the same, loop (through the array or .ItemsSeleted collection) and run the Update SQL statement.

Cheers,

@bastanu would you have a walkthrough guide you could send over by any chance? I'm fairly new to VBA and I'm struggling a bit on how to get started

 

[MajP]

Private Sub btnAddFollowUp_Click()
  Dim strIn As String
  Dim aStr() As String
  Dim i As Integer
  Dim ComplaintNumber As Long
  Dim strSql As String
  strIn = Me.txtEventNumbers & ""
  If Not strIn = "" Then
    aStr = Split(strIn, ",")
    For i = 0 To UBound(aStr)
      Debug.Print aStr(i)
      If IsNumeric(aStr(i)) Then
        ComplaintNumber = CLng(aStr(i))
        If (Me.txtFollowUp & "") <> "" Then
           strSql = "Update tblComplaints set complaintfollowup = '" & Me.txtFollowUp & "' where ComplaintNumber = " & ComplaintNumber
           CurrentDb.Execute strSql
         Else
           MsgBox "Enter a followup.", vbCritical
           Exit Sub
         End If
      Else
        MsgBox aStr(i) & " is not a valid Complaint number.", vbCritical
      End If
    Next i
  Else
    MsgBox "No Complaint Numbers entered", vbInformation
End If
End Sub

 

[ebs17]

The user knows what records they want to update

In practice, entering data by hand is more error-prone than selecting from a given list. There are quick differences between wanting and doing (daily form).

In addition, in the case of multiple entries, the necessary structure would have to be adhered to exactly or corrected by code.
Separation of single contents by comma, space, hyphen - everything that is conceivable is thought of by a user and creatively used, also in combinations.

So show how a user should enter several values as key.

In practice one would use an update query. So the input list would have to be built into a working filter for the query. The stronger the enforced standardization is, the easier this filter generation is. If you give the user the freedom to do his own creative work, it becomes more complex, because you have to take into account and handle everything in advance, which only occurs to such a user at the moment.

 

[MajP]

Here is a much better user interface and avoids any problems with data integrity. In this way the user sees the records to update. First select the records. If the list is correct then update, else modify the list.

Private Sub btnAddFollowUp_Click()
  Dim i As Integer
  Dim ComplaintNumber As Long
  Dim strSql As String
  Dim followup As String
  Dim rs As DAO.Recordset
 
  followup = Me.txtFollowUp & ""
  If Not followup = "" Then
    Set rs = Me.subFrmComplaints.Form.Recordset
    If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
    Do While Not rs.EOF
      rs.Edit
      rs!complaintfollowup = followup
      rs.Update
      rs.MoveNext
    Loop
  Else
    MsgBox "Add a followup.", vbInformation
  End If
  End Sub


Private Sub cmdCreate_Click()
  Dim strIn As String
  Dim aStr() As String
  Dim i As Integer
  Dim ComplaintNumber As String
  Dim strFilter As String
  Dim strSql As String
  strIn = Me.txtEventNumbers & ""
  If Not strIn = "" Then
    aStr = Split(strIn, ",")
    For i = 0 To UBound(aStr)
      If IsNumeric(aStr(i)) Then
        ComplaintNumber = aStr(i)
        If strFilter = "" Then
          strFilter = ComplaintNumber
        Else
          strFilter = strFilter & ", " & ComplaintNumber
        End If
      Else
        MsgBox aStr(i) & " is not a valid Complaint number.", vbCritical
      End If
    Next i
    Me.subFrmComplaints.Form.RecordSource = "Select * from tblComplaints where complaintNumber IN (" & strFilter & ")"
  Else
    MsgBox "No Complaint Numbers entered", vbInformation
 End If
End Sub

 

[SHANEMAC51]

I would recommend using a multiselect list box. The list box would hold the desc of the complaint text.

instead of a list, I use a pop-up form in which you can not only select the existing text, but also add a completely new text or duplicate the old one for correction

 

[gojets1721]

Here is a much better user interface and avoids any problems with data integrity. In this way the user sees the records to update. First select the records. If the list is correct then update, else modify the list.
View attachment 106798

Private Sub btnAddFollowUp_Click()
  Dim i As Integer
  Dim ComplaintNumber As Long
  Dim strSql As String
  Dim followup As String
  Dim rs As DAO.Recordset

  followup = Me.txtFollowUp & ""
  If Not followup = "" Then
    Set rs = Me.subFrmComplaints.Form.Recordset
    If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
    Do While Not rs.EOF
      rs.Edit
      rs!complaintfollowup = followup
      rs.Update
      rs.MoveNext
    Loop
  Else
    MsgBox "Add a followup.", vbInformation
  End If
  End Sub


Private Sub cmdCreate_Click()
  Dim strIn As String
  Dim aStr() As String
  Dim i As Integer
  Dim ComplaintNumber As String
  Dim strFilter As String
  Dim strSql As String
  strIn = Me.txtEventNumbers & ""
  If Not strIn = "" Then
    aStr = Split(strIn, ",")
    For i = 0 To UBound(aStr)
      If IsNumeric(aStr(i)) Then
        ComplaintNumber = aStr(i)
        If strFilter = "" Then
          strFilter = ComplaintNumber
        Else
          strFilter = strFilter & ", " & ComplaintNumber
        End If
      Else
        MsgBox aStr(i) & " is not a valid Complaint number.", vbCritical
      End If
    Next i
    Me.subFrmComplaints.Form.RecordSource = "Select * from tblComplaints where complaintNumber IN (" & strFilter & ")"
  Else
    MsgBox "No Complaint Numbers entered", vbInformation
End If
End Sub

This worked great! Thanks everyone!!