<div>
Hi Nev,<br />
<br />
Are the two queries (<b><i>qryKawProductAvailable</i></b> and <b><i>qryKawProductAllocated </i></b>) based on <b>tblSale </b>only or are there other tables involved?<br />
<br />
~Tala~
</div>


<div>
There is other tables involved. &nbsp;Here is the sql for qryKawProductAvailable<br />
<br />
SELECT tblSale.BookID, tblSale.SaleID, tblBook.BookName, tblSale.BookInStock, tblAuthor.AuthorType<br />
FROM (tblAuthor INNER JOIN tblBook ON tblAuthor.AuthorID = tblBook.AuthorID) INNER JOIN tblSale ON tblBook.BookID = tblSale.BookID<br />
WHERE (((tblSale.BookID)=[forms]<wbr />![frmAddBu<wbr />yer]![txtp<wbr />roduct]) AND ((tblSale.BookInStock)=Yes<wbr />) AND ((tblAuthor.AuthorType)=&quot;k<wbr />aweka&quot;));<br />
<br />
and sql for qrykawProductAllocated<br />
<br />
SELECT tblBuyer.buyerID, tblBuyer.BuyerTrademeName,<wbr /> tblSale.BookID, tblBook.BookName, tblSale.SaleID, tblSale.BookInStock, tblAuthor.AuthorType<br />
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<br />
WHERE (((tblBuyer.buyerID)=[form<wbr />s]![frmAdd<wbr />Buyer]![bu<wbr />yerID]) AND ((tblSale.BookInStock)=No)<wbr /> AND ((tblAuthor.AuthorType)=&quot;k<wbr />aweka&quot;));<br />
<br />
cheers
</div>


<div>
Copy that. &nbsp;What are the names of Listbox1 and Listbox2?
</div>


<div>
Ok, here goes.<br />
<br />
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 <b>but </b> 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. &nbsp;<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42091553-1"><span>Private Sub cmdAddBuyer_Click()</span>
<span>'On Error GoTo Error_Handler</span>
<span>    </span>
<span>    'declare connections and variables -&gt;</span>
<span>    Dim rs As New ADODB.Recordset</span>
<span>    Dim Conn As ADODB.Connection</span>
<span>    Set Conn = CurrentProject.AccessConnection</span>
<span>    Dim strSQL As String</span>
<span>    Dim dbFailOnError, dbSeeChanges</span>
<span>    Dim iVal As Variant</span>
<span>    Dim lngBuyerID As Long, blnBookInStock As Boolean</span>
<span>        </span>
<span>'-------------------------------------------------------------------</span>
<span>'Defining List Column Fields:</span>
<span>'[0] = BookID</span>
<span>'[1] = SaleID</span>
<span>'[2] = Bookname</span>
<span>      </span>
<span>    '-------------------------------------------------------------------</span>
<span>    'collect the BuyerID and set the InStock value -&gt;</span>
<span>    lngBuyerID = Forms(&quot;frmAddBuyer&quot;).Controls(&quot;txtBuyerID&quot;)</span>
<span>    blnBookInStock = False</span>
<span>        </span>
<span>    'looping through all the items selected in the listbox</span>
<span>    With Forms(&quot;frmAddBuyer&quot;).Controls(&quot;lst1&quot;)</span>
<span>        If .ItemsSelected.Count &gt; 0 Then</span>
<span>            For Each iVal In .ItemsSelected</span>
<span>                If Not IsNull(iVal) Then</span>
<span>                    strSQL = &quot;UPDATE tblSale SET BookInStock =&quot; &amp; blnBookInStock &amp; &quot;, &quot; &amp; _</span>
<span>                    &quot;buyerID =&quot; &amp; lngBuyerID &amp; &quot; &quot; &amp; _</span>
<span>                    &quot;WHERE (((BookID)=&quot; &amp; .Column(0, iVal) &amp; &quot;))&quot;</span>
<span>                    'Debug.Print &quot;BOOK_ID=:&quot; &amp; .Column(0, iVal)</span>
<span>                    Conn.Execute strSQL, dbFailOnError</span>
<span>                End If</span>
<span>            Next iVal</span>
<span>        End If</span>
<span>    End With</span>
<span>    </span>
<span>    'refresh the listboxes -&gt;</span>
<span>    Forms(&quot;frmAddBuyer&quot;).Controls(&quot;lst1&quot;).Requery</span>
<span>    Forms(&quot;frmAddBuyer&quot;).Controls(&quot;lst2&quot;).Requery</span>
<span>    </span>
<span>Err_Handler_Exit:</span>
<span>    Exit Sub</span>
<span></span>
<span>Error_Handler:</span>
<span>If Err.Number = 0 Then</span>
<span>    Resume Err_Handler_Exit</span>
<span>Else</span>
<span>    MsgBox &quot;An unexpected application error has been detected.&quot; &amp; vbCrLf &amp; &quot;&quot; &amp; vbCrLf &amp; _</span>
<span>    &quot;Error: &quot; &amp; Err.Number &amp; &quot; (&quot; &amp; Err.Description &amp; &quot;)&quot;, vbOKOnly, &quot;Books&quot;</span>
<span>      Resume Err_Handler_Exit</span>
<span>End If</span>
<span>End Sub</span>
</code></pre>
<br />
Test it out and let me know how you go.<br />
<br />
~Tala~
</div>


