Passing Values into Order Detail Subform 2

[AaronMV]

I have an "Order Entry" form with an "Item Detail" Subform in datasheet view. These connect to Order and OrderDetail tables. On the Order Entry form, I have a Category combo box (cmbCategory), an Item combo box (cmbItem), a Quantity textbox (txtQuantity), and an "Add Item" button (btnAddItem). cmbCategory is used to filter the choices in cmbItem. My problem is in passing the values from cboItem and txtQuantity into a new row on my continous subform when the cmdAddItem button is clicked. Any assistance with the code for this step is greatly appreciated!

AaronMV

 

[Remou]

How about something like:

'Go to subform control
DoCmd.GoToControl "Item Detail subform"
'Go to new subform record
DoCmd.GoToRecord , , acNewRec
'Fill in fields from main form
Me.[Item Detail subform].Form.[txtQuantity] = Me.[txtQuantity]

 

[AaronMV]

Remou,
Thanks for the help. I added your code to my click event for btnAddItem, but I now get an runtime error 2109: There is no field named "tblOrdersDetail Subform"

This break is occuring on this line:

DoCmd.GoToControl ("tblOrdersDetail Subform")

This is the actual subform name auto-created by Access.

Any insight you can provide is greatly appreciated. As you have probably guessed, I am pretty new to VBA...

AaronMV

 

[Remou]

I suspect this may be the name of the form contained by the subform control. Often the form has the same name as the control, but not always. You will need to look at the form in design view. Click carefully on the edge of the subform control and check the name on the properties sheet. This is the name you want for GoToControl and for Me.[Item Detail subform].

 

[AaronMV]

Remou,

I found the problem with the form name, it helps if I spell it right! Anyway, now that I have that right, I am able to add the item into the subform fields. My new problem is that when I go back to the Category combobox and select another Category/Item to add to the order, the subform record I just added is removed from the subform, but the data from the record has been entered into the OrderDetail table. I don't seem to be able to get the "continuous" subform to enter more than one item at a time.

Any help is greatly appreciated.

AaronMV

 

[Remou]

This sounds a little odd, so I will ramble for a few lines. You imply above that you used the wizards to build the subform. The way that the wizards name subforms is to take the name of the table and stick 'subform' on the end. The implication of this is that a subform control called "tblOrdersDetail Subform" (or similar) contains a form called "tblOrdersDetail Subform" and that this form is based on a table called "tblOrdersDetail". Hence ... but the data from the record has been entered into the OrderDetail table ....
Does this in anyway reflect your set-up?

 

[AaronMV]

Remou, I am not surprised that this sounds odd, as that's the story of my life! Anyway, the Order Form is named frmOrder, and the subform is named "tblOrderDetails subform"
There is no additional nested subform named "tblOrderDetails subform". Is there a particular block of code I can post to assist with debugging? I have a hunch it has to do with the After_Update event procedure on cmbCategory, probably a missing line of code or two (or 10) that retains the first record added to the subform, but I have not been able to conjure the syntax. Here is what I have there:

Private Sub cmbCategory_AfterUpdate()
Me.cmbItem = Null
Me.cmbItem.Requery
Me.cmbItem = Me.cmbItem.ItemData(0)

End Sub

 

[Remou]

What are the link child fields and link master fields?

Please forgive the following over-explanation. I do not mean an additional nest: subforms on a form consist of a subform control and a form contained by the subform control. The subform control and the contained form are two separate things. You can change the form contained to another form or even a table by changing the Source Object for the subform control.

 

[AaronMV]

Link Child, Link Master? Forgive me but I have not used these terms before. Here's what I think you are after: The subform has two rows. The ProductID row in the subform has a Control Source of the ProductID field in the OrderDetails table. The Quantity row in the subform has a Control Source of the Quantity field in the OrderDetails table.

 

[Remou]

I am guessing that you want your form to look something like:

[tt]-----Main Form based on Order table------
OrderID: 123

----Subform based on OrderDetail table---
OrderID ProductID Quantity Whatever
-----------------------------------------
123 1 1000
123 15 20
123 6 400[/tt]

In the example above I have shown OrderID, but in the final version, it will probably not be shown.
For this type of set-up to work, you need to set the Link Child Fields and Link Master Fields on the property sheet for the subform control (these are properties of the control, not the form contained). Both the Link Child and Link Master Fields for the above example would be set to OrderID.
The easiest way to see an example is to create a scratch form for table Orders using Autoform and then use the wizard to add a subform for OrderDetail. At the final stages of the wizard, it will ask you for the Link Child and Link Master fields. You will also find a useful example of an order / order detail form in the Northwind sample database.
I hope I am getting nearer an understanding of your form.

 

[AaronMV]

OK, we are on the same page now. I have verified that the Link Child and Link Master for the Subform control are both set to OrderID. Your Example is, in fact, how my form looks with the exception that I did not include the OrderID field in the subform.

 

[Remou]

Ok. Good. So when you choose the Add Item command button, the records are not showing up? I have created a small outline of your form from the information above, and when I click Add Item, a new row is added to the subform with the data entered in cmbItem and txtQuantity.

I am imagine the story with the code below is that you have different categories of products that limit the cmbItem list. I have tried this code, and it seems to work ok. It does not affect the rows that appear for order items.
[tt]Private Sub cmbCategory_AfterUpdate()
Me.cmbItem = Null
Me.cmbItem.Requery
Me.cmbItem = Me.cmbItem.ItemData(0)
End Sub[/tt]

All this is a little unhelpful, in that I am only saying that it seems to work for me. Is there any other code associated with your form that might be involved?

 

[PHV]

What is the value of the DataEntry property of the subform ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AaronMV]

The DataEntry property was set to Yes. As this is Boolean, I switched it to No to see what happens. Works like a champ now!

Thanks Remou and PHV for all your help with this one.

AaronMV