Want to click checkbox in a form to update a table

[riley23258]

Hello, I'm building a tool for my company to use to track inbound receipts against purchase orders and materials. I have it in such as way where the form for receiving shows a subform, and when a PO# is entered, the subform will display all materails related to that purchase order. I want to be able to click a checkbox and have that "click event" update an Inbound Receipts table with the following: User Name, Supplier Part Number and Quantity. The Supplier Part Number and quantity are from the subform and the user name would then come from the base form. Is there VBA code that would allow this creation of a new record in the receipts table upon clicking the checkbox? (I've attached pictures of the form & subform)

 

[theDBguy]

Hi. Welcome to AWF!

Of course, you can do a lot of stuff using VBA. However, without knowing much about your database structure, I am reluctant to tell you how to do it because there may be a better way of accomplishing what you want. What does checking an item from a materials list have to do with receipts?

 

[LarryE]

It sounds like you want to create a new record in a table based upon the criteria you are entering on a form. I would use an Append action query to do that IF the data table or query you are editing is NOT related to any field in the receipts table. You can use an Append action query to append records in the receipts table based upon criteria in the active form. But theDBGuy is correct. We don't know anything about your table structure. If there is a field in the receipts table that is related with a Primary and Foreign Key to any of the fields on the form, then there might be a better way of accomplishing your goal and still maintain referential integrity. That is what we would be concerned about.

 

[riley23258]

It sounds like you want to create a new record in a table based upon the criteria you are entering on a form. I would use an Append action query to do that IF the data table or query you are editing is NOT related to any field in the receipts table. You can use an Append action query to append records in the receipts table based upon criteria in the active form. But theDBGuy is correct. We don't know anything about your table structure. If there is a field in the receipts table that is related with a Primary and Foreign Key to any of the fields on the form, then there might be a better way of accomplishing your goal and still maintain referential integrity. That is what we would be concerned about.

Thank you for the reply. I've added snapshots of the tables in reference as well as the form and subform structure. And yes, I want to click the checkbox next to the material in the subform and have it create a new record in the "InboundReceiptsTbl".

 

[riley23258]

Hi. Welcome to AWF!

Of course, you can do a lot of stuff using VBA. However, without knowing much about your database structure, I am reluctant to tell you how to do it because there may be a better way of accomplishing what you want. What does checking an item from a materials list have to do with receipts?

Hi and thanks for the welcome and response.

I want the end user to be able to click the checkbox next to the material they are receiving. The materials list is populated via a Purchase Order entry form. So when the user goes to receive these materials, they can simply type in the PO# and the subform will show them all the materials ordered. Then, they can go down the list and check the items they actually have. I would like this checkbox to create a new record in the "InboundReceiptsTbl" that shows user name, part number, qty received and date.

 

[LarryE]

Thank you for the reply. I've added snapshots of the tables in reference as well as the form and subform structure. And yes, I want to click the checkbox next to the material in the subform and have it create a new record in the "InboundReceiptsTbl".

Looking at your table structure, I see you already have a relationship between PurchaseOrders and InboundReceipts using the OrderNo field. That is as it should be but the OrderNo field in the InboundReceipts table should NOT be a Primary Key, it should be a Foreign Key to the OrderNo field in the PurchaseOrders table and therefore, the OrderNo field in the InboundReceipts table must allow duplicate OrderNo entries.

1. Open the InboundReceipts table
2. Remove the Primary Key symbol from the OrderNo field
3. Below, find the Indexed property, and change Yes (No Duplicates) to Yes (Duplicates OK). That will allow multiple OrderNo entries from the PurchaseOrder table into the InboundReceipts table.

Now you need to get the OrderNo field in the InboundReceipts table updated when new OrderNo’s are entered into the PurchaseOrders table.

This is where you need to run an Append Action Query to add new OrderNo’s into the InboundReceipts table based on the criteria from your Receiving Form when new OrderNo’s are entered.

 

[riley23258]

Looking at your table structure, I see you already have a relationship between PurchaseOrders and InboundReceipts using the OrderNo field. That is as it should be but the OrderNo field in the InboundReceipts table should NOT be a Primary Key, it should be a Foreign Key to the OrderNo field in the PurchaseOrders table and therefore, the OrderNo field in the InboundReceipts table must allow duplicate OrderNo entries.

1. Open the InboundReceipts table
2. Remove the Primary Key symbol from the OrderNo field
3. Below, find the Indexed property, and change Yes (No Duplicates) to Yes (Duplicates OK). That will allow multiple OrderNo entries from the PurchaseOrder table into the InboundReceipts table.

