By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Form/Subform

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
PC Datasheet wrote:
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?


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
Nov 13 '05 #2

P: n/a
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" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:de**********@coranto.ucs.mun.ca...
PC Datasheet wrote:
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?


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

Nov 13 '05 #3

P: n/a
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" <no****@nospam.spam> schreef in bericht news:UY***************@newsread3.news.atl.earthlin k.net...
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" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:de**********@coranto.ucs.mun.ca...
PC Datasheet wrote:
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?


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


Nov 13 '05 #4

P: n/a
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" <no****@nospam.spam> wrote in message
news:rh***************@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

Nov 13 '05 #5

P: n/a
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:de*********@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" <no****@nospam.spam> wrote in message
news:rh***************@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


Nov 13 '05 #6

P: n/a
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" <no****@nospam.spam> wrote in message
news:VM***************@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:de*********@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" <no****@nospam.spam> wrote in message
news:rh***************@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



Nov 13 '05 #7

P: n/a
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:de*********@enews3.newsguy.com...
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" <no****@nospam.spam> wrote in message
news:VM***************@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:de*********@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" <no****@nospam.spam> wrote in message
news:rh***************@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



Nov 13 '05 #8

P: n/a
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" <no****@nospam.spam> wrote in message
news:NM**************@newsread3.news.atl.earthlink .net...
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:de*********@enews3.newsguy.com...
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" <no****@nospam.spam> wrote in message
news:VM***************@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:de*********@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" <no****@nospam.spam> wrote in message
news:rh***************@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
>



Nov 13 '05 #9

P: n/a
Thanks, John, I would appreciate very much to be able to look at your test
DB. You can send it to re******@pcdatasheet.com.
"John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> wrote in message
news:de*********@enews1.newsguy.com...
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" <no****@nospam.spam> wrote in message
news:NM**************@newsread3.news.atl.earthlink .net...
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:de*********@enews3.newsguy.com...
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" <no****@nospam.spam> wrote in message
news:VM***************@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:de*********@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" <no****@nospam.spam> wrote in message
> news:rh***************@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
>>
>
>



Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.