Continuous forms updating all

[musclecarlover07]

In my database I want to update a lot of records at once. Not all records only selected ones. So I have a form with a multi select list box. I select all the records I want to update then click a button. It then opens another form (continuous form). On this form I want to change only the records that have been selected by the list box with a combo box. I tried to do a "do loop" but it only does 3 records. Any help or better suggestions would be awesome.

[ItsMe]

Post your code. Also, why are you opening another form from the listbox? Is it the other form that allows the user to determine what gets edited? When I read your post, I think you should be iterating the list box and applying edits based on the selections in the listbox.

[musclecarlover07]

I'm not that familiar with access. Still learning the in and outs. The second form allows me to edit the data. If there is a way that I could edit the records from the list box would be awesome.

Basically the idea is selected a bunch of records and assign them to one person and do this multiple times. Till all records are assigned.

Do While Me.CurrentRecord <> acLast
       If Me.CurrentRecord <> acLast Then
           Me.Assigned = Me.Combo55
           DoCmd.GoToRecord , , acNext
       End If
   Loop


   If Me.CurrentRecord = acLast Then
       Me.Assigned = Me.Combo55
       Exit Sub
   End If

like I said this only works on three records. If 1 or 2 records are selected then it
gives me an error stating cannot go to specified record.

[ItsMe]

Your code may not be working correctly because you are not at the first record when you begin. It is hard to say for sure. I would use DAO or an update query to update the records. You would have to start by iterating the selections within your listbox. Here is some code. YOu will have to change lstSelection to the name of your listbox. You can view the results in the immediate window. The immediate window can be viewed by hitting Ctrl+G on your keyboard. Get this first step and then you can move to the next.

Dim varSelection As Variant
Dim strAnswer As String
 If Me.lstSelection.Count = 0 Then
 Exit Sub
 Else
 For Each varSelection In Me.lstSelection.itemselection
 strAnswer = Me.lstSelection.Column(0, varSelection)

 'Insert code here to find and update the record in the table
Debug.Print strAnswer

 Next varSelection
 End If

[musclecarlover07]

It does start at the first record. Bit if I click on the 4 record to start it won't run the code. I remember doing something with DAO. But it never changed records. I looked at doing an update query but it changes all the records. I'm not sure how to get it to the point where it will filter the update query with what I selected on the list box. So I put the code behind the button with the list box. So that way I select the items in the list box hit the button then to run the code. When I do this it highlights the .Count and gives error method or member not found. Also I though if I did Table!MainWorkNew = Me. Combo7. It gives me an error saying object required.

[ItsMe]

The code I provided should go behind a button's click event. This button should be dedicated to the purpose of updating records related to the listbox. No other code should be included, yet.

What is the code you tried? Can you post it here please?

[musclecarlover07]

The only thing I added to this was trying to update the table. With Table!MainWorkNew!Assigned.

Dim rs As DAO.Recordset
   Dim db As Database


   Set db = CurrentDb
   Set rs = db.OpenRecordset("MainWorkNew")


   If rs.RecordCount <> 0 Then
       rs.MoveFirst
       While Not rs.EOF
           Me.Assigned = Me.Combo55
           rs.MoveNext
       Wend
   End If


   rs.Close

this is what I was told to use but it didn't cycle through the records on the continuous form.

[ItsMe]

There is not any instant gratification when creating complex VBA code blocks. You need to nest the update code within the iteration of the listbox. First step is to integrate the code I provided in post #4. Then you need to determine a where clause to add to the code. Then you need to add code that will nest within the first loop and do the updates. The update code may update one record or it may update several as the first loop iterates the listbox.

I believe you will be better off in the long run not to use the approach in post #3. Do you want to try the code in post #4?

[musclecarlover07]

Yes I would like to try the code in post 4. everything else hasn't worked. You you have been more then helpful.

[ItsMe]

Create a button on the form with the listbox. PLace the code in post #4 in the click event. Change lstSelection to the name of your listbox. Make a couple selections in the listbox and click the button. Look in the immediate window and post the results here.

[musclecarlover07]

Ok so I did that. When I clicked the button an it gave me an error.

Compile error: Method or data member not found
then it highlights .Count

so I deleted that. Then it gave the same error. But highlighted .itemselection
I changed that to .ItemsSelected

When I did this it didn't give an error. In the immediate window it have me the ID number of the selected records.

[ItsMe]

Sorry about that. The code I provided was not correct. The following is tested.

 
Dim varSelection As Variant
Dim lngID As Long
 If Me.List0.ItemsSelected.Count = 0 Then
 Exit Sub
 Else
 For Each varSelection In Me.List0.ItemsSelected
 lngID = Me.List0.Column(0, varSelection)
 'Insert code here to find and update the record in the table
Debug.Print lngID
 Next varSelection
 End If

So, from what you tell me, the first column is the key value. So we can use a long integer for this instead of a string variable. I am going to imagine that you want to find the key value being returned inside a field that resides in table MainWorkNew.

What is the name of the field in table MainWorkNew that you want to search?
What is the name of your listbox?
Is the name of the field you want to UPDATE named, "Assigned"

[musclecarlover07]

So yes I want the searches and everything because it is my primary key. It is also the easiest thing I can set to a primary key and not worry bout potential duplicates.

Right now I'm building a test database to see what works.
The search field is called Id. Everything is related to that
list0
yes assigned. When I actually build the working model I will give better and appropriate names.

[ItsMe]

You will have to create a combo on the same form as your listbox.

Dim rs As DAO.Recordset
Dim db As DAO.Database
   Set db = CurrentDb
   Set rs = db.OpenRecordset("MainWorkNew", dbOpenDynaset)
Dim varSelection As Variant
Dim lngID As Long
 If Me.List0.ItemsSelected.Count = 0 Then
 Exit Sub
 Else
 For Each varSelection In Me.List0.ItemsSelected
 lngID = Me.List0.Column(0, varSelection)
     'Insert code here to find and update the record in the table
    rs.FindFirst "[ID] = " & lngID
    If Not rs.NoMatch Then
    rs.Edit
    rs![Assigned] = Me.Combo55
    rs.Update
    End If
'Move to the next item selected in listbox
 Next varSelection
 End If
rs.Close
Set rs = Nothing
Set db = Nothing

[musclecarlover07]

Thank you for the help. I have been trying to figure this out and had asked for help else where but just kept going in circles. Now to study and get a better understanding on what is going on and actually build the database. Again thank you.