How to add a checkbox field to a datasheet form created from a query

I created a main form with a datasheet subform that includes 4 fields: ID, Product, MinimumStock, StockOnHand. Subform record sources are from a query.

I now want to add a checkbox field onto the form, so the user can select products that are under minimum qty and then by clicking a button on main form to generate a Production Order of records ticked.

Also, it is possible to show updated stock level in the main ProductTable? How this can be realised?

I just started learning Access for couple months, and only know very little about Macro and VBA. If any programming is needed, please kindly post the code as well.

Thank you for your help in advance.
lisa yuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You can't do this as you described it.  When you add an unbound control to a form, it shows the same value for each row.  So when you check the first row you want to select, every row will be checked.  When you uncheck a row, every row will be unchecked.

If you want to create orders for ALL products that are under the minimum quantity, then you don't want the user to have to check anything.  He can press a button, and you can create orders for every low quantity item and post them and even email them to the suppliers or simply print them for someone to mail them.

If you want the users to choose which items to reorder, then you would probably be better off if you used a multi-select listbox.  Use the low quantity criteria to filter the RowSource so the listbox only includes items that need ordering.  Then the user can select multiple items from the listbox.  Your code will create only orders for selected items.

We would need more information to be more specific about how this should happen.  You might even be able to run the reorder process every day when the first user logs on.  Or, if you prefer you can schedule it to run on Monday's or whenever you want.  My choice would be automatically every day baring information that would preclude that option.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You could add a Yes/No field to the table, say "Selected", and include that on the subform.
This the user could mark and unmark as needed.
0
lisa yuAuthor Commented:
Due to the production capacity, there is a possibility that not all low stock products can be produced on the day. that's why I want to add a checkbox where the production people can choose which products to produce.

If what I want to do is not feasible, any other options I have to realise the request?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Gustav BrockCIOCommented:
It is. I described how.
0
lisa yuAuthor Commented:
Hi Gustav, the Subform record source is from a Query, and is in datasheet view. I have tried to add a Yes/No field (checkbox) to the subform but it didn't work. When I check a row I want to select, every row will be checked.  When I uncheck a row, every other rows is unchecked. Unless I missed something?
0
Gustav BrockCIOCommented:
It is the table that can have a field added. Then add a checkbox to the subform bound to that field.
0
PatHartmanCommented:
When I check a row I want to select, every row will be checked.  When I uncheck a row, every other rows is unchecked. Unless I missed something?
This is exactly what I told you would happen with an unbound control.

You could add a Yes/No field to the table, say "Selected", and include that on the subform.
This the user could mark and unmark as needed.
This is of course possible but I don't ever recommend it because in a multi-user environment, it can cause problems if more than one person attempts the select at the same time.  That is why I suggested the multi-select listbox.
0
Gustav BrockCIOCommented:
Methods provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.