Form/Subform

I'm stuck on something that seems should be easy and I need some help. My
main form has an option group with five options. My subform chooses from
different lists depending on which option is selected in the main form. I
thought all I had to do for the subform was create a query and set the
criteria equal to the option group value in the main form. No way, Jose!
When the form/subform opens, the option group value is not available to the
subform because the subfoem opens ahead of the main form. I can't have an
event in the main form set the query in the subform at data entry because
the form/subform needs to display existing records when the form/subform
opens. How can I set this up?

Thanks,

Steve

re: Form/Subform

PC Datasheet wrote:
[color=blue]
> I'm stuck on something that seems should be easy and I need some help. My
> main form has an option group with five options. My subform chooses from
> different lists depending on which option is selected in the main form. I
> thought all I had to do for the subform was create a query and set the
> criteria equal to the option group value in the main form. No way, Jose!
> When the form/subform opens, the option group value is not available to the
> subform because the subfoem opens ahead of the main form. I can't have an
> event in the main form set the query in the subform at data entry because
> the form/subform needs to display existing records when the form/subform
> opens. How can I set this up?[/color]

There may be better ways to do this, but I run into this a lot. What I
do is set the subform query to either a specific criteria that matches,
but does not refer to the option group:

Select blah blah from Ooga booga Where MY_FIELD = 1

Where 1 is the default value of the option group.

Then, in the after update event of the option group I have something
like this:

Me.SubForm.Form.Recordsource = "Select blah blah from Ooga booga Where
MY_FIELD = " & me.OptionGroupValue

There might be another way around it, but if there is, I've gotten set
in my probably over-complicated ways...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Form/Subform

Tim,

Thanks for responding!

I see where what you do will work for data entry but what do you do where
you have existing records in the database where the first record in the main
form's subform has records based on the option group value of 3 (rather than
1)? How do you get 3 from the main form to the subform when the subform is
opening before the main form has opened?

Steve


"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
news:defceg$qoj$1@coranto.ucs.mun.ca...[color=blue]
> PC Datasheet wrote:
>[color=green]
>> I'm stuck on something that seems should be easy and I need some help. My
>> main form has an option group with five options. My subform chooses from
>> different lists depending on which option is selected in the main form. I
>> thought all I had to do for the subform was create a query and set the
>> criteria equal to the option group value in the main form. No way, Jose!
>> When the form/subform opens, the option group value is not available to
>> the subform because the subfoem opens ahead of the main form. I can't
>> have an event in the main form set the query in the subform at data entry
>> because the form/subform needs to display existing records when the
>> form/subform opens. How can I set this up?[/color]
>
> There may be better ways to do this, but I run into this a lot. What I do
> is set the subform query to either a specific criteria that matches, but
> does not refer to the option group:
>
> Select blah blah from Ooga booga Where MY_FIELD = 1
>
> Where 1 is the default value of the option group.
>
> Then, in the after update event of the option group I have something like
> this:
>
> Me.SubForm.Form.Recordsource = "Select blah blah from Ooga booga Where
> MY_FIELD = " & me.OptionGroupValue
>
> There might be another way around it, but if there is, I've gotten set in
> my probably over-complicated ways...
> --
> Tim http://www.ucs.mun.ca/~tmarshal/
> ^o<
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me[/color]

re: Form/Subform

Initially set NO recordsource for the subform.
Assign the recordsource for the subform in the mainform's Activate event like:

Private Sub Form_Activate()
Me!MySubform.Form.Recordsource= "SQL or query here"
End sub

Arno R

