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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple listboxes to select set of data
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 |
#2
|
|||
|
|||
Multiple listboxes to select set of data
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.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
#3
|
|||
|
|||
Multiple listboxes to select set of data
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? |
Thread Tools | |
Display Modes | |
|
|