473,587 Members | 2,487 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Nov 13 '05 #1
9 2735
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.OptionGroupV alue

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
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****@PurpleP andaChasers.Moe rtherium> 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.OptionGroupV alue

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
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.Fo rm.Recordsource = "SQL or query here"
End sub

Arno R

"PC Datasheet" <no****@nospam. spam> schreef in bericht news:UY******** *******@newsrea d3.news.atl.ear thlink.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****@PurpleP andaChasers.Moe rtherium> 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.OptionGroupV alue

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
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.f rame1
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******** *******@newsrea d2.news.atl.ear thlink.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
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.f rame1because the subform opened ahead of the main form and
forms!frmMain.f rame1 was not available to the subform's query criteria.

Steve
"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews3.newsgu y.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.f rame1
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******** *******@newsrea d2.news.atl.ear thlink.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
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******** *******@newsrea d1.news.atl.ear thlink.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.f rame1because the subform opened ahead of the main form and
forms!frmMain.f rame1 was not available to the subform's query criteria.

Steve
"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews3.newsgu y.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.f rame1
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******** *******@newsrea d2.news.atl.ear thlink.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
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@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews3.newsgu y.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******** *******@newsrea d1.news.atl.ear thlink.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.f rame1because the subform opened ahead of the main form and
forms!frmMain.f rame1 was not available to the subform's query criteria.

Steve
"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews3.newsgu y.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.f rame1
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******** *******@newsrea d2.news.atl.ear thlink.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
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******** ******@newsread 3.news.atl.eart hlink.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@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews3.newsgu y.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******** *******@newsrea d1.news.atl.ear thlink.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.f rame1because the subform opened ahead of the main form
and forms!frmMain.f rame1 was not available to the subform's query
criteria.

Steve
"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews3.newsgu y.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.f rame1
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******** *******@newsrea d2.news.atl.ear thlink.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
Thanks, John, I would appreciate very much to be able to look at your test
DB. You can send it to re******@pcdata sheet.com.
"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews1.newsgu y.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******** ******@newsread 3.news.atl.eart hlink.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@ca l+central.com (remove +'s)> wrote in message
news:de******** *@enews3.newsgu y.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******** *******@newsrea d1.news.atl.ear thlink.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.f rame1because the subform opened ahead of the main
form and forms!frmMain.f rame1 was not available to the subform's query
criteria.

Steve
"John Welch" <j+ohnw+elch@ca l+central.com (remove +'s)> wrote in
message news:de******** *@enews3.newsgu y.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.f rame1
> 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******** *******@newsrea d2.news.atl.ear thlink.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
3322
by: William Wisnieski | last post by:
Hello Everyone, Access 2000 I have a main form with a continuous subform. On the main form I have a text field called . It gets populated based on what the user selects in a field on the sub form (code in the AfterUpdate Event). Here's the issue. If the user deletes a subform record. The field on the main form shows null even if there is a value in the preceding subform record. What I'd like it to do is immediately refresh to
2
343
by: Claude | last post by:
Let' say we have an application for a production facility running 24/7 broken into 3 shifts 6-2,2-10,10-6 each production report date contains 3 shifts as above in each shift there can be from one to several different product runs (the average is about 4) but it could be more or less - it's not defined or definite for many reasons Then, for each run there is a production report of product, good pieces,
25
10197
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
4
6990
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the user enters the last qualifying field on the main form. In one case this works fine, the subform shows the data the user wants to update -- which means showing all the data put in previously (ie showing this via the requery and the continuous...
12
12919
by: swingingming | last post by:
Hi, in the NorthWind sample database, when clicking on the next navigation button on the new order record with nothing on the subform (order details), we got an order with nothing ordered. How can we prevent this from happening? Thanks. ming
14
4081
by: Anja | last post by:
Hi everyone, I have a sub form that references a query to get the results. However, what I want to do is filter the results further based on a certain criteria. How can I tell the sub form to filter results based on a certain criteria. I tried using the Filter and OrderBy fields in the designer but they seem to have no affect!
4
8822
by: Macbane | last post by:
Hi, I have a 'main' form called frmIssues which has a subform control (named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs. A control button on the main form opens a pop-up form which allows me to edit the record in the subform. What I want to happen is for subform with the new edits to be updated on the main form when I close the popup. I'm sure this is a very small bit of code in the the 'On close' event for the popup...
6
96015
NeoPa
by: NeoPa | last post by:
Introduction The first thing to understand about Sub-Forms is that, to add a form onto another form takes a special Subform control. This Subform control acts as a container for the form that you want to act as a Sub-Form of the main one. That is to say, if you wanted frmB to act as a Sub-Form of frmA, then you would create a Subform control on frmA (in this example we'll call it sfmB). Subforms have a .Form property which contains a...
11
7144
by: mrowe | last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my mind I am wonder if this is causing my problem, but I don’t want to go through the work to convert to DAO unless I know it is truly in my best interest.) I am having problems getting a requery to show up consistently on a couple of forms. I have...
0
7852
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8216
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
7974
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8221
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6629
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5395
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3845
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2364
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1455
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.