Update Multiple Records at once 2

[ba4crm]

Hello,
I have a table that has subscriber ids, where each subscriber can have 1 or more than 1 errors. The errors are listed as individual records on this table. Each subscriber belongs to a customer number (they can only belong to one customer number) I am displaying the subscriber numbers under each customer number and would like the users to select more than one subscriber number and assign the subscriber records to themselves. Ideally, I would like the users to define how many subscriber records they would like to select, but I would settle for creating a pre-set number of records (5, 10, etc). When they select the subscriber ids, all the associated error records should be assigned to the user.
How can I do this? I am using Access 2003.

 

[Andrzejek]

Is that your situation?
[tt]
Cust_No Subscr_id Errors
1 1 Error1
1 1 Error2
1 1 Error3
1 2 Error11
1 2 Error21
1 2 Error31
2 5 Other Error[/tt]

What do you mean by "assign the subscriber records to themselves"? And "all the associated error records should be assigned to the user"

The best way is to show data you have now, and data you hope to get.

Have fun.

---- Andy

 

[MajP]

Faq701-6099

Describes a generic function that can be used in a query to return a selected group of records using a multi select listbox.
If you can figure out how to use a multiselect list box in a Select query then modify your select query into an update query. Then have a button on the form to execute the update query.

The other way would be to do it all in code. Loop through the itemsselected collection of the multiselect and run an update query on each item selected assigning the user.

 

[ba4crm]

Andy - your layout is exactly what I have. What I would like the user to be able to do is:
If they select "Top 2", then the subscriber Id 1 and subscriber Id 2 (with error 1, 2, 3, 11, 21, and 31) should be updated to show the user as the assignee (there is a field called "Assignee" in the same table).

 

[Andrzejek]

Aha! We are getting somewhere now...
So we have something like:
[tt]
Cust_No Subscr_id Errors Assignee
1 1 Error1 ba4crm
1 1 Error2 ba4crm
1 1 Error3 ba4crm
1 2 Error11 ba4crm
1 2 Error21 ba4crm
1 2 Error31 ba4crm
2 5 Other Error
[/tt]
Now the question is: how are you going to display the information to the user so they can make a selection(s) of which errors should be assigned to them?
What control (on the UserForm?) are you going to use and what information to show?

Have fun.

---- Andy

 

[Andrzejek]

Well, maybe that's too many questions....

If your table is, let's say, tblMyTable, you can use this UPDATE statement:

strSQL = "UPDATE tblMyTable " _
  & " Set Assignee = 'ba4crm' " _
  & " WHERE [blue]Cust_No[/blue] IN ([blue]1[/blue]) " _
  & " AND [blue]Subscr_id[/blue] IN ([blue]1, 2[/blue]) " [green]
  '& " AND Assignee IS NOY NULL"[/green]
Conn.Execute strSQL

You need to collect some information, like UserName (ba4crm), Cust_No and Aubsct_id (I used IN because you can update multiple Cust_No's and Aubsct_id's in the same UPDATE statement).

If you don't want to over-write previously assigned Assignee, use the commented out portion of the UPDATE statement.

Have fun.

---- Andy

 

[ba4crm]

Thanks Andy!
Well to prevent the user from only picking those subscribers with less errors, I am not displaying the errors or the error count. I have a query that is grouping the subscriber Ids under each Cust_No and displaying this. So when the user says, "I want to assign the top 2 subs to me", they will get sub id 1 and 2 assigned to themselves in the main table.
Note: I removed the customer number from this question - they will only see records for one customer number at a time.
User sees this:

Subs_Id Assignee
1 ba4crm
2 ba4crm
3
5

In the table, this is what should happen:
Subs_Id Errors Assignee
1 Error1 ba4crm
1 Error2 ba4crm
2 Error3 ba4crm
2 Error11 ba4crm
3 Error21
3 Error31
5 OtherError

Thanks

 

[ba4crm]

