How to transfer records from one listbox to another

[Lu.14-08-14]

I to create a database for a small shop as coursework for my college. I have two Listboxes one with all of the stock of the business and another empty one, i want to select a record in the first record then click a button which would transfer the record to the empty Listbox. I have no idea how to do this.

[ranman256]

the listbox is based on a table, so you can run an append query to copy the record lsttBox2's table
BUT dont forget to refresh the listbox2 to see the change:

the following button click will run the query...

sub btnCopy_click()
   docmd.setwarnings false
   docmd.Openquery "qaCopy1ListBoxValue"     'this copies the value
   lstBox2.requery 

   docmd.setwarnings true
end sub

[orange]

Watch this older MSAccess video from DataPig "the listbox trick"

[Lu.14-08-14]

Watch this older MSAccess video from DataPig "the listbox trick"

i followed every thing in the video however when i go to click on the button so that the records can transfer nothing happens, as in no error message appears nothing it transferred and at the bottom it still says i ran the query??? any help

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.

[davegri]

The method in the video works. Suggest you watch it carefully again and transfer the method to your listboxes and tables.
If you're still stuck, post your DB here and someone will take a look.

[Lu.14-08-14]

its because in the video he has a field called "Deleted" which updates from 0 to 1 and thats how he transfers the records but i what i need to do is that im doing this for a corner shop so i want to simulate someone buying a product by them finding the product (record) they need and they would move it over to the second listbox then im yet to do another button which deducts the value from the quantity field in the table linked with the first listbox. so i dont know how to apply it to my DB

[davegri]

but i what i need to do is that im doing this for a corner shop so i want to simulate someone buying a product by them finding the product (record) they need and they would move it over to the second listbox then im yet to do another button which deducts the value from the quantity field in the table linked with the first listbox.

From the user's point of view:
What happens if they want more than quantity one of a particular product? How do they do that? Multiple rows of same product on second listbox?
What if they change their minds and want to move it back? Add back to stock on hand?
What happens when the product quantity reaches zero? Does it disappear from the "Available" (first) listbox?

[Lu.14-08-14]

What happens if they want more than quantity one of a particular product? - i want the product to show up in the first listbox if still available, so if i have 20 of one product and the user wants 5 then they would be able to add that product to the second listbox 5 times and it would still show in the first listbox because there is still 15 left, when the quantity reaches 0 then it will not show anymore in the first listbox.
What if they change their minds and want to move it back? - i have also made a "remove" button which will send the product back to the first listbox

[davegri]

OK. We need to know more. So far there is only a Products table with a quantity.
Tell us more about the database functionality, and what you expect it to accomplish.

[Lu.14-08-14]

i have sent you a private message

[kd2017]

the listbox is based on a table, so you can run an append query to copy the record lsttBox2's table
BUT dont forget to refresh the listbox2 to see the change:

the following button click will run the query...

sub btnCopy_click()
   docmd.setwarnings false
   docmd.Openquery "qaCopy1ListBoxValue"     'this copies the value
   lstBox2.requery 

   docmd.setwarnings true
end sub

Watch this older MSAccess video from DataPig "the listbox trick"

Both of these methods use command buttons to call a saved query. What are the pros and cons of doing this vs hard coding the sql? EG

Sub btnCopy_click()
  Dim db As DAO.Database
  Set db = CurrentDb
  db.execute "INSERT INTO ..."
  db.close
  Set db = nothing

  lstBox2.requery
End Sub

[ssanfu]

For a saved query, the pro is that it is saved and easy to edit. The con is that it is saved and easy to edit.

For a query in code, the pro is that it is hard to edit. The con is that it is hard to edit.
Building the action query in code allows you to tailor the SQL to exactly what you want. You can skip fields or add fields, depending on the data.
In a saved query, it is what it is. Every time.


In either case, I never use

docmd.setwarnings false
docmd.Openquery "qaCopy1ListBoxValue

I use "DoCmd.Execute (...), dBFailOnError"

Using DoCmd.Openquery, Access first evaluates the SQL then passes it to the DBE. (DataBase Engine)
Using the Execute method, the SQL is passed directly to the DBE. (Faster)




My $0.02........