We help IT Professionals succeed at work.

Updating records selected in a multi select simple List box

Nev Williams
Nev Williams asked
on
128 Views
Last Modified: 2017-04-13
Hi everyone,

My first question.  

On form, frmAddBuyer, is a field containing the buyerID (PK in tblBuyer), for the current buyer.

I have two listboxes on the form.  The first multi-select listbox shows the books currently in stock and available to be bought, based on tblSale.  The table name might be a bit misleading, but all books entered into the database are added to tblSale.

The 2nd listbox shows the the books that have been bought by the current buyer.

There is a combo box on the form, that filters the books, based on the bookID.

What I need to happen after I have selected multiple books in the first listbox, is click on my Add Purchase button, and have the selected books move across to the 2nd list box, and have both listboxes refresh.  The selected books in the first listbox will no longer be shown as available and in stock.

The rowsource for the first listbox is: SELECT [qryKawProductAvailable].[BookID], [qryKawProductAvailable].[SaleID], [qryKawProductAvailable].[BookName] FROM qryKawProductAvailable ORDER BY [SaleID];   The bound column is column 1.

The rowsource for the 2nd listbox is:  SELECT [qryKawProductAllocated].[buyerID], [qryKawProductAllocated].[BuyerTrademeName], [qryKawProductAllocated].[BookID], [qryKawProductAllocated].[BookName], [qryKawProductAllocated].[SaleID], [qryKawProductAllocated].[BookInStock], [qryKawProductAllocated].[AuthorType] FROM qryKawProductAllocated ORDER BY [buyerID];   The bound column is column 1.

I'm thinking when the button is clicked, it opens a recordset based on tblSale, and the books selected in the first listbox.  As it cycles through each record, it inserts the buyerid into the buyerid field, and changes the bookInStock field to No.

Sorry for any confusion...!
Comment
Watch Question

Commented:
Hi Nev,

Are the two queries (qryKawProductAvailable and qryKawProductAllocated ) based on tblSale only or are there other tables involved?

~Tala~

Author

Commented:
There is other tables involved.  Here is the sql for qryKawProductAvailable

SELECT tblSale.BookID, tblSale.SaleID, tblBook.BookName, tblSale.BookInStock, tblAuthor.AuthorType
FROM (tblAuthor INNER JOIN tblBook ON tblAuthor.AuthorID = tblBook.AuthorID) INNER JOIN tblSale ON tblBook.BookID = tblSale.BookID
WHERE (((tblSale.BookID)=[forms]![frmAddBuyer]![txtproduct]) AND ((tblSale.BookInStock)=Yes) AND ((tblAuthor.AuthorType)="kaweka"));

and sql for qrykawProductAllocated

SELECT tblBuyer.buyerID, tblBuyer.BuyerTrademeName, tblSale.BookID, tblBook.BookName, tblSale.SaleID, tblSale.BookInStock, tblAuthor.AuthorType
FROM (tblAuthor INNER JOIN tblBook ON tblAuthor.AuthorID = tblBook.AuthorID) INNER JOIN (tblSale INNER JOIN tblBuyer ON tblSale.BuyerID = tblBuyer.buyerID) ON tblBook.BookID = tblSale.BookID
WHERE (((tblBuyer.buyerID)=[forms]![frmAddBuyer]![buyerID]) AND ((tblSale.BookInStock)=No) AND ((tblAuthor.AuthorType)="kaweka"));

cheers

Commented:
Copy that.  What are the names of Listbox1 and Listbox2?

Commented:
Ok, here goes.

Assuming you are using a newer version of Access with correct library references for ADO usage, the following procedure will do exactly as you requested but you will need to modify the control names that I have referred to in the code below to reflect the actual names that you have used on your form.  

Private Sub cmdAddBuyer_Click()
'On Error GoTo Error_Handler
    
    'declare connections and variables ->
    Dim rs As New ADODB.Recordset
    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.AccessConnection
    Dim strSQL As String
    Dim dbFailOnError, dbSeeChanges
    Dim iVal As Variant
    Dim lngBuyerID As Long, blnBookInStock As Boolean
        
'-------------------------------------------------------------------
'Defining List Column Fields:
'[0] = BookID
'[1] = SaleID
'[2] = Bookname
      
    '-------------------------------------------------------------------
    'collect the BuyerID and set the InStock value ->
    lngBuyerID = Forms("frmAddBuyer").Controls("txtBuyerID")
    blnBookInStock = False
        
    'looping through all the items selected in the listbox
    With Forms("frmAddBuyer").Controls("lst1")
        If .ItemsSelected.Count > 0 Then
            For Each iVal In .ItemsSelected
                If Not IsNull(iVal) Then
                    strSQL = "UPDATE tblSale SET BookInStock =" & blnBookInStock & ", " & _
                    "buyerID =" & lngBuyerID & " " & _
                    "WHERE (((BookID)=" & .Column(0, iVal) & "))"
                    'Debug.Print "BOOK_ID=:" & .Column(0, iVal)
                    Conn.Execute strSQL, dbFailOnError
                End If
            Next iVal
        End If
    End With
    
    'refresh the listboxes ->
    Forms("frmAddBuyer").Controls("lst1").Requery
    Forms("frmAddBuyer").Controls("lst2").Requery
    
