how to open Sub form

[questionit]

I am trying to open Sub Form using the command below, but i am getting error.

Please confirm if i can open sub form using this command:


Call DoCmd.OpenForm("Forms![main_form]![sub_form]", acNormal, , , , acWindowNormal)

[missinglinq]

A subform would normally open automatically when the main form it's part of opens. If you're trying to independantly open a form that is normally used as a subform, you simply open it as you would any other form, with no reference to it being a "sub" form.

[questionit]

A subform would normally open automatically when the main form it's part of opens. If you're trying to independantly open a form that is normally used as a subform, you simply open it as you would any other form, with no reference to it being a "sub" form.

I am opening the main form, which has a subform in it as well. I know you are saying when i open the main form, the subform gets opened as well.

But unless i open the subform explicity like this:

'Call DoCmd.OpenForm("sub_search_form", acNormal, , , , acWindowNormal)

The following piece of code will be higlighted and error will appear :

 Dim formSub As Form
           Set formSub = Forms.Item("sub_search_form")

The error is: 2450 - Cant find the form
sub_search_form

But when i open it explicity as i mentioned above, no problem happens

I am opening the main form similar way as above

Any idea whats wrong

[questionit]

How would you open a subform only. This subform is on a mainform but i want to open the subform only , not the whole form.

how would i do that

I have tried with this:

Call DoCmd.OpenForm (" Forms![main_form] ! [sub_form] ")

but this gives me error: not found...


help?

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

MODERATOR.

[NeoPa]

Apart from the CODE tags, your question is impossible to answer as you don't give us the information we need. It's not the Experts job to get that information from you. If you want help, go to the trouble yourself of posting a question that makes sense.

[questionit]

I need to open subform only because i need to do some ADO operations with it , not because i want to show it on screen.

If i open the main form, which has subform on it. I know you are saying by opening the main form, the subform gets opened as well. But if i open the main form, i cant do any ADO works with the subform.

For example:

I open main form:

Call DoCmd.OpenForm("main_form", acNormal, , , , acWindowNormal)
 

I want to use the code below for subform:
 Set formSub = Forms.Item("sub_form")
   formSub.RecordSource = sqlQry
 

To do this, i have to open the subform physically with DoCmd.OpenForm.

Now when i want to close the mainform, i will have to close the subform as well seperately -- Then whats the purpose of having a subform on a mainform!

Thats why i want a way by which i open the mainform, by which its subform gets opened as well for any DAO, etc operations. And when i close the mainfom, i dont need to explicity close the subform.

Apart from the CODE tags, your question is impossible to answer as you don't give us the information we need. It's not the Experts job to get that information from you. If you want help, go to the trouble yourself of posting a question that makes sense.

[questionit]

Is it possible to do the following without opening the form :

Dim subForm As Form
           Set subForm = Forms.Item("sub_form_client")
           subForm.RecordSource = sqlQry
 

If the form is not opened, then i get run-time error - form not found !

[Twanne]

Just add the subfrom to your mainform in design mode. When you explicitly open the form in your code then. You get the form 2 times. Once in your main form, and once as a main form. The second one is the one you don't want because you have to close it by hand each time.

So normally you should be able to see the subform on you main form if you added it correctly. When you don't want to see it you can set the property visibly of the subfotm to false.

Ps. Are you useing DAO or ADO because they have their differences...

[questionit]

Ok, so far so good

If i open the subform on its own. it seems to work i.e show updated results, etc

But when i open the main form, which has the subform on it, there it doesn't show the updated results..

any idea why is that?

Just add the subfrom to your mainform in design mode. When you explicitly open the form in your code then. You get the form 2 times. Once in your main form, and once as a main form. The second one is the one you don't want because you have to close it by hand each time.

So normally you should be able to see the subform on you main form if you added it correctly. When you don't want to see it you can set the property visibly of the subfotm to false.

Ps. Are you useing DAO or ADO because they have their differences...

[Twanne]

Try to adjust your recordsource and filter. It is possible the error is situated there. I had to search for the correct filter for about 2 hours this morning.

Also you might want to link your mainform and your subform trough an ID. That way they know af each other and know what to display. It is a property of the subform. In dutch: "Subvelden koppelen" and "Hoofdvelden koppelen". Literally translated: Link subfields and Link mainfields. Working in a dutch environment zo I don't know what it is in English.
Greetz

[MikeTheBike]

Is it possible to do the following without opening the form :

Dim subForm As Form
           Set subForm = Forms.Item("sub_form_client")
           subForm.RecordSource = sqlQry
 

If the form is not opened, then i get run-time error - form not found !

Basically no, but why would you want to ?

MTB

[questionit]

Tried best, but no sucess
I need to do the following:

 
  sqlQry = "SELECT name, age FROM table1"
          Forms![client_search].Form.RecordSource = sqlQry
 

When i do it on the actual form, it works but when that form is on another form as a subform it doesn't work (i mean it does get updated)

client_search form is a "Continous Form"

can i do this kind of stuff on a form when its a subform - i wonder?

Try to adjust your recordsource and filter. It is possible the error is situated there. I had to search for the correct filter for about 2 hours this morning.

Also you might want to link your mainform and your subform trough an ID. That way they know af each other and know what to display. It is a property of the subform. In dutch: "Subvelden koppelen" and "Hoofdvelden koppelen". Literally translated: Link subfields and Link mainfields. Working in a dutch environment zo I don't know what it is in English.
Greetz

[missinglinq]

I've merged these two posts together, questionit, since you're asking the same identical question again! I would suggest, as NeoPa has already suggested, that your read (or re-read) How to Ask a Question paying particular attention to the section on Double Posting.