"PC Datasheet" <nospam@nospam.spam> schreef in bericht news:UYHOe.790$FW1.138@newsread3.news.atl.earthlin k.net...[color=blue]
> Tim,
>
> Thanks for responding!
>
> I see where what you do will work for data entry but what do you do where
> you have existing records in the database where the first record in the main
> form's subform has records based on the option group value of 3 (rather than
> 1)? How do you get 3 from the main form to the subform when the subform is
> opening before the main form has opened?
>
> Steve
>
>
> "Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
> news:defceg$qoj$1@coranto.ucs.mun.ca...[color=green]
>> PC Datasheet wrote:
>>[color=darkred]
>>> I'm stuck on something that seems should be easy and I need some help. My
>>> main form has an option group with five options. My subform chooses from
>>> different lists depending on which option is selected in the main form. I
>>> thought all I had to do for the subform was create a query and set the
>>> criteria equal to the option group value in the main form. No way, Jose!
>>> When the form/subform opens, the option group value is not available to
>>> the subform because the subfoem opens ahead of the main form. I can't
>>> have an event in the main form set the query in the subform at data entry
>>> because the form/subform needs to display existing records when the
>>> form/subform opens. How can I set this up?[/color]
>>
>> There may be better ways to do this, but I run into this a lot. What I do
>> is set the subform query to either a specific criteria that matches, but
>> does not refer to the option group:
>>
>> Select blah blah from Ooga booga Where MY_FIELD = 1
>>
>> Where 1 is the default value of the option group.
>>
>> Then, in the after update event of the option group I have something like
>> this:
>>
>> Me.SubForm.Form.Recordsource = "Select blah blah from Ooga booga Where
>> MY_FIELD = " & me.OptionGroupValue
>>
>> There might be another way around it, but if there is, I've gotten set in
>> my probably over-complicated ways...
>> --
>> Tim http://www.ucs.mun.ca/~tmarshal/
>> ^o<
>> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
>> /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me [/color]
>
>[/color]

re: Form/Subform

Are you trying to set the subform's query string dynamically, such as in the
subform's onLoad event procedure?
If you create a saved query like:
SELECT foo from tblMyTable where some_field = forms!frmMain.frame1
then the subform should have no problem loading the correct records when the
form opens, becuase the form will requery the subform when it opens.
Maybe there is some reason you can't do this, but I didn't see it in your
question.

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:rhGOe.765$9i4.294@newsread2.news.atl.earthlin k.net...[color=blue]
> I'm stuck on something that seems should be easy and I need some help. My
> main form has an option group with five options. My subform chooses from
> different lists depending on which option is selected in the main form. I
> thought all I had to do for the subform was create a query and set the
> criteria equal to the option group value in the main form. No way, Jose!
> When the form/subform opens, the option group value is not available to
> the subform because the subfoem opens ahead of the main form. I can't have
> an event in the main form set the query in the subform at data entry
> because the form/subform needs to display existing records when the
> form/subform opens. How can I set this up?
>
> Thanks,
>
> Steve
>[/color]

re: Form/Subform

Thamks for responding, John!

This is he point where my question started. I initially did what you are
saying and Access threw up a parameter dialog asking for the value of
forms!frmMain.frame1because the subform opened ahead of the main form and
forms!frmMain.frame1 was not available to the subform's query criteria.

Steve


"John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
news:defnb202aff@enews3.newsguy.com...[color=blue]
> Are you trying to set the subform's query string dynamically, such as in
> the subform's onLoad event procedure?
> If you create a saved query like:
> SELECT foo from tblMyTable where some_field = forms!frmMain.frame1
> then the subform should have no problem loading the correct records when
> the form opens, becuase the form will requery the subform when it opens.
> Maybe there is some reason you can't do this, but I didn't see it in your
> question.
>
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:rhGOe.765$9i4.294@newsread2.news.atl.earthlin k.net...[color=green]
>> I'm stuck on something that seems should be easy and I need some help. My
>> main form has an option group with five options. My subform chooses from
>> different lists depending on which option is selected in the main form. I
>> thought all I had to do for the subform was create a query and set the
>> criteria equal to the option group value in the main form. No way, Jose!
>> When the form/subform opens, the option group value is not available to
>> the subform because the subfoem opens ahead of the main form. I can't
>> have an event in the main form set the query in the subform at data entry
>> because the form/subform needs to display existing records when the
>> form/subform opens. How can I set this up?
>>
>> Thanks,
>>
>> Steve
>>[/color]
>
>[/color]

re: Form/Subform

