Reference a subform 3

[kieranplatt1]

I have a form showing a clients record which has a subform showing brief details of the clients past orders. I would like to make the subform clickable so that if a 'Sale ID' is clicked on the list another form called 'Sales Record' opens and shows full details of that sale.

Ideally my where condition should be something like this I think...
[Sale ID] Like [Forms]![ClientForm]![SUBFORM]![Sale ID]

I am having a problem referencing the subform though it does not appear to have a name and is not listed on the forms part of the database!

Can anyone help please?

 

[tunsted]

The subform is actually a control on the parent form. To refer to a control on the subform itself, you need to use the Form propery of the subform, e.g.

[Forms]![ClientForm]![SUBFORM].Form.[Sale ID]

This should work.

 

[kieranplatt1]

This doesn't work - I guess I need to substitute [SUBFORM] with the actual name of the subform but it doesn't have one!

Any ideas?

 

[RoyVidar]

As tunsted stated, the subform isn't recognized as an open form, but a control on the main form.

There's some additional challenges too, you'll need to use the subform control name, which might differ from the name of the subfrom as viewed in the database.

Easiest way (I think) to obtain correct reference, is to go thru the expression builder (right click a controlsource or in the criteria row in a query), then doubleclick thru forms, loaded forms, main form, subform, the control in question then hit OK.

Roy-Vidar

 

[RoyVidar]

Oups - didn't see the "I am having a problem referencing the subform though it does not appear to have a name and is not listed on the forms part of the database!" - sounds strange.

Couple of questions:
* is it really a subform (or perhaps a listbox)?
* might there be some corruptions

Id design view of the main form, what properties pop up in the properties dialog when clicking on the "subform"?

Roy-Vidar

 

[kieranplatt1]

I tried using expression builder as you suggest - I think the subform is referred to as 'Child250' or 'New Sales Subform' but neither these seem to work or display field names under expression builder- the db pops up a box for me to manually enter the number instead.

I tried..
[Sale ID] Like [Forms]![ClientsTab]![New Sales Subform]![Sale ID] as the WHERE condition under the OpenForm command in a macro?? and also tried substituting 'new sales subform for 'Child250'

regards

Kieran

 

[kieranplatt1]

Sorry it does work with Child250 as the name BUT it is slow searching through 30000 records - is there any way of doing it via a module to speed it up.

regards

Kieran

 

[RoyVidar]

One idea, could be to keep the form open (just use it's visible property to toggle visible/not visible). Then toggle the filter property of the form.

[tt]forms!YourOtherForm.filter= "[Sale ID] = " & _
[Forms]![ClientsTab]![Child250]![Sale ID]
forms!YourOtherForm.filteron=true[/tt]

Using the = operator in stead of Like?

There are also numerous posts on speeding up form loading (a search here would reveal some of them) - one of the "main items" is turning off the Name Autocorrect options (Tools | Options - general tab). Some more general "speeding up" links:
Microsoft Access Performance FAQ
Making Access Faster - 168 Tips to Speed Up Your Apps!

Roy-Vidar

 

[kieranplatt1]

Thank you both v much!

Kieran