Now you need to get the OrderNo field in the InboundReceipts table updated when new OrderNo’s are entered into the PurchaseOrders table.

This is where you need to run an Append Action Query to add new OrderNo’s into the InboundReceipts table based on the criteria from your Receiving Form when new OrderNo’s are entered.

Thank you! That actually makes 100% sense about not having OrderNo be a primary key in InboundReceiptsTbl. I've made it foreign, and have created the append query. I'm still stuck on how to make this function when the user clicks the checkbox.

The help thus far is much appreciated!

 

[LarryE]

I also noticed you have many identical fields in different tables. That goes against every construct in ACCESS and any relational database system. You should enter data only once in a single table and let the table relationships handle the rest.

This design is much too complex to maintain. You have tables and fields which are unnecessary and relationships that cannot be used effectively.

It needs redesigning from the beginning. I will look further and make some recommendations.

 

[riley23258]

I also noticed you have many identical fields in different tables. That goes against every construct in ACCESS and any relational database system. You should enter data only once in a single table and let the table relationships handle the rest.

This design is much too complex to maintain. You have tables and fields which are unnecessary and relationships that cannot be used effectively.

It needs redesigning from the beginning. I will look further and make some recommendations.

Thanks for the tip LarryE. I've noticed this as well as I've begun constructing reports and forms. I created a copy of the DB and began cleaning it up where possible.

 

[LarryE]

Here is a very basic design based on what you have. You have:

1. Multiple Suppliers
2. Which have multiple materials for sale
3. Which may be included in one or more of your Purchase Orders
4. Each Purchase Order material item has a Received Date and Quantity Received. It's possible you may not receive all items ordered on a single day. Each Receipt form related to any Purchase Order stays active and open until all items are received. Once each material item ordered has a received date, you can then take some action to mark the Purchase Order as complete. But if an item is received but damaged or not acceptable, you might include a ReturnedDate field and ReasonReturned field so returned items can be tracked as well.

Of course, you need to complete all the fields in each table, but remember, you enter supplier data, materials data, Purchase Order data and Receipts data only once.

I would:

1. Make the Inbound Receipts form a SubForm of the Purchase Orders form
2. Then make the Purchase Orders form a SubForm of the Materials Form
3. Then make the Materials form a SubForm of the Suppliers form

That way, you can take advantage of using form Master/Child fields so ACCESS will filter your forms for you automatically and maintain referential integrity easily.

 

[riley23258]

Here is a very basic design based on what you have. You have:

1. Multiple Suppliers
2. Which have multiple materials for sale
3. Which may be included in one or more of your Purchase Orders
4. Each Purchase Order material item has a Received Date and Quantity Received. It's possible you may not receive all items ordered on a single day. Each Receipt form related to any Purchase Order stays active and open until all items are received. Once each material item ordered has a received date, you can then take some action to mark the Purchase Order as complete. But if an item is received but damaged or not acceptable, you might include a ReturnedDate field and ReasonReturned field so returned items can be tracked as well.

Of course, you need to complete all the fields in each table, but remember, you enter supplier data, materials data, Purchase Order data and Receipts data only once.

I would:

1. Make the Inbound Receipts form a SubForm of the Purchase Orders form
2. Then make the Purchase Orders form a SubForm of the Materials Form
3. Then make the Materials form a SubForm of the Suppliers form

That way, you can take advantage of using form Master/Child fields so ACCESS will filter your forms for you automatically and maintain referential integrity easily.

View attachment 98734

Thank you so much for the clarification. I think I've got my relationships and Tbls setup correctly now. I'm going to rework my forms as you mentioned in the "I would:" section.

Thank you again for all the help today! I'm confident I'll be reaching out again tomorrow.

 

[LarryE]

You cannot use Part Number as a Primary key and foreign key. You need to use MaterialID which should be an AutoNumber data type in the Materials table. All Primary keys should be AutoNumber data types. Primary keys and foreign keys are just references. Nothing more. They identify unique records in each table. Part Numbers are input by users. They do not identify unique records because you could order a single part number many times.

Use the MaterialID field in the Materials table as a Primary key and as a foreign key in the Purchase Orders table. Set the data type for the MaterialID field in the Materials table to AutoNumber, set its New Values property to Increment, and set its Index property to Yes (No Duplicates).

Then in the Purchase Orders table, create a MaterialID field with data type Number and set its Index property to Yes (Duplicates OK).

Primary keys and foreign keys are what ACCESS uses to maintain referential integrity. They MUST be unique numbers and cannot be input. Foreign keys are also just references but can be duplicated and are not unique. They link the table fields together.