<div>
Hi Nev,<br />
<br />
In your tblSale, what are you using as your Primary Key?
</div>


<div>
Hi Tala,<br />
<br />
Great first effort. &nbsp;It nearly worked straight off the bat, but I had to make a small change. &nbsp;<br />
<br />
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.<br />
<br />
I had to edit this line &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'WHERE (((BookID)=&quot; &amp;&nbsp;.Column(0, iVal) &amp;&nbsp;&quot;))&quot;<br />
<br />
to this line: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&quot;WHERE (((SaleID)=&quot; &amp;&nbsp;.Column(1, iVal) &amp;&nbsp;&quot;))&quot; &nbsp;<br />
<br />
It seems to be working correctly, but I will do a bit more testing <br />
<br />
thanks<br />
Nev
</div>


<div>
That change is picking up the SaleID right?
</div>


<div>
Is SaleID your Primary Key in tblSale?
</div>


<div>
Yes, it is the primary key in tblSale
</div>


<div>
Copy that.<br />
<br />
In that case, the change you made should work flawlessly based on SaleID as your Primary Key.<br />
<br />
Let me know if you have any other issues otherwise if you are happy with this solution, please go ahead and close this question.<br />
<br />
-Tala-
</div>


<div>
There is one thing I have noticed in the testing I have been doing.<br />
<br />
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. &nbsp;It has worked though, because if close the form, and reopen the listboxes are correct and showing the books he bought.<br />
<br />
If I close the form and reopen, and then add the books he has bought, the list boxes refresh correctly.<br />
<br />
I'm guessing, ideally, I should save the buyer record before adding the books? &nbsp;Is there a line of code I can add to the start of your code, that does this.?
</div>


<div>
In the code, there are two lines below the loop that are requerying your listboxes.<br />
<br />
Could you confirm the second Listbox name is correct?
</div>


<div>
Yes, the name is correct.<br />
<br />
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.<br />
<br />
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. &nbsp;It remains empty. &nbsp;There is no error message.
</div>


<div>
Ok, it seems there may be an issue with the <b>BuyerID </b>and how the SQL's are being refreshed. &nbsp;<br />
<br />
I know you said earlier : 
<blockquote>
&quot;On form, frmAddBuyer, is a field containing the buyerID (PK in tblBuyer), for the current buyer&quot;.
</blockquote>
<br />
Could you please tell me how you are creating new BuyerID? &nbsp;Is it done on a separate form or via a command button?
</div>


<div>
I have a form (frmBuyer) that lists all the existing buyers in the database. &nbsp;I have two buttons on this form. Add New Buyer and Existing Buyer.<br />
<br />
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.<br />
<br />
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.<br />
<br />
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).
</div>


<div>
Sorry Nev. I overlooked this comment :<br />
<br />

