Combo Box/Subform

[Brian Keanie]

This should be simple but have spent several hours trying to solve and
decided to ask (as usual)

Created a form with just a combo box on it. The combo box obtains its list
from a table. Would like to add a subform (one in the combo to many in the
sub) but can't seem to get the relationship correct. The main form is based
on a table and the subform is based on a query with the the two fields from
the two tables have an "indeterminate" relationship.

Please no hints .... Please be explicit.
Thanks always to those who reply.

Best regards

 

[Arvin Meyer [MVP]]

The data in the combo box has to have its bound column in common with a
field in the query which is the recordsource of the subform. That does not
mean it must be bound to a field in the main form, merely that it has a
column (usually the first one) bound to a field that is used in the
subform's recordsource. In the criteria space of the subform's query, add
the reference to the combo, like:

[Forms]![YourFormName]![YourComboName]

Now, in the AfterUpdate event of the combo, add this line of code:

Me!NameOfSubform.Form.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Brian Keanie]

Thanks to Arvin for the suggestion.
I did add the reference to the [Forms]! ..... etc to the criteria space
under the common field but when I change from design to output view I get a
wee box coming up asking "Enter Parameter Value"
Also when I enter Me!SfrmGroupList.Form.Requery I get the message "Microsoft
Access can't find the macro 'Me!SfrmGroupList'

Having said that ...... I do have the combo/subform functioning to a point
...... that being after selecting a line from the combo box the material
shows up in the subform (Just like it should) However if I select a second
one nothing shows up. Maybe if we could solve the problems from above we
could lick this. Any further thoughts would be wonderful

Best regards

The data in the combo box has to have its bound column in common with a
field in the query which is the recordsource of the subform. That does not
mean it must be bound to a field in the main form, merely that it has a
column (usually the first one) bound to a field that is used in the
subform's recordsource. In the criteria space of the subform's query, add
the reference to the combo, like:

[Forms]![YourFormName]![YourComboName]

Now, in the AfterUpdate event of the combo, add this line of code:

Me!NameOfSubform.Form.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

This should be simple but have spent several hours trying to solve and
decided to ask (as usual)

Created a form with just a combo box on it. The combo box obtains its list
from a table. Would like to add a subform (one in the combo to many in the
sub) but can't seem to get the relationship correct. The main form is based
on a table and the subform is based on a query with the the two fields from
the two tables have an "indeterminate" relationship.

Please no hints .... Please be explicit.
Thanks always to those who reply.

Best regards

 

[tina]

I did add the reference to the [Forms]! ..... etc to the criteria space

under the common field but when I change from design to output view I get a
wee box coming up asking "Enter Parameter Value"

when you use a form reference as criteria in a query, the referenced form
must be open when you switch from query design view to query datasheet view,
otherwise you will get the Enter Parameter Value dialog box. it's not an
error, and not a problem - just test the form reference via the form/subform
interface, *not* by opening the query directly.

Also when I enter Me!SfrmGroupList.Form.Requery I get the message "Microsoft
Access can't find the macro 'Me!SfrmGroupList'

sounds like you entered the requery command directly on the combobox's
AfterUpdate property event line. that's a common mistake of folks who aren't
familiar with VBA. do this instead:

in form design view, click on the combobox to select it. in the Properties
box, delete the text from the AfterUpdate event line. when your cursor is in
that line, look to the right and you'll see a Build button (...). click on
the button, and from the dialog box select the Code Builder option and click
OK. the VBE window will open, with your cursor already in the correct
position inside a new AfterUpdate event procedure. type in the requery
command at that position, as

Me!SfrmGroupList.Form.Requery

hth

Thanks to Arvin for the suggestion.
I did add the reference to the [Forms]! ..... etc to the criteria space
under the common field but when I change from design to output view I get a
wee box coming up asking "Enter Parameter Value"
Also when I enter Me!SfrmGroupList.Form.Requery I get the message "Microsoft
Access can't find the macro 'Me!SfrmGroupList'