Andy - thanks for the SQL. In that statement, how will I control that the user wants to select the top 2 records only. What if someone wants to select top 3 or top n?
And in the way you have it, will it update all the subscriber records (let's say they have 3 errors for each sub) with the assignee?

 

[Andrzejek]

What do you mean by: "how will I control that the user wants to select the top 2 records only. What if someone wants to select top 3 or top n?"?

Back to my previous question then - how do you display info to the user for them to make a selection: 2 records, 3 records, n records?

Have fun.

---- Andy

 

[ba4crm]

My stretch goal would be to have a text box on the form where the user can enter the number of records they want to select (or a combo box).
Alternatively, I was going to put 4 buttons on the form that say "2", "5", "20", "All".

 

[Andrzejek]

Can you use multi-select list box on your Form and display "records for one customer number at a time":
[tt][blue]
Subs_Id Assignee[/blue]
1 ba4crm
2 ba4crm[/tt]

So user can select how many they want to 'take'?

You already have: Customer Number, Subs_Id, Assingee, and (hopefully) UserName.

You can display all info per customer number, or just records 'not taken by anybody yet' [tt](Where Assignee IS NULL)[/tt]

Have fun.

---- Andy

 

[ba4crm]

I think the issue with that approach is that the user will still have to click each record (so if they are picking 10 records, they will have to click 10 times). I am trying to cut-down on teh key strokes and have them simply enter 10 and have 10 records assigned to them.

 

[Andrzejek]

If you decide to go with Multi-select list box, look at this example:

UserForm1 with ListBox1 (MultiSelect property set to 1 - fmMultiSelectMulti) and a CommandButton1

Option Explicit

Private Sub UserForm_Initialize()
[green]'Fill the box[/green]
With ListBox1
    .AddItem "1    Andy"
    .AddItem "2    Andy"
    .AddItem "3"
    .AddItem "4"
    .AddItem "5"
End With
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
Dim strIN As String
Dim strSQL As String
Dim intCust_No As Integer

intCust_No = 7 [green]'or whatever your Cust_id is[/green]
[green]
'Pick selected Items[/green]
For i = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(i) Then
    If Len(strIN) = 0 Then
      strIN = Split(ListBox1.List(i), " ")(0)
    Else
      strIN = strIN & ", " _
        & Split(ListBox1.List(i), " ")(0)
    End If
  End If
Next i
[green]
'Use info in UPDATE statement[/green]
strSQL = "UPDATE tblMyTable " & vbNewLine _
  & " Set Assignee = 'ba4crm' " & vbNewLine _
  & " WHERE Cust_No = " & intCust_No & vbNewLine _
  & " AND Subscr_id IN (" & strIN & ") "
[green]
'Show me strSQL[/green]
Debug.Print strSQL

End Sub

Have fun.

---- Andy

 

[Andrzejek]

OK, you may have 3 command buttons (or select from Combo box) and add to my example above (Select 2, select 3, select 4 items):

Option Explicit
Dim s As Integer

Private Sub cmd2_Click()
For s = 0 To 1
    ListBox1.Selected(s) = True
Next s
End Sub

Private Sub cmd3_Click()
For s = 0 To 2
    ListBox1.Selected(s) = True
Next s
End Sub

Private Sub cmd4_Click()
For s = 0 To 3
    ListBox1.Selected(s) = True
Next s
End Sub

...

Have fun.

---- Andy

 

[Andrzejek]

Or stay with my original idea of Multi-select list box and set its MuliSelect property to [tt]2 - fmMultiSelectExtended[/tt]

This way your user may select 1st item, hold Shift key and select 20th item, and all in between will be selected. Ctrl key will allow them to select non-consecutive items.

With the slect "2", "5", "20", "All" - you will need to check how many items there are to select from (a nighmare). What if you have just 17 items? Select "20" does not make any sense. What if you have 50 items and 2 users want to split it half-and-half?

Have fun.

---- Andy

 

[ba4crm]

Hi Andy - I was able to convince the user to go with the multi-select box!
However, when I try to run the code to test, I get the message "Method or Data Member Not Found" for "strIN = Split(ListBox1.List(i), " ")(0)" - the .List is highlighted.
I am not sure what to do to troubleshoot.
I am attaching the file as well.

 

[ba4crm]

I was able to use Fontstuff's Multi-List query (was easier for me to follow) at

http://www.fontstuff.com/access/acctut11.htm

and modified it to be an update query - it worked!!
However, I am trying to further tweak it as such:

strSQL = "UPDATE Table1 " & vbNewLine _
& " Set Assignee = 'ba4crm' " & vbNewLine _
& " WHERE Table1.SubId IN(" & strCriteria & ") "

I have another module in the database that captures the network login name of the user in a function called fOSUserName.

How do I pass it to the update query so that instead of the "ba4crm", the update query enters the person's user name - using the fOSUserName module.
I have attached the modified database

 

[PHV]

Replace this:
& " Set Assignee = 'ba4crm' " & vbNewLine _
with this:
& " Set Assignee = '[!]" & fOSUserName & "[/!]' " & vbNewLine _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ba4crm]

Thank you PH,
When I run the code, I get the message that records are being updated in the table. However, when I view the table, the Assignee field is blank. When I see the Immediate window, I see
UPDATE Table1
Set Assignee = ''
WHERE Table1.SubId IN('OPQ','RST')

Also, my update query has the Assignee field set to " in the "Update To" field.

 

[Andrzejek]

I have another module in the database that captures the network login name of the user in a function called fOSUserName.

Could you show that Function from your module? Is it something like:

Public Function fOSUserName() As String
.....
some code here
....
End Function

Have fun.

---- Andy