limit one list box data set based on selection in another list box

I am using Access 2k to resolve a form problem where multiple list boxes are
partially dependant on the value(s) in other list boxes.  I keep reading
that an unbound list box can be populated with data from the value of
another list box, but how to do it is not explained.

I have Vendors, Mfr, and ProductLines with list boxes for each.  A vendor
supplies the manufacturer's product lines.  What I want to have done is the
mfr list and productline list fill in the appropriate (related) data based
on the vendor.  The tables are normalized and all the key values are present
(vendorid, mfrId, PLid, etc.)

Any suggestions?

WB


0
WB
10/10/2007 8:15:21 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1615 Views

Similar Articles

[PageSpeed] 21

Are any of the list boxes multi select?
-- 
Dave Hargis, Microsoft Access MVP


"WB" wrote:

> I am using Access 2k to resolve a form problem where multiple list boxes are
> partially dependant on the value(s) in other list boxes.  I keep reading
> that an unbound list box can be populated with data from the value of
> another list box, but how to do it is not explained.
> 
> I have Vendors, Mfr, and ProductLines with list boxes for each.  A vendor
> supplies the manufacturer's product lines.  What I want to have done is the
> mfr list and productline list fill in the appropriate (related) data based
> on the vendor.  The tables are normalized and all the key values are present
> (vendorid, mfrId, PLid, etc.)
> 
> Any suggestions?
> 
> WB
> 
> 
> 
0
Utf
10/10/2007 8:51:02 PM
no
"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
news:E39CB66F-31BE-472B-8FDD-17FA5030A396@microsoft.com...
> Are any of the list boxes multi select?
> -- 
> Dave Hargis, Microsoft Access MVP
>
>
> "WB" wrote:
>
> > I am using Access 2k to resolve a form problem where multiple list boxes
are
> > partially dependant on the value(s) in other list boxes.  I keep reading
> > that an unbound list box can be populated with data from the value of
> > another list box, but how to do it is not explained.
> >
> > I have Vendors, Mfr, and ProductLines with list boxes for each.  A
vendor
> > supplies the manufacturer's product lines.  What I want to have done is
the
> > mfr list and productline list fill in the appropriate (related) data
based
> > on the vendor.  The tables are normalized and all the key values are
present
> > (vendorid, mfrId, PLid, etc.)
> >
> > Any suggestions?
> >
> > WB
> >
> >
> >


0
WB
10/10/2007 9:02:06 PM
If they are not multi select, I would suggest using combo boxes instead of 
list boxes.  They are much easier to use.
The technique is to create a query as the row source for the second combo 
that is filtered by the value in the first (The same can be used for list 
boxes). It would be something like:

    Select MfrId, MfrName  FROM tblMfgr WHERE VendorID = cboVendor;

Then in the After Update event of the Vendor Combo, requery the Mfgr Combo

    Me.cboMfgr.Requery

Same for the 3rd.
-- 
Dave Hargis, Microsoft Access MVP


"WB" wrote:

> no
> "Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
> news:E39CB66F-31BE-472B-8FDD-17FA5030A396@microsoft.com...
> > Are any of the list boxes multi select?
> > -- 
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "WB" wrote:
> >
> > > I am using Access 2k to resolve a form problem where multiple list boxes
> are
> > > partially dependant on the value(s) in other list boxes.  I keep reading
> > > that an unbound list box can be populated with data from the value of
> > > another list box, but how to do it is not explained.
> > >
> > > I have Vendors, Mfr, and ProductLines with list boxes for each.  A
> vendor
> > > supplies the manufacturer's product lines.  What I want to have done is
> the
> > > mfr list and productline list fill in the appropriate (related) data
> based
> > > on the vendor.  The tables are normalized and all the key values are
> present
> > > (vendorid, mfrId, PLid, etc.)
> > >
> > > Any suggestions?
> > >
> > > WB
> > >
> > >
> > >
> 
> 
> 
0
Utf
10/10/2007 9:13:00 PM
Reply:

Similar Artilces:

