asked on

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.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lisa yu

ASKER

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?

Gustav Brock

It is. I described how.

lisa yu

ASKER

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?

Gustav Brock

It is the table that can have a field added. Then add a checkbox to the subform bound to that field.

PatHartman

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.

Gustav Brock

Methods provided.