I do this all the time and it works fine. Just to make sure, I made a test
case to try it out again before my original post, and it worked fine.
Could you be spelling something wrong?

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:VMJOe.752$_84.189@newsread1.news.atl.earthlin k.net...[color=blue]
> Thamks for responding, John!
>
> This is he point where my question started. I initially did what you are
> saying and Access threw up a parameter dialog asking for the value of
> forms!frmMain.frame1because the subform opened ahead of the main form and
> forms!frmMain.frame1 was not available to the subform's query criteria.
>
> Steve
>
>
> "John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
> news:defnb202aff@enews3.newsguy.com...[color=green]
>> Are you trying to set the subform's query string dynamically, such as in
>> the subform's onLoad event procedure?
>> If you create a saved query like:
>> SELECT foo from tblMyTable where some_field = forms!frmMain.frame1
>> then the subform should have no problem loading the correct records when
>> the form opens, becuase the form will requery the subform when it opens.
>> Maybe there is some reason you can't do this, but I didn't see it in your
>> question.
>>
>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>> news:rhGOe.765$9i4.294@newsread2.news.atl.earthlin k.net...[color=darkred]
>>> I'm stuck on something that seems should be easy and I need some help.
>>> My main form has an option group with five options. My subform chooses
>>> from different lists depending on which option is selected in the main
>>> form. I thought all I had to do for the subform was create a query and
>>> set the criteria equal to the option group value in the main form. No
>>> way, Jose! When the form/subform opens, the option group value is not
>>> available to the subform because the subfoem opens ahead of the main
>>> form. I can't have an event in the main form set the query in the
>>> subform at data entry because the form/subform needs to display existing
>>> records when the form/subform opens. How can I set this up?
>>>
>>> Thanks,
>>>
>>> Steve
>>>[/color]
>>
>>[/color]
>
>[/color]

re: Form/Subform

Don't know if it makes a difference but the query in the subform is the
rowsource for a combobox not the recordsource for the subform.

Steve


"John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
news:defq1j02e8c@enews3.newsguy.com...[color=blue]
>I do this all the time and it works fine. Just to make sure, I made a test
>case to try it out again before my original post, and it worked fine.
> Could you be spelling something wrong?
>
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:VMJOe.752$_84.189@newsread1.news.atl.earthlin k.net...[color=green]
>> Thamks for responding, John!
>>
>> This is he point where my question started. I initially did what you are
>> saying and Access threw up a parameter dialog asking for the value of
>> forms!frmMain.frame1because the subform opened ahead of the main form and
>> forms!frmMain.frame1 was not available to the subform's query criteria.
>>
>> Steve
>>
>>
>> "John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
>> news:defnb202aff@enews3.newsguy.com...[color=darkred]
>>> Are you trying to set the subform's query string dynamically, such as in
>>> the subform's onLoad event procedure?
>>> If you create a saved query like:
>>> SELECT foo from tblMyTable where some_field = forms!frmMain.frame1
>>> then the subform should have no problem loading the correct records when
>>> the form opens, becuase the form will requery the subform when it opens.
>>> Maybe there is some reason you can't do this, but I didn't see it in
>>> your question.
>>>
>>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>>> news:rhGOe.765$9i4.294@newsread2.news.atl.earthlin k.net...
>>>> I'm stuck on something that seems should be easy and I need some help.
>>>> My main form has an option group with five options. My subform chooses
>>>> from different lists depending on which option is selected in the main
>>>> form. I thought all I had to do for the subform was create a query and
>>>> set the criteria equal to the option group value in the main form. No
>>>> way, Jose! When the form/subform opens, the option group value is not
>>>> available to the subform because the subfoem opens ahead of the main
>>>> form. I can't have an event in the main form set the query in the
>>>> subform at data entry because the form/subform needs to display
>>>> existing records when the form/subform opens. How can I set this up?
>>>>
>>>> Thanks,
>>>>
>>>> Steve
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

re: Form/Subform

I just tried that out too and it works just fine for me.
There must be some detail that you're not mentioning. Would you like me to
send you the db I made to test my answer?

