Forms - Moving data between subforms

I have recently started to develop a database using Access 2003 to handle the purchase of materials for a business.

One of my main forms, called frmMaterials, deals with every aspect of placing an order with a supplier. It contains 4 tabs each with a different section of the order process.

The first tab concerns placing an order from a pre-generated list of required materials. This tab contains three subforms based on different queries and in a table view.

Subform 1, named subRequired, lists all the required materials for that particular user.

Subform 2, named subMaterials, lists all the materials that exist in the database and is filtered by category which is selected in a combobox nearby.

Subform 3, named subBasket, is a basket where all items selected as part of this order will reside until a user confirms the order using a button.

What I am struggling with is trying to get items selected in subform 2 to have their details copied to subform 3 when an "order" button is pressed (or the record in subform 2 is double clicked).

I have looked through a number of articles on the forums and found some snippets of code which could possibly be related to my issue but so far I havent been able to get them to work in my particular circumstance.

Any help would be much appreciated.

Cheers,

Rich

What do you mean by 'copied'. You should not be saving the same data in multiple tables. If what you need to do is display updates to form and subform queries then use the Requery method. If these form/subforms can have master/child links, the Requery may not be needed.

Ah yes i see what you mean, I dont want to be copying all the data to another table at all. I should know better really. So what I would need in the table behind subform 3 would be only the Material ID (which would be able to give me all the information from the materials table without replication of data), Quantity Ordered and a Date ordered.

So what i need to be able to do is select a record in subform 2 and have the current record's Material ID placed in a new record in subform3 when a button is pressed. The user could then manually fill in quantity required.

Looking through the tutorial videos in the stickies I have found that i can reference the subforms using:

Me.subMaterials.Form!MaterialID

So maybe i could make some code for a button something like:


I will experiment a bit more and see what i come up with.

I have been working off and on with this part of my database and have managed to come up with the following VB and SQL. This code is attached to a button's on click event for the moment.



For some reason however it is asking me to manually provide values for the CategoryName and Description in the SELECT part of the SQL.

I tried to replicate the code present on the MSDN page for Insert Into. Am I missing something obvious?

You can view or enter data in a main form and in a subform by switching back and forth between them to move through their records or to add new records.....


Thanks and hope it's can useful

I tried modifying the SQL a bit to:



It produces the following error:

Run-time error '3075'

Syntax error (missing operator) in query expression 'SPEED ADHESIVE'.

SPEED ADHESIVE is the Description of the product i had selected in the subform so it is correctly grabbing this info.

any hints?

Use apostrophe delimiter for string values:

DoCmd.RunSQL "INSERT INTO tblBasket (Description, BasketID)
VALUES ('" & Me.subOrder.Form!Description & "', '0001')"