<blockquote>
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.<br />
<br />
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). 
</blockquote>
<br />
Absolutely, you need to Save the new <b>Buyer </b>record to your Buyer table first otherwise it won't work as you expect. <br />
<br />
~Tala~
</div>


<div>
Hmmm. &nbsp;Same problem with the 2nd listbox.
</div>


<div>
Just noticed your line about having to save the record. <br />
<br />
Can you give me a line of code to add to above to save the record?
</div>


<div>
Ok sit tight I am going to try and duplicate your issue with a test db here.<br />
<br />
Will post back shortly.<br />
<br />
-Tala-
</div>


<div>
I think I have it sorted.<br />
<br />
On frmAddBuyer there is a combo box that I use to select the books to populate the first listbox.<br />
<br />
I've added to the after_Update event: &nbsp;DoCmd.RunCommand acCmdSaveRecord<br />
<br />
Now the 2nd listbox is refreshing correctly when I add a new buyer, and immediately allocate the books.<br />
<br />
Yay!!!!<br />
<br />
I think we are good to go now ...
</div>


<div>
Ok, so lets work through this together as it appears there is an issue with the logic.<br />
<br />
Is your frmBuyer bound to the Buyer table?
</div>


<div>
I think my previous comment crossed with yours.<br />
<br />
can you have a read of it &nbsp;- I think we are good to go now
</div>


<div>
Great! I was kind of wondering where those other controls came into your process but wanted to tease it out with you first.<br />
<br />
I am happy you have it working now.<br />
<br />
Let us know if you have any other issues.<br />
<br />
-Tala-
</div>


<div>
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. &nbsp;<br />
<br />
It will save me a lot of time!<br />
<br />
Cheers, Tala!<br />
<br />
Regards<br />
Nev
</div>


<div>
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.<br />
<br />
-Tala-
</div>


<div>
Tusitala did a fantastic job interpreting what was required in solving my problem and providing a solution. &nbsp;Hat's off to you - you're obviously the real deal..!!!!
</div>


<div>
Thanks for the kind words. Glad we could help here at Experts Exchange!
</div>


<div>
<div class="content wysiwyg-content">
Hi everyone,<br />
<br />
My first question. &nbsp;<br />
<br />
On form, frmAddBuyer, is a field containing the buyerID (PK in tblBuyer), for the current buyer.<br />
<br />
I have two listboxes on the form. &nbsp;The first multi-select listbox shows the books currently in stock and available to be bought, based on tblSale. &nbsp;The table name might be a bit misleading, but all books entered into the database are added to tblSale.<br />
<br />
The 2nd listbox shows the the books that have been bought by the current buyer.<br />
<br />
There is a combo box on the form, that filters the books, based on the bookID.<br />
<br />
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. &nbsp;The selected books in the first listbox will no longer be shown as available and in stock.<br />
<br />
The rowsource for the first listbox is: SELECT [qryKawProductAvailable].[<wbr />BookID], [qryKawProductAvailable].[<wbr />SaleID], [qryKawProductAvailable].[<wbr />BookName] FROM qryKawProductAvailable ORDER BY [SaleID]; &nbsp; The bound column is column 1.<br />
<br />
The rowsource for the 2nd listbox is: &nbsp;SELECT [qryKawProductAllocated].[<wbr />buyerID], [qryKawProductAllocated].[<wbr />BuyerTrade<wbr />meName], [qryKawProductAllocated].[<wbr />BookID], [qryKawProductAllocated].[<wbr />BookName],<wbr /> [qryKawProductAllocated].[<wbr />SaleID], [qryKawProductAllocated].[<wbr />BookInStoc<wbr />k], [qryKawProductAllocated].[<wbr />AuthorType<wbr />] FROM qryKawProductAllocated ORDER BY [buyerID]; &nbsp; The bound column is column 1.<br />
<br />
I'm thinking when the button is clicked, it opens a recordset based on tblSale, and the books selected in the first listbox. &nbsp;As it cycles through each record, it inserts the buyerid into the buyerid field, and changes the bookInStock field to No.<br />
<br />
Sorry for any confusion...!
</div>
</div>


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...!

Updating records selected in a multi select simple List box

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.