Accessing subforms through control buttons

[Lol Owen]

Hi, new to the forum, wonder if anyone can be of help. I'm currently studying an HNC in Business IT and have to complete a small database in MS Access. My part relates to stock control (surprise). I have, for example, a main form with an embedded subform. The main form contains such details as order informartion/customer information. The subform contains multiple fields for product details such as product ID, name, quantity in stock, quantity ordered. I am trying to get the right syntax to access the subform from a command button on the main form. in the code behind this button I can do simple maths such as debiting order quantity from the stock quantity, and performing a simple save record. However, no matter what I try I cannot get the button to access the subform. I keep getting messages along the lines of " cannot access subform etc". Whilst this is strictly not necessary to complete my project it would make a proper job of it, plus it's driving me mad.

A little info on the course: we receive no tuition in Access, we just get some booklets handed out and that's it, find the rest out yopurselves.

Hope someone can offer some help,

Cheers, Lol

 

[DALeffler]

Ifyou want to access fields in your subform from a command button on the main form, the syntax to use in the On Click event for the button on the main form is:

Me![Main Form Control Name of Subform].Form![Control Name on SubForm]

So if the subform control name on your mainform is My_SubForm, and the control you want to access on the sub form is call sbfrmData, the syntax is:

Me![My_SubForm].Form![sbfrmData]

hth,

Doug.

 

[Lol Owen]

Thanks doug, will give it a try and let you know the result.

Cheers, Lol

 

[Lol Owen]

Doug, the code worked a treat! Thanks a lot. However, it only worked on the first record displayed on the subform, i.e. if the order details comprise 3 lines, only the first line is worked upon. I have tried putting in a Do-Loop but this just causes Access to lock up. This loop approach works on a stand alone form. Can you suggest where i might be going wrong please? The code I have typed in based on your suggestion is :

Do While Me![order Details Subform1].Form![Quantity Ordered]>0
Me![Order Details Subform1].Form![Final Total]=Me![Order Details Subform1].Form![Unit Price]*Me![Order Details Subform1].Form![Quantity Ordered]
Loop

Thanks, Lol

 

[Rich]

What you're trying to do can be done better via queries/calculated controls

 

[Lol Owen]

To be honest Rich I've tried the query/calculated control method and not got very far. Part of the problem is that as part of the order details per line there is a volume discount to be considered. i.e. if the quantity ordered is a certain ammount then a further discount applies. Whilst in Vb I can program this, I was struggling with the syntax in the expression builder. I just cannot find any books on this so I have to take the route I can at least get partial success on i.e. VB

Cheers, Lol

 

[Lol Owen]

Right gents, I've had an epiphany and sorted it through calculated controls. I didn't realise that I could put VB code as an event procedure (like I said I'm effectively self taught). This I have done and it works a treat. Thanks for your advice, especially Doug, for the proper syntax for accessing sub forms.

Cheers, Lol