Hey,
I'm working on a project which is basically a stock control system with a few extras. One of those extras that's causing me lots of grief right now is a method to place manual orders with a supplier. The form I have trouble with is based on 2 data tables, and 2 linked tables. I can get that to work fine, the problem definitely isn't in the table design.
The form is based on a supplier table. All the supplier info comes up, and Supplier code (the primary key) is filled in with a combo box. There's a purchase order ID there too from a linked table, but it's not important.
There is a sub-form which is based on a query. That query takes the stock table and the Purchase Order ID (also used in the supplier section). The sub-form displays records that match both the supplier code (foreign key in stock table) and the Purchase Order ID. At the start of filling in the form, nothing is displayed here as a new Purchase Order ID is used each time.
In the sub-form is a combo box that is based on a query. This query uses the content of the supplier code as the criteria in supplier code on the stock table. This works, and displays all the stock items that are available for the selected supplier.
The problem I have is that while the first supplier chosen correctly finds the stock with the query on the combo box, updating the supplier combo box doesn't update the records available in the stock combo box.
I have tried a Requery macro with the stock combo box as the command name, and tried assigning this to the onchange of the supplier combo box. The macro must be calling successfully because I get an error, but, well, I get an error and it doesn't update the stock combo box correctly. The error is:
"There is no field named CB_Stock in the current record"
That name is definitely correct, so my best guess is it errors because the stock combo box is in a sub-form.
My question then is, is there any way I can update a field in a sub-form with a Macro from the main form?
Sorry I didn't think of this first, but I'm using Access 2003.
Solved it, thanks anyway! And to anyone with a similar problem:
Solution
Right click the top left corner of the subform (the square)
Select properties
Select event
In on current, select a macro that requeries a control in the subform
Remove any calls of that macro on anything outside of the subform.
I'm working on a project which is basically a stock control system with a few extras. One of those extras that's causing me lots of grief right now is a method to place manual orders with a supplier. The form I have trouble with is based on 2 data tables, and 2 linked tables. I can get that to work fine, the problem definitely isn't in the table design.
The form is based on a supplier table. All the supplier info comes up, and Supplier code (the primary key) is filled in with a combo box. There's a purchase order ID there too from a linked table, but it's not important.
There is a sub-form which is based on a query. That query takes the stock table and the Purchase Order ID (also used in the supplier section). The sub-form displays records that match both the supplier code (foreign key in stock table) and the Purchase Order ID. At the start of filling in the form, nothing is displayed here as a new Purchase Order ID is used each time.
In the sub-form is a combo box that is based on a query. This query uses the content of the supplier code as the criteria in supplier code on the stock table. This works, and displays all the stock items that are available for the selected supplier.
The problem I have is that while the first supplier chosen correctly finds the stock with the query on the combo box, updating the supplier combo box doesn't update the records available in the stock combo box.
I have tried a Requery macro with the stock combo box as the command name, and tried assigning this to the onchange of the supplier combo box. The macro must be calling successfully because I get an error, but, well, I get an error and it doesn't update the stock combo box correctly. The error is:
"There is no field named CB_Stock in the current record"
That name is definitely correct, so my best guess is it errors because the stock combo box is in a sub-form.
My question then is, is there any way I can update a field in a sub-form with a Macro from the main form?
Sorry I didn't think of this first, but I'm using Access 2003.
Solved it, thanks anyway! And to anyone with a similar problem:
Solution
Right click the top left corner of the subform (the square)
Select properties
Select event
In on current, select a macro that requeries a control in the subform
Remove any calls of that macro on anything outside of the subform.
Last edited:
