A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Simple newbie(ish) question



 
 
Thread Tools Display Modes
  #1  
Old July 13th, 2004, 04:59 PM
Aaron Howea
external usenet poster
 
Posts: n/a
Default Simple newbie(ish) question

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???
  #2  
Old July 13th, 2004, 05:20 PM
Dirk Goldgar
external usenet poster
 
Posts: n/a
Default Simple newbie(ish) question

"Aaron Howea" wrote in message

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.

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

(please reply to the newsgroup)


  #3  
Old July 14th, 2004, 08:58 AM
external usenet poster
 
Posts: n/a
Default Simple newbie(ish) question


-----Original Message-----
"Aaron Howea" wrote in

message

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.

--
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!
  #4  
Old July 14th, 2004, 05:35 PM
Dirk Goldgar
external usenet poster
 
Posts: n/a
Default Simple newbie(ish) question

wrote in message


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.

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

(please reply to the newsgroup)


  #5  
Old July 15th, 2004, 08:39 AM
Aaron Howe
external usenet poster
 
Posts: n/a
Default Simple newbie(ish) question

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...!

-----Original Message-----
wrote in message


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.

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

(please reply to the newsgroup)


.

  #6  
Old July 16th, 2004, 02:57 AM
Dirk Goldgar
external usenet poster
 
Posts: n/a
Default Simple newbie(ish) question

"Aaron Howe" wrote in message

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.

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

(please reply to the newsgroup)


  #7  
Old July 19th, 2004, 08:33 AM
external usenet poster
 
Posts: n/a
Default Simple newbie(ish) question


-----Original Message-----
"Aaron Howe" wrote

in message

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.

--
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SIMPLE BEGINNER ACCESS 2000 QUESTION Moni881 New Users 5 July 15th, 2004 09:51 PM
Simple Query Question Rebecca Running & Setting Up Queries 1 June 3rd, 2004 10:59 AM
simple question? mark New Users 1 May 7th, 2004 09:16 AM
Simple Excel Question Scott B. Hogle Worksheet Functions 3 March 15th, 2004 12:53 AM
Simple pie chart question Jim Meyer Charts and Charting 2 October 6th, 2003 05:34 PM


All times are GMT +1. The time now is 04:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.