Simple newbie(ish) question

[Aaron Howea]

I have a quick question about Access forms which I'm
hoping someone can help me with! I have a set of tables,
one for agencies and another for clients. Each client in
their table has an agency name against them, and the
agents in the agency table are set as a primary key, with
a relationship.

I would like to create a form where the user can select an
agency from a combobox, and the list of clients linked to
that agency would populate a listbox next to it. I am
told that I need to bind the controls of the form, but I
can't seem to find any pointers on how to do this. Any
help on where to start???

 

[Dirk Goldgar]

I have a quick question about Access forms which I'm
hoping someone can help me with! I have a set of tables,
one for agencies and another for clients. Each client in
their table has an agency name against them, and the
agents in the agency table are set as a primary key, with
a relationship.

I would like to create a form where the user can select an
agency from a combobox, and the list of clients linked to
that agency would populate a listbox next to it. I am
told that I need to bind the controls of the form, but I
can't seem to find any pointers on how to do this. Any
help on where to start???

You can do exactly what you describe, but it requires a smidgen of code
to make the listbox reflect what is chosen in the combo box. Or you can
use a subform to display the related clients, instead of a list box. If
you do that, no code at all is needed, because you can use set the Link
Master/Child Fields properties of the subform control so that the
subform always shows the clients related to the agency chosen in the
combo box. The only real advantage I can see to doing it the list box
way is if the user is to choose multiple clients from the list for some
sort of further processing. Then you might well want a list box,
because you can set a list box for multiselect.

I can tell you how to set up either of these approaches, if you'll tell
me which you prefer.

 

[Guest]

-----Original Message-----


You can do exactly what you describe, but it requires a smidgen of code
to make the listbox reflect what is chosen in the combo box. Or you can
use a subform to display the related clients, instead of a list box. If
you do that, no code at all is needed, because you can use set the Link
Master/Child Fields properties of the subform control so that the
subform always shows the clients related to the agency chosen in the
combo box. The only real advantage I can see to doing it the list box
way is if the user is to choose multiple clients from the list for some
sort of further processing. Then you might well want a list box,
because you can set a list box for multiselect.

I can tell you how to set up either of these approaches, if you'll tell
me which you prefer.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks for your reply Dirk, I think the subform may well
be useful then. I did try that approach too but I
couldn't seem to get the thing working. I'm not totally
new to Access but haven't used it in a long time and this
has completely thrown me!

 

[Dirk Goldgar]

Thanks for your reply Dirk, I think the subform may well
be useful then. I did try that approach too but I
couldn't seem to get the thing working. I'm not totally
new to Access but haven't used it in a long time and this
has completely thrown me!

The subform approach definitely works, at least for doing what you've
described so far. I suggest you try it again, and come back here for
help with any specific problems you run into. Bear in mind that (IIRC)
the subform wizard won't let you name an unbound control as a Link
Master Field for the subform, but that doesn't mean you can't do it --
you just have to forget the wizard and set the linking fields yourself
manually on the property sheet of the subform control.

 

[Aaron Howe]

I'm still having some issues making it work, it tells me I
can't link unbound forms and so I'm a bit lost...!

 

[Dirk Goldgar]

I'm still having some issues making it work, it tells me I
can't link unbound forms and so I'm a bit lost...!

I'm not sure how you're going about it, that you're getting that
message. Try it like this:

1. Create the form that will be the main form. This form will be
unbound, and will have only one control on it of any significance: the
combo box for selecting an agency. Call this combo box "cboAgency".

2. Create the form that will be shown as a subform on the main form.
You create this as a wholly separate, standalone form, bound to the
table of clients, or to a query of that table that includes only the
fields you want to show on the subform -- but it must include the Agency
field from the Clients table. You'll want this form to be set for
either continuous forms view or datasheet view. On this form, I suggest
you set the Agency field to be invisible. Save the form with the name
"sfClients", and close the form.

3. Open the main form in design view. Drag sfClients from the database
window onto the main form, and drop it where you want the subform to be.
That will create a subform control with sfClients as its source object.

4. Open the property sheet of the subform control and go to the Data
tab. Set the Link Child Fields property to "Agency" (without the
quotes) -- or whatever the name of the agency field in that form's
recordsource is -- and set the Link Master Fields property to
"cboAgency".

That ought to do it. All that's left is to tinker with the fomatting of
the subform control and sfClients itself to make it look the way you
like.

 

[Guest]

-----Original Message-----


I'm not sure how you're going about it, that you're getting that
message. Try it like this:

1. Create the form that will be the main form. This form will be
unbound, and will have only one control on it of any significance: the
combo box for selecting an agency. Call this combo box "cboAgency".

2. Create the form that will be shown as a subform on the main form.
You create this as a wholly separate, standalone form, bound to the
table of clients, or to a query of that table that includes only the
fields you want to show on the subform -- but it must include the Agency
field from the Clients table. You'll want this form to be set for
either continuous forms view or datasheet view. On this form, I suggest
you set the Agency field to be invisible. Save the form with the name
"sfClients", and close the form.

3. Open the main form in design view. Drag sfClients from the database
window onto the main form, and drop it where you want the subform to be.
That will create a subform control with sfClients as its source object.

4. Open the property sheet of the subform control and go to the Data
tab. Set the Link Child Fields property to "Agency" (without the
quotes) -- or whatever the name of the agency field in that form's
recordsource is -- and set the Link Master Fields property to
"cboAgency".

That ought to do it. All that's left is to tinker with the fomatting of
the subform control and sfClients itself to make it look the way you
like.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Hi Dirk,

I tried that out this morning and unfortunately it didn't
work out. It does show the fields from the query that I
want, but it doesn't pay any attention to the selection I
make from cboAgency - instead it just gives me every
record, regardless of my selection. I think I may just
have to give up on this one.

Thanks anyway,
Aaron

 

[mal]

I've used a wizard frequently to do this creating the main form and
then adding a subform to the main form when it asks for the
relationship. Design the subform and link it to the tabel you require
so that it retrieves ALL records. Then when you drop it in the main
form the wizard will ask what the relationship is between the two.
yopu can of course do it without the wizard and it is just! a matter
of setting the child parent relationship correctly.

 

[mal]

The wizard should do it OK.
Set up a form for the child records so that it is bound to a table and
selects ALL records.
Set up the parent form where you will select the specific record.
Drop the child in the main and you should get a message asking what
the relationship is.
You can set the child/parent up without the wizard at any time if it
is wrong.