Linq

[FishVal]

Tried best, but no sucess
I need to do the following:

 
  sqlQry = "SELECT name, age FROM table1"
          Forms![client_search].Form.RecordSource = sqlQry
 

When i do it on the actual form, it works but when that form is on another form as a subform it doesn't work (i mean it does get updated)

client_search form is a "Continous Form"

can i do this kind of stuff on a form when its a subform - i wonder?

Hi!

Form opened as subform does not exist in Forms collection. You may reference subform Form object the following way:

Forms![MainForm]![SubForm].Form

But be sure the subform was really opened (Access will open it when it will need to display it). E.g. if a main form is in datasheet view, then its subform will not be loaded until the datasheet was not expanded and any VBA referencing to it will cause an error.

[questionit]

Hi FishVal

What you mean by 'datasheet not Expanded' ? By the way, mymain form is 'Single form

As you have said way to open the subform, when i do the following, i get wrror, - form doesn't exist - can you check if this is correct:

Call DoCmd.OpenForm("Forms![client_form_main]![client_form_sub].Form", acNormal, , , , acWindowNormal)
 
 

Hi!

Form opened as subform does not exist in Forms collection. You may reference subform Form object the following way:

Forms![MainForm]![SubForm].Form

But be sure the subform was really opened (Access will open it when it will need to display it). E.g. if a main form is in datasheet view, then its subform will not be loaded until the datasheet was not expanded and any VBA referencing to it will cause an error.

[missinglinq]

FishVal said that

Forms![MainForm]![SubForm].Form

was the way to reference the subform, he didn't say it was the way to open the subform! He told you, as I have previously, that the subform is opened when the parent form is opened. The problem, I think, is that you're trying to open the form

Forms![client_form_main]![client_form_sub].Form

and this is not a form! It's an object (control) on the form client_form_main, but it's not a form! You can't open it anymore than you could open a textbox or a command buttton.

Linq ;0)>

[FishVal]

Hi FishVal

What you mean by 'datasheet not Expanded' ? By the way, mymain form is 'Single form

As you have said way to open the subform, when i do the following, i get wrror, - form doesn't exist - can you check if this is correct:

Call DoCmd.OpenForm("Forms![client_form_main]![client_form_sub].Form", acNormal, , , , acWindowNormal)
 
 

1. As soon as your main form is in Single form view you'll never deal with "subform late opening". Plz don't mention this.
2. I've said this is the way to get a Form object of subform opened on a main form, which will be done automatically as soon as you have placed subform control to the main form.

Dim frmSubForm As Form
Set frmSubForm = Forms![client_form_main]![client_form_sub].Form
frmSubForm.RecordSource = "...." 

or simply

Forms![client_form_main]![client_form_sub].Form.RecordSource = "...." 

or in the mainform module

Me![client_form_sub].Form.RecordSource = "...." 

Summarizing all above:
1. If you open subform as a separate form with DoCmd.OpenForm, then Forms![client_form_sub] will return you Form object.
2. If subform is a control on the main form (usual situation), then it will be opened automatically and Forms![client_form_main]![client_form_sub].Form will return you Form object.

[FishVal]

The problem, I think, is that you're trying to open the form

Forms![client_form_main]![client_form_sub].Form

and this is not a form! It's an object (control) on the form client_form_main, but it's not a form! You can't open it anymore than you could open a textbox or a command buttton.

Linq ;0)>

Hi, Linq.

You are somewhat confused, I suppose.

Forms![client_form_main]![client_form_sub] will return control
Forms![client_form_main]![client_form_sub].Form will return form

It could be easily checked in VBA Intermediate using TypeName function (BTW very useful one)

? TypeName(Forms![client_form_main]![client_form_sub])
SubForm
? TypeName(Forms![client_form_main]![client_form_sub].Form)
Form_client_form_sub

The latter is name of class created by Access for this particulary form, it provides all form properties needed.

[questionit]

Hi FishVal

I have got everything working now with your help
Thanks a lot for your help.

Hi, Linq.

You are somewhat confused, I suppose.

Forms![client_form_main]![client_form_sub] will return control
Forms![client_form_main]![client_form_sub].Form will return form

It could be easily checked in VBA Intermediate using TypeName function (BTW very useful one)

? TypeName(Forms![client_form_main]![client_form_sub])
SubForm
? TypeName(Forms![client_form_main]![client_form_sub].Form)
Form_client_form_sub

The latter is name of class created by Access for this particulary form, it provides all form properties needed.

[FishVal]

Hi FishVal

I have got everything working now with your help
Thanks a lot for your help.

You are welcome. Glad my advice was helpful.

[questionit]

Hi fishVal,

Can you give me some other good functions like TypeName to use in Immediate window

Thanks

Hi, Linq.

You are somewhat confused, I suppose.

Forms![client_form_main]![client_form_sub] will return control
Forms![client_form_main]![client_form_sub].Form will return form

It could be easily checked in VBA Intermediate using TypeName function (BTW very useful one)

? TypeName(Forms![client_form_main]![client_form_sub])
SubForm
? TypeName(Forms![client_form_main]![client_form_sub].Form)
Form_client_form_sub

The latter is name of class created by Access for this particulary form, it provides all form properties needed.

[FishVal]

Hi fishVal,

Can you give me some other good functions like TypeName to use in Immediate window

Thanks

Hi, there.

You can use any VBA function or/and object property/method in Immediate window, don't know which ones are generally better than others. :)

BTW what concerns TypeName function, I particury prefer to use it to quickly see a type of some deeply nested object property, in generally Watch window is more suitable to inspect regular and object variables during code tracing.