File Size Limit??
Hi, Can anyone tell me if there is a limit to the file size of an Excel 2000 document. We've got a guy here who has a 63Mb spreadsheet, and keeps wondering why he's getting "Out of memory" messages every time he opens it. I've put an extra 512Mb in his PC, but he's still getting lock-ups. He's convinced that there are no limits to the size of the file, but I'm pretty certain that there is... Anyone help? Thanks, Jon. The limit is memory. -- Don Guillett SalesAid Software donaldb@281.com "Jon" <anonymous@discussions.microsoft.com> ...

Limits
I am doing a fairly easy programme on excel. It is a catchment runoff, and evaporation calculator. But i would like to place a limit on a cell, and have that exess go into another column, is this possible? EG 75 rainfall, soil can hold only 10mm more. SO 10mm INFILTRATES, ie that cell should have a limit of the max soil moisutre, the other 65mm RUNOFF's and thats a different column. ANYBODY PLEASEEEEEEEEEE!!!! lol You need a Workbook_Change event macro for that. The code you would write in that macro should first check that the change occurred in a cell within the range you want. ...

access2007 option group dotted line box around text
I have an access 2007 application with a form with a unbound option box with three option buttons and labels. When I select them sometimes the dotted box shows around 2 of the labels. The options have values 1, 2 and 3. Not sure why it is not just showing the dotted line box around the selected option? Any tips to fix it? Mark see my previous post for suggestions and resolution. I accidentally thought a deleted this one and it didn't post. Mark "Mark Andrews" <mandrewsNOSPAM@rptsoftware.com> wrote in message news:Okuv5ohaKHA.5608@TK2MSFTNGP05....

changing trend/series data by manipulating chart lines?
I would like to change series data in a spreadsheet that has been line charted by manipulating the chart lines or trend-lines. Is that possible in Excel? Thanks for any help. You mean change values by clicking and dragging points? It was possible in Excel 97 through 2003, but no longer in 2007. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 9/2/2010 9:02 AM, Ed wrote: > I would like to change series data in a spreadsheet that has been line > charted by manipulating the chart lines or trend-lines. Is that > possible in Excel? Thanks for any h...

Combo Box Values
Access 2007 How do I display a first and last name on the form from a combo box? I would like the address, city, and home phone number to fill in the appropriate fields on the form when selecting the name. You refer to the combo box's Column collection in its AfterUpdate event. Let's assume that the first name is in the second column, the last name is in the third column, the address is in the fourth column, the city is in the fifth column and the phone number is in the sixth column. You'd put their values into text boxes using code like: Private Sub cboSelecti...

cut and paste based on a condition
I have a excel spread sheet that pulls in from an oracle database. in column h:h i have 4 different criteria 15-1, 23-4, 4-4, NO SLA. would like to refresh the main page (where all the data is stored) tha run a macro to seperate it into 4 seperate worksheets (15-1, 23-4, 4-4 NO SLA) A cut and paste type function. I used the copy function from o the forum. It created alot more work. Thanks in advanc -- Message posted from http://www.ExcelForum.com It sounds like you want to steal some code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Look for: Update Sheet...

Address List Filter problem
I want to create an address list containing employees and distribution lists of employees. I do not want to show resource mailboxes or system mailboxes. I added a custom attribute to each employee and each dist list with the word Employee in it. I created a filter which looked for the custom attribute with Employee in it. The people show up correctly but the distribution lists do not. I have tried several variations but still have not found a solution. It seems that anytime I add a custom filter to an address list the Query based distribution lists no longer show up. If this is the way i...

Redirecting emails to another email address
Hi, Can a rule or service be created to redirect incoming emails from a specific email (sender) to another email address? Johan Yes, you can create a Wizard Rule to forward the message to another address. If you are not connected to an Exchange server you must keep your Outlook open in order to process the rule. -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Johan du Preez" <anonymous@discussions.microsoft.com> wrote in message news:1204001c44247$9ff65a10$a501280a@phx.gbl.....

