Multiple listboxes to select set of data

[Thore]

Hello all,

I know that this sort of question has been asked several
times, but the answers were no help to me in the end
(either too sophisticated or not really applicable to my
problem...)

Version: Access 2000
Level of Expertise: Beginner...

I got 2 tables (Company & Contact) in a one-to-many
relationship with the following fields:

Company:
CompanyID
CompanyName

Contact:
ContactID
ContactName
CompanyID (<-- obviously the link to the Company)
ContactPhone
ContactEmail
etc....

For displaying the specific Contact record in a form I
would like to use two list boxes. The first showing all
company names. Once clicked on on the list, the second
list box should only contain the ContactNames for the
specific company. Clicking on that, the contact record
should be displayed on the form.
I assume I will have to work with one form and subform,
and I got it working in that way that the subform only
contains contact record for the company when scrolling
through them. However, the litsbox in the subform stills
shows all contact records. I have no clue how to set the
properties so that it works.
So, if you could provide me with instructions, I would be
really greatfull.

Thanks and brgds,
Thore

 

[Albert D. Kallal]

Here is my suggestion.

Create a nice looking continues form, and NOT a list box for the companies.

frmCompanyList

Now, create another nice looking continued form for the contacts. Place a
button on this continues form. This button when pressed will launch the
contact form. The code behind this button will be:

docmd.OpenForm "frmContacts",,,"contactID = " & me!ContactID

You can test the above button. The way the button will look is much like
what you see in the following screen shots:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

Ok, now, lets put it all together!

Now, create a nice big BLANK form. Turn off all the navigation buttons,
selection buttons etc. Turn most that junk off, since this is just a nice
big blank form.

Drop in your first company continues form. (you can use drag and drop in
design mode to do this). Before you drop in this form, in this
frmCompanyList, you need to place the following line of code in the forms
on-current event:

me.Parent.frmListContacts.Requery

Ok..we dropped in this nice form on the left side. Lets drop in our
frmListContacts on the right side.

Now, for the 2nd sub-form (frmListContats),we set the following settings on
this large main form (select the sub-form) we set:

linkChildFields CompanyID (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)

LinkMasterFields [frmCompanyList].[form].[CompanyID]

That is it....

I count two lines of code to solve the whole thing....

 

[Guest]

Hi Albert,

first of all, thanks for the help. However, the following
obstacles are between me and success:

frmCompanyList, you need to place the following line of code in the forms
on-current event:

me.Parent.frmListContacts.Requery

I went into "Properties" --> "Event" --> "On Current" and
copied the code into the field. I then got the error
message that "MS Access cannot find the macro 'me.'"

Nevertheless I continued...

this large main form (select the sub-form) we set:

linkChildFields CompanyID (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)

LinkMasterFields [frmCompanyList].[form].[CompanyID]

I get the message that I cannot build a link between
unbound forms.

So, what to do now?