Having said that ...... I do have the combo/subform functioning to a point
..... that being after selecting a line from the combo box the material
shows up in the subform (Just like it should) However if I select a second
one nothing shows up. Maybe if we could solve the problems from above we
could lick this. Any further thoughts would be wonderful

Best regards

The data in the combo box has to have its bound column in common with a
field in the query which is the recordsource of the subform. That does not
mean it must be bound to a field in the main form, merely that it has a
column (usually the first one) bound to a field that is used in the
subform's recordsource. In the criteria space of the subform's query, add
the reference to the combo, like:

[Forms]![YourFormName]![YourComboName]

Now, in the AfterUpdate event of the combo, add this line of code:

Me!NameOfSubform.Form.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

This should be simple but have spent several hours trying to solve and
decided to ask (as usual)

Created a form with just a combo box on it. The combo box obtains its list
from a table. Would like to add a subform (one in the combo to many in the
sub) but can't seem to get the relationship correct. The main form is based
on a table and the subform is based on a query with the the two fields from
the two tables have an "indeterminate" relationship.

Please no hints .... Please be explicit.
Thanks always to those who reply.

Best regards

 

[Brian Keanie]

Tina ... Thanks for the guidance. We are making progress however let me just
go throo this one more time.
The combo box is on "frmMain". The combobox is populated by a query which
gets its list from a table called "tblMaterialMaster". The subform is called
"SfrmGroupList".
Having made the changes that you suggested the combo box worked well with
the exception of the first item in the list. If I closed the form with the
first item showing in the combo and then reopened it the items within that
category did show in the subform. I could almost live with that, however, if
I added a new category(s) in the underlying table the combo box did show
them but they wouldn't show anything in the subform either. Any further
ideas?

Best regards

I did add the reference to the [Forms]! ..... etc to the criteria space
under the common field but when I change from design to output view I

get

a
wee box coming up asking "Enter Parameter Value"

when you use a form reference as criteria in a query, the referenced form
must be open when you switch from query design view to query datasheet view,
otherwise you will get the Enter Parameter Value dialog box. it's not an
error, and not a problem - just test the form reference via the form/subform
interface, *not* by opening the query directly.

Also when I enter Me!SfrmGroupList.Form.Requery I get the message "Microsoft
Access can't find the macro 'Me!SfrmGroupList'

sounds like you entered the requery command directly on the combobox's
AfterUpdate property event line. that's a common mistake of folks who aren't
familiar with VBA. do this instead:

in form design view, click on the combobox to select it. in the Properties
box, delete the text from the AfterUpdate event line. when your cursor is in
that line, look to the right and you'll see a Build button (...). click on
the button, and from the dialog box select the Code Builder option and click
OK. the VBE window will open, with your cursor already in the correct
position inside a new AfterUpdate event procedure. type in the requery
command at that position, as

Me!SfrmGroupList.Form.Requery

hth

Thanks to Arvin for the suggestion.
I did add the reference to the [Forms]! ..... etc to the criteria space
under the common field but when I change from design to output view I

get

a
wee box coming up asking "Enter Parameter Value"
Also when I enter Me!SfrmGroupList.Form.Requery I get the message "Microsoft
Access can't find the macro 'Me!SfrmGroupList'

Having said that ...... I do have the combo/subform functioning to a point
..... that being after selecting a line from the combo box the material
shows up in the subform (Just like it should) However if I select a second
one nothing shows up. Maybe if we could solve the problems from above we
could lick this. Any further thoughts would be wonderful

Best regards

The data in the combo box has to have its bound column in common with a
field in the query which is the recordsource of the subform. That does not
mean it must be bound to a field in the main form, merely that it has a
column (usually the first one) bound to a field that is used in the
subform's recordsource. In the criteria space of the subform's query, add
the reference to the combo, like:

[Forms]![YourFormName]![YourComboName]

Now, in the AfterUpdate event of the combo, add this line of code:

Me!NameOfSubform.Form.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

This should be simple but have spent several hours trying to solve and
decided to ask (as usual)