2008 R2 dpm 2007 setup: SIS-Limited not found?
I'm trying to run the command line to install the SIS component via ocsetup.exe SIS-Limited /quiet /norestart but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. Any ideas what i need to do to get past this? Thanks It turns out you just need to add the file services role in r2, dont need the manual command.. "markm75g" wrote: > I'm trying to run the command line to install the SIS component via > ocsetup.exe SIS-Limited /quiet /norestart > > but it tells me it cant find this SIS-Limited component (on 2008 r2 x64).. ...

Help! IF function is too limited
Hi! I've created a drop-down list of cities in a cell, under which there are two more cells to be filled out with the address and zip codes corresponding to each of the cities. How can I do this, knowing that the original list is a three-column list made of city-address-zip? -- Ringo ------------------------------------------------------------------------ Ringo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27502 View this thread: http://www.excelforum.com/showthread.php?threadid=470178 Use VLOOKUP into the original; list using the DV value =VLOOKUP(B...

Cell Selection?
I have a colum of numbers 198 0 0 198 2 What I need, is to be able to select 3. the 198's are not going to be used in the next part of my equation. That seems simple enough, however all the numbers could be usable (not 198) and I need to use just the first three. Any ideas? One interp / way, using non-array formulas Assuming source numbers in A1 down Put In B1: =IF(COUNT($C$1:C1)>3,"",C1) In C1: =IF(ISERROR(SMALL(D:D,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))) In D1: =IF(A1="","",IF(A1=198,"",ROW())) Select B1:D...

Using data forms
I would like to create a worksheet where the data is entered in a data forms dialog box. Is there a way to set the worksheet up so that when I bring it up the data form dialog box would automatically show? Also is there a way that I would be be able to to switch between the data form and the worksheet? Thanks You can switch between data form and worksheet by using the menu option Data>Form... options You could record a macro doing that and assign it to a toolbar button or one on the sheet. If you want it presented on opening the workbook, you could use the workbook_open() event t...

Adjusting One Side of a Box
Greetings, When in Design View and a text box, a label box, or a line are selected (just using those as examples), if I hold the "Windows Flag" key down pressing one of the cursor keys, whatever I've selected moves one dot in the direction I want. If I hold the Cntr key down while pressing the cursor key, it appears that I have 4x the sensitivity in moving (i.e., four presses of the cursor key moves the distance of one dot). Is there any way to do the same thing when needing to adjust just one side of a drawn box? Thanks. Ken -- Message posted via AccessMonster.com http:/...

Related Data Between 2 Different Workbooks
Hello everyone, I need to create 2 workbooks, which have data relationships. I don't know how to get data connect when I change data in the first workbook, this data will also be automatically changed in the second workbook. Please give me a hand. Thank you very much and I am looking forward to hearing from you. Regards, Peter -- freeecoom ------------------------------------------------------------------------ freeecoom's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27507 View this thread: http://www.excelforum.com/showthread.php?threadid=470378 On...

Pay To List
How do I delete some of the names in my "Pay To" list (the "pay to" list that is used when making entries into your accounts)? I deleted a large amount in the payees list but they still show when selecting "pay to" when creating a new account entry. I tried the FAQ page and online help and couldn’t find anything. Thanks! John Normally deleting them from the explicit list will get them out of the pulldown/autocomplete list. So, your list at Account List More|Categories and Payees|Payees is much shorter than it was and still you see names not in this list...

I want to set up quarterly windows based on a date
I have a date in column A. I want to set up quarterly windows using this date. What is the formula for adding 3 months to date? I can get the ending date by subtracting a day from the beginning of the next quarter date. (A1) + 3 month : =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) HTH -- AP "emiller" <emiller@discussions.microsoft.com> a �crit dans le message de news: 7A938094-801F-42BA-9316-28D636C6FA8A@microsoft.com... >I have a date in column A. I want to set up quarterly windows using this > date. What is the formula for adding 3 months to date? I can get the >...

Making one record read-only
Hello, I have a form which is used to input records into the main table, is there anyway in which I could have a drop-down box or tick box which once selected will lock that individual record? For example if you are on record 24 and tick it it will make 24 read only until the tick is unchecked? Thank You -- Message posted via http://www.accessmonster.com Add a Yes/No field to the table, and on the form bind a checkbox control to that field. Then, in the Form_Current event, if the checkbox is checked lock all controls on the form (or at least the ones you want locked). You can't rea...