Err_Handler_Exit:
    Exit Sub

Error_Handler:
If Err.Number = 0 Then
    Resume Err_Handler_Exit
Else
    MsgBox "An unexpected application error has been detected." & vbCrLf & "" & vbCrLf & _
    "Error: " & Err.Number & " (" & Err.Description & ")", vbOKOnly, "Books"
      Resume Err_Handler_Exit
End If
End Sub

Open in new window


Test it out and let me know how you go.

~Tala~

Commented:
Hi Nev,

In your tblSale, what are you using as your Primary Key?

Author

Commented:
Hi Tala,

Great first effort.  It nearly worked straight off the bat, but I had to make a small change.  

When I first ran the code, it was updating all books, even those that had sold previously and were not shown in the first listbox.

I had to edit this line          'WHERE (((BookID)=" & .Column(0, iVal) & "))"

to this line:                          "WHERE (((SaleID)=" & .Column(1, iVal) & "))"  

It seems to be working correctly, but I will do a bit more testing

thanks
Nev

Commented:
That change is picking up the SaleID right?

Author

Commented:
Yes, it is.

Commented:
Is SaleID your Primary Key in tblSale?

Author

Commented:
Yes, it is the primary key in tblSale

Commented:
Copy that.

In that case, the change you made should work flawlessly based on SaleID as your Primary Key.

Let me know if you have any other issues otherwise if you are happy with this solution, please go ahead and close this question.

-Tala-

Author

Commented:
There is one thing I have noticed in the testing I have been doing.

When I add a new buyer, and add the books he has bought via the listboxes, the 2nd listbox doesn't refresh and show the books he bought.  It has worked though, because if close the form, and reopen the listboxes are correct and showing the books he bought.

If I close the form and reopen, and then add the books he has bought, the list boxes refresh correctly.

I'm guessing, ideally, I should save the buyer record before adding the books?  Is there a line of code I can add to the start of your code, that does this.?

Commented:
In the code, there are two lines below the loop that are requerying your listboxes.

Could you confirm the second Listbox name is correct?

Author

Commented:
Yes, the name is correct.

If I add the new buyer and then close the addBuyer form, reopen the addbuyer form and then allocate the books via the listboxes, the refreshing works correctly.

It is only when I add the new buyer, then immediately allocate the books via the listboxes, that the 2nd list box doesn't refresh and show the books just allocated.  It remains empty.  There is no error message.

Commented:
Ok, it seems there may be an issue with the BuyerID and how the SQL's are being refreshed.  

I know you said earlier :
"On form, frmAddBuyer, is a field containing the buyerID (PK in tblBuyer), for the current buyer".

Could you please tell me how you are creating new BuyerID?  Is it done on a separate form or via a command button?

Author

Commented:
I have a form (frmBuyer) that lists all the existing buyers in the database.  I have two buttons on this form. Add New Buyer and Existing Buyer.

If I select an existing buyer from my list of buyers, click the existing buyer button, frmAddBuyer will open and display that particular buyers record.

If I have a new buyer to add, I click on the Add New Buyer button, frmAddBuyer opens with a new empty record and I can add his details.

My thinking is that the problem is related to the new record for the new buyer has not been saved, before I try and add books (using additional tables i.e. tblSale).
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Sorry Nev. I overlooked this comment :

If I have a new buyer to add, I click on the Add New Buyer button, frmAddBuyer opens with a new empty record and I can add his details.

My thinking is that the problem is related to the new record for the new buyer has not been saved, before I try and add books (using additional tables i.e. tblSale).

Absolutely, you need to Save the new Buyer record to your Buyer table first otherwise it won't work as you expect.

~Tala~

Author

Commented:
Hmmm.  Same problem with the 2nd listbox.

Author

Commented:
Just noticed your line about having to save the record.

Can you give me a line of code to add to above to save the record?

Commented:
Ok sit tight I am going to try and duplicate your issue with a test db here.

Will post back shortly.

-Tala-

Author

Commented:
I think I have it sorted.

On frmAddBuyer there is a combo box that I use to select the books to populate the first listbox.

I've added to the after_Update event:  DoCmd.RunCommand acCmdSaveRecord

Now the 2nd listbox is refreshing correctly when I add a new buyer, and immediately allocate the books.

Yay!!!!

I think we are good to go now ...

Commented:
Ok, so lets work through this together as it appears there is an issue with the logic.

Is your frmBuyer bound to the Buyer table?

Author

Commented:
I think my previous comment crossed with yours.

can you have a read of it  - I think we are good to go now

Commented:
Great! I was kind of wondering where those other controls came into your process but wanted to tease it out with you first.

I am happy you have it working now.

Let us know if you have any other issues.

-Tala-

Author

Commented:
I'm really grateful for your help, and this small change will make an enormous improvement in the way I do things with the database.  

It will save me a lot of time!

Cheers, Tala!

Regards
Nev

Commented:
Forgot to mention that you can go ahead and uncomment those Error Handlers now so that if any errors occur, they will be trapped and presented to you to help troubleshoot any problems you may have in future.

-Tala-

Author

Commented:
Tusitala did a fantastic job interpreting what was required in solving my problem and providing a solution.  Hat's off to you - you're obviously the real deal..!!!!

Commented:
Thanks for the kind words. Glad we could help here at Experts Exchange!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.