Set Value from one field on main form to field on subform

[burrina]

Here are the forms. I have not changed the modifications back yet.

[burrina]

Here are the original queries,forms and tables.

[Rod]

Sorry, the tables were there! The conversion to v2007 switched on the 'show forms only' option and I missed it. I apologise for any extra work.

The message is appearing because someone has renamed the products table.



The row source SQL is looking for a table called Products but all that exists is a table called ProductsNOLONGERUSING.

I suggest you rename the table or import the properly named table into your db.

Meanwhile I'll have a look at the original problem.

[burrina]

Yes, I renamed the Products table while beta testing but this caused a major problem. I also created a new table to replace the Products table named TProductsSubTable for the subform AddProductsSub. So I now have the original forms AddProducts and AddProductsSubform and the newly created forms AddProductsNEW and AddProductsSubformNEW. The subform TimeCardCatAndProdSub still uses the original Orders table. Maybe if I knew how to change the sql code, I could point it to the new table named TProductSubTable. Thanks Rod, I hope I have explained things well enough for you to take a look at. When you click on the subform on the TimeCards form and choose a quantity, then when you choose a category, this is where the lookup code fires. In design mode click on Category which has a name of Cat, it is a combo and take a look at the after update event code.

In theory I should be able to use my newly created form FProductMultiItemSelect and then make the append query add the PRODUCTS selected to the form. I suppose I could try and use a set value macro as well. ???

[Rod]

SELECT Products.[Product Name], Products.[Unit Price], Products.[Category ID] FROM Products WHERE (((Products.[Category ID])=[Cat])) ORDER BY Products.[Product Name];

For the particular SQL that caused you problems, the table name is highlighted in red, the table field names are highlighted in blue and the remaining '[Cat]' is the name of the control on the form.

Where to find it?



Go to the Data tab in the Property Sheet in the design view of the subform. Row Source contains the SQL. If you want to edit the SQL in a new window, select it and press Shift+F2.

PS Will investigate the rest of your post later.

[burrina]

Thanks, I reverted back to my original code, table and forms.

[Rod]

Nothing wrong with the concept except there's a typo: the table is catalogued with the name, TProductSubTable, whereas the SQL refers to TProductsSubTable.

Also the SQl switches between TProductsSubTable and Products. Is this what you want?

[burrina]

Maybe it is just me, but it works fine the way it is. Anyway, I don't have enough code experience to fix it if I make any changes. The db works the way it is except for the fact that I can only add items one at a time. This is my last hurdle I think!