"PC Datasheet" <nospam@nospam.spam> wrote in message
news:NMLOe.882$FW1.23@newsread3.news.atl.earthlink .net...[color=blue]
> Don't know if it makes a difference but the query in the subform is the
> rowsource for a combobox not the recordsource for the subform.
>
> Steve
>
>
> "John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
> news:defq1j02e8c@enews3.newsguy.com...[color=green]
>>I do this all the time and it works fine. Just to make sure, I made a test
>>case to try it out again before my original post, and it worked fine.
>> Could you be spelling something wrong?
>>
>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>> news:VMJOe.752$_84.189@newsread1.news.atl.earthlin k.net...[color=darkred]
>>> Thamks for responding, John!
>>>
>>> This is he point where my question started. I initially did what you are
>>> saying and Access threw up a parameter dialog asking for the value of
>>> forms!frmMain.frame1because the subform opened ahead of the main form
>>> and forms!frmMain.frame1 was not available to the subform's query
>>> criteria.
>>>
>>> Steve
>>>
>>>
>>> "John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
>>> news:defnb202aff@enews3.newsguy.com...
>>>> Are you trying to set the subform's query string dynamically, such as
>>>> in the subform's onLoad event procedure?
>>>> If you create a saved query like:
>>>> SELECT foo from tblMyTable where some_field = forms!frmMain.frame1
>>>> then the subform should have no problem loading the correct records
>>>> when the form opens, becuase the form will requery the subform when it
>>>> opens.
>>>> Maybe there is some reason you can't do this, but I didn't see it in
>>>> your question.
>>>>
>>>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>>>> news:rhGOe.765$9i4.294@newsread2.news.atl.earthlin k.net...
>>>>> I'm stuck on something that seems should be easy and I need some help.
>>>>> My main form has an option group with five options. My subform chooses
>>>>> from different lists depending on which option is selected in the main
>>>>> form. I thought all I had to do for the subform was create a query and
>>>>> set the criteria equal to the option group value in the main form. No
>>>>> way, Jose! When the form/subform opens, the option group value is not
>>>>> available to the subform because the subfoem opens ahead of the main
>>>>> form. I can't have an event in the main form set the query in the
>>>>> subform at data entry because the form/subform needs to display
>>>>> existing records when the form/subform opens. How can I set this up?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Steve
>>>>>
>>>>
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

re: Form/Subform

Thanks, John, I would appreciate very much to be able to look at your test
DB. You can send it to resource@pcdatasheet.com.


"John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
news:deg2fs02opp@enews1.newsguy.com...[color=blue]
>I just tried that out too and it works just fine for me.
> There must be some detail that you're not mentioning. Would you like me to
> send you the db I made to test my answer?
>
> "PC Datasheet" <nospam@nospam.spam> wrote in message
> news:NMLOe.882$FW1.23@newsread3.news.atl.earthlink .net...[color=green]
>> Don't know if it makes a difference but the query in the subform is the
>> rowsource for a combobox not the recordsource for the subform.
>>
>> Steve
>>
>>
>> "John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
>> news:defq1j02e8c@enews3.newsguy.com...[color=darkred]
>>>I do this all the time and it works fine. Just to make sure, I made a
>>>test case to try it out again before my original post, and it worked
>>>fine.
>>> Could you be spelling something wrong?
>>>
>>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>>> news:VMJOe.752$_84.189@newsread1.news.atl.earthlin k.net...
>>>> Thamks for responding, John!
>>>>
>>>> This is he point where my question started. I initially did what you
>>>> are saying and Access threw up a parameter dialog asking for the value
>>>> of forms!frmMain.frame1because the subform opened ahead of the main
>>>> form and forms!frmMain.frame1 was not available to the subform's query
>>>> criteria.
>>>>
>>>> Steve
>>>>
>>>>
>>>> "John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in
>>>> message news:defnb202aff@enews3.newsguy.com...
>>>>> Are you trying to set the subform's query string dynamically, such as
>>>>> in the subform's onLoad event procedure?
>>>>> If you create a saved query like:
>>>>> SELECT foo from tblMyTable where some_field = forms!frmMain.frame1
>>>>> then the subform should have no problem loading the correct records
>>>>> when the form opens, becuase the form will requery the subform when it
>>>>> opens.
>>>>> Maybe there is some reason you can't do this, but I didn't see it in
>>>>> your question.
>>>>>
>>>>> "PC Datasheet" <nospam@nospam.spam> wrote in message
>>>>> news:rhGOe.765$9i4.294@newsread2.news.atl.earthlin k.net...
>>>>>> I'm stuck on something that seems should be easy and I need some
>>>>>> help. My main form has an option group with five options. My subform
>>>>>> chooses from different lists depending on which option is selected in
>>>>>> the main form. I thought all I had to do for the subform was create a
>>>>>> query and set the criteria equal to the option group value in the
>>>>>> main form. No way, Jose! When the form/subform opens, the option
>>>>>> group value is not available to the subform because the subfoem opens
>>>>>> ahead of the main form. I can't have an event in the main form set
>>>>>> the query in the subform at data entry because the form/subform needs
>>>>>> to display existing records when the form/subform opens. How can I
>>>>>> set this up?
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Steve
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]