help with form/subform sync

[Andrew L.]

I have a form based on Artists with a subform based on Performances
(1:many). The only control in the main form is a combo-box for
selecting an artist, the subform then displays performances by the
artist (forms linked by ArtistKey).

When the form opens, records from the first artist display even though
no artist is selected.

Also, and more annoying, is the fact that using the mouse wheel will
scroll through artists (in the subform) while the Artist displayed in
the combobox remains unchanged. I can fix that 1. by shifting focus
out of the parent form (don't know how to do that programatically),
but I would also like to know how to change things so the displayed
artist changes to reflect the performances being displayed in the
subform.

Artists:
ArtistKey
ArtistName

Shows:
ShowKey
ref_ArtistKey
Date
etc...

Andrew

 

[tina]

your main form should be unbound (the RecordSource property should be
blank). the combo box control should also be unbound (ControlSource property
should be blank). set the properties of the combo box control as follows:

RowSource: SELECT ArtistKey, ArtistName FROM Artists ORDER BY ArtistName;
ColumnCount: 2
BoundColumn: 1
ColumnWidth: 0"; 1"
(note: if the artist name column is too narrow or too wide, change the 1
inch setting as appropriate.)
LimitToList: Yes

within the main form design view, click once on the subform to select it. in
the Properties box, set the following properties, as

LinkChildFields: ref_ArtistKey
LinkMasterFields: NameOfComboBoxControlOnMainForm

substitute the correct name of the combo box control, of course.

hth

 

[Andrew L.]

I tried exactly as you wrote with no luck. The combo on main form
worked, but the subform remained empty. I tried changing the Source
Object property on subform to "Table.Shows" and then everything works
perfectly! I think this translates to: the main form is unbound but
the subform must be bound.

(As is, records were not sorted (since the source table for subform
sorts by key), so I changed the Source property for subform to a query
based on Shows, and that's even better!)

Thank you so much!
Andrew

 

[Al Camp]

Andrew,
Not sure if you'll check back, but...
If the raw table works, so should a query (sorted as you want). So I'm a bit leery
about
the query you may have originally had against the subform.

Is "Table.Shows" the actual name of your table. If so, you should consider dropping
the '.' from the name.
Access uses "!" and "." characters to indicate heirarchy in objects, so periods in names
could be pretty confusing.
Something like more conventional like "tblShows" would avoid that...

Glad you're all set...

 

[tina]

when you add a subform control to a form, and click on the droplist in the
SourceObject property, you get a list of all forms, tables, and Select
queries in the database; the table objects are listed with the "Table.name"
syntax and the queries are listed with the "Query.name" syntax. you can bind
the subform control directly to a table or query, without building another
form to display the data (though i never do it, and wouldn't recommend it).

hth

 

[tina]

I think this translates to: the main form is unbound but

the subform must be bound.

correct. the subform control has to be "bound" (SourceObject property set)
to a table, query, or another form based on a table or query. otherwise the
LinkChildFields and LinkMasterFields properties are useless, because there
is nothing to link to.

personally, i wouldn't link a subform control directly to a table or query.
that is basically the same thing as opening a table or query datasheet
directly for data entry and/or review - never a good idea, because you can't
control what the user does in a table/query datasheet the way you can in a
form. recommend you build a form bound to the query that's based on your
"Shows" table, then bind the subform control in the main form to that second
form. this is the standard practice for a mainform/subform setup.

hth

 

[Andrew L.]

I'd like to get this straight here. I follow you about the subform not
being based directly on a query or table...that makes sense.
Unfortunately, I am such a rank beginner that I have no idea how to
take advantage of the error checking and validation a form can offer
for data entry. So I'll begin by making sure I follow your hierarchy
here....
--TABLES--
=Artists = =Shows=
artistkey ref_artistkey
artistname showdate
showyear
(many more...)

--Queries--
=qryShows=
ref_artistkey
artistname (from Artists)
(lots more from Shows)

Given this little strucure can you outline a hierarchy of forms to be
used for adding records. Alternately, if you can point me to sample db
with such a structure... I tried Northwind but the sample is too basic
and there is no form really designed there for data entry. I know I'm
asking pretty basic stuff here and I sure appreciate the help.
Recommendation on a really good book would be gratefully accepted,
too.

Andrew

 

[tina]

the relationship your diagram displays is one to many: one artist (parent
table) may have many shows (child table), but each show belongs to only one
artist. if that is correct, then the standard form setup to handle a
two-table parent/child relationship is as follows:

create a form bound to the Artists table, i'll call it frmArtists. create
another form bound to the Shows table, i'll call it sfrmShows; if you want
to impose a particular order on the shows records, then build a query *that
is based on the Shows table ONLY*, and bind sfrmShows to that query. in
sfrmShows design view, either do not include a control for the ref_artistkey
field, or set the control's Visible property to No. in the design view of
frmArtists, add a subform control and name it ChildShows. set the control's
properties as follows:

SourceObject: sfrmShows
LinkChildFields: ref_artistkey
LinkMasterFields: artistkey

you can use this form/subform to add new artists, with or without shows
records, and also to edit/delete existing artist records and/or
add/edit/delete shows records for existing artist records.

hth

 

[Andrew L.]

OK, I did this and the only problem I run into is this: when adding a
record, the ArtistKey field on the new (blank) record is not being
populated. I am using a combobox in the mainform displaying
ArtistName, bound to ArtistKey. The subform, based on qryShows (a
sorted Shows table), is linked to the mainform with
ArtistKey:ref_ArtistKey, as you suggested.

I don't know if that combobox is the right type of control to use, as
the dropdown works, but auto-complete does not. Should I change the
Shows table so that the ref_ArtistKey is a lookup to ArtistName in
Artists bound to ArtistKey? Years ago, one of the MVP's recommended
never using lookups in tables because he likes to see the data
actually in the table, so I haven't done that since...

 

[tina]

NO, don't use a lookup field in your table, under any circumstances. in this
instance, it won't do a thing to help you anyway.

i think we're having a communication problem here. your initial post said
that your main form contains only a combo box control that shows a droplist
of Artists, and you want to select a specific artist and see all his/her
shows in the subform. i told you how to do that. your last post asked me to
outline a hierarchy of forms to be used for adding records. i assumed that
you meant adding records to both tables, and so i told you how to set up a
form/subform to do that; that solution does not include using a combo box
control on the main form. now you're asking about combo box control issues
again, which i assume means you're reverting to the setup you described in
your initial post.

at any rate, i've explained to you how to create both setups, so all i can
suggest at this point is that you go back and read this thread in the order
posted, and choose the solution you actually want to use. once you decide
what you want, then implement that solution *by itself* without "borrowing"
instructions from the other solution. if you have problems implementing,
post back with specific questions.

hth

 

[Andrew L.]

I went back and sure enough you are right. The "second" solution
(including data entry and edits, bound form & sform) works well with
bound text boxes in the form and NO ref_artistkey in the sform. I do
not quite understand why the sform cannot have that field visible;
this seems counter-intuitive to me, but it works.

The first solution, with the combo-box, for reviewing by artist, is
also working.

One last question. What type of control can I use to allow
auto-complete to function in selecting (ex. an artist)? It might be
nice to use this (rather than the dropdown) in the first solution.

 

[tina]

well, the combo box control does have an AutoExpand property, which normally
defaults to Yes when the control is created. if you're dissatisfied with
using a combo box, you might test a listbox control and see if it's more to
your liking.

as for the foreign key field in the subform (solution #2), you can bind it
to a visible control - but what's the point? the value is entered
automatically as soon as the user begins entering data in a new subform
record; and you don't want the user to change the value, because that's what
links the subform record to the current mainform record, so the control it's
bound to should be protected if visible; and most often the primary/foreign
key value is numeric and will mean nothing to the user; and finally, since
it's automatically entered, and should be protected from user change, and is
usually a value that means nothing to the user, displaying it is a waste of
valuable screen real estate. even if the primary/foreign key is meaningful
to the user, such as an account number, consider the scenario: on the main
form, (in either of the two solutions outlined in this thread) the account
number (which is also the record's primary key) for the current record is
displayed. on the subform, every record also displays that same account
number. especially in a ContinuousForms or Datasheet view, such repetition
looks pretty silly, and that portion of the subform display will be
essentially ignored by the user once s/he realizes that all the account
numbers in the displayed subform records *always* match the account number
on the mainform.

hth

I went back and sure enough you are right. The "second" solution
(including data entry and edits, bound form & sform) works well with
bound text boxes in the form and NO ref_artistkey in the sform. I do
not quite understand why the sform cannot have that field visible;
this seems counter-intuitive to me, but it works.

The first solution, with the combo-box, for reviewing by artist, is
also working.

One last question. What type of control can I use to allow
auto-complete to function in selecting (ex. an artist)? It might be
nice to use this (rather than the dropdown) in the first solution.

NO, don't use a lookup field in your table, under any circumstances. in this
instance, it won't do a thing to help you anyway.

i think we're having a communication problem here. your initial post said
that your main form contains only a combo box control that shows a droplist
of Artists, and you want to select a specific artist and see all his/her
shows in the subform. i told you how to do that. your last post asked me to
outline a hierarchy of forms to be used for adding records. i assumed that
you meant adding records to both tables, and so i told you how to set up a
form/subform to do that; that solution does not include using a combo box
control on the main form. now you're asking about combo box control issues
again, which i assume means you're reverting to the setup you described in
your initial post.

at any rate, i've explained to you how to create both setups, so all i can
suggest at this point is that you go back and read this thread in the order
posted, and choose the solution you actually want to use. once you decide
what you want, then implement that solution *by itself* without "borrowing"
instructions from the other solution. if you have problems implementing,
post back with specific questions.

hth

OK, I did this and the only problem I run into is this: when adding a
record, the ArtistKey field on the new (blank) record is not being
populated. I am using a combobox in the mainform displaying
ArtistName, bound to ArtistKey. The subform, based on qryShows (a
sorted Shows table), is linked to the mainform with
ArtistKey:ref_ArtistKey, as you suggested.

I don't know if that combobox is the right type of control to use, as
the dropdown works, but auto-complete does not. Should I change the
Shows table so that the ref_ArtistKey is a lookup to ArtistName in
Artists bound to ArtistKey? Years ago, one of the MVP's recommended
never using lookups in tables because he likes to see the data
actually in the table, so I haven't done that since...


the relationship your diagram displays is one to many: one artist (parent
table) may have many shows (child table), but each show belongs to

only
one

artist. if that is correct, then the standard form setup to handle a
two-table parent/child relationship is as follows:

create a form bound to the Artists table, i'll call it frmArtists. create
another form bound to the Shows table, i'll call it sfrmShows; if you want
to impose a particular order on the shows records, then build a query *that
is based on the Shows table ONLY*, and bind sfrmShows to that query. in
sfrmShows design view, either do not include a control for the ref_artistkey
field, or set the control's Visible property to No. in the design view of
frmArtists, add a subform control and name it ChildShows. set the control's
properties as follows:

SourceObject: sfrmShows
LinkChildFields: ref_artistkey
LinkMasterFields: artistkey

you can use this form/subform to add new artists, with or without shows
records, and also to edit/delete existing artist records and/or
add/edit/delete shows records for existing artist records.

hth


I'd like to get this straight here. I follow you about the subform not
being based directly on a query or table...that makes sense.
Unfortunately, I am such a rank beginner that I have no idea how to
take advantage of the error checking and validation a form can offer
for data entry. So I'll begin by making sure I follow your hierarchy
here....
--TABLES--
=Artists = =Shows=
artistkey ref_artistkey
artistname showdate
showyear
(many more...)

--Queries--
=qryShows=
ref_artistkey
artistname (from Artists)
(lots more from Shows)

Given this little strucure can you outline a hierarchy of forms to be
used for adding records. Alternately, if you can point me to sample db
with such a structure... I tried Northwind but the sample is too basic
and there is no form really designed there for data entry. I know I'm
asking pretty basic stuff here and I sure appreciate the help.
Recommendation on a really good book would be gratefully accepted,
too.

Andrew




I think this translates to: the main form is unbound but
the subform must be bound.

correct. the subform control has to be "bound" (SourceObject property
set)
to a table, query, or another form based on a table or query. otherwise
the
LinkChildFields and LinkMasterFields properties are useless, because
there
is nothing to link to.

personally, i wouldn't link a subform control directly to a table or
query.
that is basically the same thing as opening a table or query datasheet
directly for data entry and/or review - never a good idea, because you
can't
control what the user does in a table/query datasheet the way you

can
in

a
form. recommend you build a form bound to the query that's based on your
"Shows" table, then bind the subform control in the main form to that
second
form. this is the standard practice for a mainform/subform setup.

hth


I tried exactly as you wrote with no luck. The combo on main form
worked, but the subform remained empty. I tried changing the Source
Object property on subform to "Table.Shows" and then everything works
perfectly! I think this translates to: the main form is unbound but
the subform must be bound.

(As is, records were not sorted (since the source table for subform
sorts by key), so I changed the Source property for subform to a query
based on Shows, and that's even better!)

Thank you so much!
Andrew