Default value of combo box based on DLookUp
Hi, I have 2 problems I'm trying to overcome regarding just one form which gets its data from 1 table. I'm trying to set the default value of a combo box to be the same as another combo box on the same form. The scenario is: I have a form for entering info about financial planning Statements Of Advice. Each client has a Plan Writer prepare their plan. To find the plan writer that last prepared the plan for a client I have a combo box with a DLookUp as its Control Source. =DLookUp("Plan_Writer","qryDefault_Planwriter","[Client]=Form![Client]") I'm...

Selecting one day or another in invite
Can I create a meeting invitation that recurs on Tuesday and Thursday, but the invitees can select one or the other that they want to attend? In other words, I'll be having the same meeting twice a week and some will attend one and some the other but I want it in one invite. Any thoughts? Thanks! Sure - See http://www.slipstick.com/calendar/pickmeeting.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.out...

Making sure an Element Group contains elements with either two attributes or one attribute
Hi all, I'm trying to put together an XSD for an XML type I'm experimenting with. I was just wondering whether it's possible for me to validate whether an image element, which I've defined in my XSD, can be enforced to contain either one named attribute, or two other named attributes. My XML document will eventually look something like this: <Vehicles> <Vehicle> <VehicleID>1</VehicleID> <!-- Various other elements --> <Images> <Image ImageURL="http://www.mywebsite....

removing fonts from dropdown list in Word 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When you click on Fonts in Word, you get a very long list of fonts, most of which I never use and would therefore like to remove. <br><br>How do I do that in Word 2008? I could do that in Word 2004! <br><br>I have already disabled them in Font Book. <br><br>Thank you. If you "never use" them, why have them on your system? Keeping fonts loaded that you do not intend to use simply wastes memory and slows your whole system down. If you remove the fonts from the syste...

Q: Rules limit
There is a limit for Exchange Rules on a 32K-size (KB147298) Whether there is a way to change this limit for single users? For me already three users have addressed with such requests :- Thanks Alexander Kenin This is not a configurable limit. It's limited by the size of one RPC packet. "Kenin Alexander" <anonymous@discussions.microsoft.com> wrote in message news:80981636-20EB-4C64-B514-F9BCA635AFE7@microsoft.com... > There is a limit for Exchange Rules on a 32K-size (KB147298). > Whether there is a way to change this limit for single users? > For me already thre...

Hide a text box
On sheet 3 of my workbook I have created a button (button 62) and a text box (textbox 63). What I want to do is use the button to toggle the text box on and off. By that I want to be able to show the box or hide the box. I have tried to copy code from the MS Visual Basic Help but it did not work, basically because I have no idea what I am doing. Can someone lease help? Orf Bartrop Under my Excel - 2000 I don't think you can hide a text box But I hope Im wrong it would be a good idea Steve On Wed, 09 Aug 2006 05:45:55 +0100, Orf Bartrop <orf@southcom.com.au> = wrote: > On...

Control box question
I have a form that opens when you run a query. (example: employee last name) Then when I want to look up another name I want to do it from the form instead of closing the form. So I added a control box to the form to run the query again. The problem is that it opens up a new form, so if I do multiple searches I will have a lot of open forms. So the the question is: Can I set up the control box to run the query and open it in the same form or at least close the last form when it opens a new one? If so how is this done? Thanks Eric How about placing an unbound combo box at t...

Delete old chartobject and create a new named one?
Hi, I need a subroutine that delete the old chartobject in a worksheet, create a new chartobject , name it as I want. Reason is that if the user delete the chart, I want to recreate it. I know how to protect it but feel that the user had to be able to change labels, colors etc. More than grateful to every suggestion! Kind regards tskogstrom Hi, This will delete a chart object, but not sure how you are identifying which chart object. Activesheet.chartobjects(1).delete ' create and give it a name with Activesheet.chartobjects.add(1,1,400,300) .name = "MyName" end with...