Created a form with just a combo box on it. The combo box obtains

its
list

from a table. Would like to add a subform (one in the combo to many

in
the

sub) but can't seem to get the relationship correct. The main form is
based
on a table and the subform is based on a query with the the two fields
from
the two tables have an "indeterminate" relationship.

Please no hints .... Please be explicit.
Thanks always to those who reply.

Best regards

 

[tina]

okay, let's back up a bit. in an earlier post to this thread, you said

The main form is based
on a table and the subform is based on a query with the the two fields from
the two tables have an "indeterminate" relationship.

if you have an "indeterminate" relationship between two tables in your
Relationships window, then the relationship between those two tables is not
set up correctly.

what's the name of the table underlying the main form? what tables are
included in the query that underlies the subform? how is the mainform's
table related to the table(s) in the subform's query? in the subform
*control* on the main form, what fields are listed in the LinkedChildFields
and LinkedMasterFields in the Properties box? is the combo box on the main
form bound to a field in the underlying table? if so, what field? i'm trying
to get a clear picture of your setup, and what you're trying to accomplish.

hth

Tina ... Thanks for the guidance. We are making progress however let me just
go throo this one more time.
The combo box is on "frmMain". The combobox is populated by a query which
gets its list from a table called "tblMaterialMaster". The subform is called
"SfrmGroupList".
Having made the changes that you suggested the combo box worked well with
the exception of the first item in the list. If I closed the form with the
first item showing in the combo and then reopened it the items within that
category did show in the subform. I could almost live with that, however, if
I added a new category(s) in the underlying table the combo box did show
them but they wouldn't show anything in the subform either. Any further
ideas?

Best regards

I did add the reference to the [Forms]! ..... etc to the criteria space
under the common field but when I change from design to output view I

get

a
wee box coming up asking "Enter Parameter Value"

when you use a form reference as criteria in a query, the referenced form
must be open when you switch from query design view to query datasheet view,
otherwise you will get the Enter Parameter Value dialog box. it's not an
error, and not a problem - just test the form reference via the form/subform
interface, *not* by opening the query directly.

Also when I enter Me!SfrmGroupList.Form.Requery I get the message "Microsoft
Access can't find the macro 'Me!SfrmGroupList'

sounds like you entered the requery command directly on the combobox's
AfterUpdate property event line. that's a common mistake of folks who aren't
familiar with VBA. do this instead:

in form design view, click on the combobox to select it. in the Properties
box, delete the text from the AfterUpdate event line. when your cursor

is

in
that line, look to the right and you'll see a Build button (...). click on
the button, and from the dialog box select the Code Builder option and click
OK. the VBE window will open, with your cursor already in the correct
position inside a new AfterUpdate event procedure. type in the requery
command at that position, as

Me!SfrmGroupList.Form.Requery

hth

Thanks to Arvin for the suggestion.
I did add the reference to the [Forms]! ..... etc to the criteria space
under the common field but when I change from design to output view I

get

a
wee box coming up asking "Enter Parameter Value"
Also when I enter Me!SfrmGroupList.Form.Requery I get the message "Microsoft
Access can't find the macro 'Me!SfrmGroupList'

Having said that ...... I do have the combo/subform functioning to a point
..... that being after selecting a line from the combo box the material
shows up in the subform (Just like it should) However if I select a second
one nothing shows up. Maybe if we could solve the problems from above we
could lick this. Any further thoughts would be wonderful

Best regards

The data in the combo box has to have its bound column in common

with

a does
not

has

a

column (usually the first one) bound to a field that is used in the
subform's recordsource. In the criteria space of the subform's

query,
add

the reference to the combo, like:

[Forms]![YourFormName]![YourComboName]

Now, in the AfterUpdate event of the combo, add this line of code:

Me!NameOfSubform.Form.Requery
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

This should be simple but have spent several hours trying to solve and
decided to ask (as usual)

Created a form with just a combo box on it. The combo box obtains its
list
from a table. Would like to add a subform (one in the combo to

many

in