I'm setting up a form to insert info into my table about vehicle info. Is there a way that I can have 2 combo boxes, one with makes (ie Ford, Chevy..) and one with models (ie C/K, Tahoe, Ranger...), so when I select the make, it'll make the models field only load vehicles for that model. So if I click on Chevy, it'll only load chevy cars.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Combo boxes 11
- Thread starter LowNissan
- Start date
- Thread starter
- #3
-
1
- #4
Bernadette
MIS
Check out article Q97624 on Microsoft's Knowledge Base. It tells you exactly how to do what you are looking for.
Good Luck
Bernadette
Good Luck
Bernadette
- Thread starter
- #5
I'm still fairly new to Access, could someone make me a small example or give me a step-by-step way. Say using the data Chevy and Ford for vehicle makes and Suburban, S-10, Blazer for chevy and F-150, Ranger for Ford. How can I make it so when I select Chevy in one combo it just brings up the chevy trucks in the next? thx in advance.
Your question is a bit unclear. If you select Ford, you will get all models. You must make a second selection to narrow the Fords down. Are you talking about what some databases call a cascading listbox? You select Ford, and a second box drops down with all the Ford models to select. If that's what you want, I don't think you can do it in Access. If you can, I'd like to know how also.
-
5
- #7
- Thread starter
- #8
Guest_imported
New member
- Jan 1, 1970
- 0
Hi CindiN, could I also get the sample DB from the MS Web site. My email address is rt2423@ureach.com
mattpearcey
Technical User
CindiN
Dont suppose i could grab a look at this database? I hae been searching threads for a similar problme with combo boxes that an example might help me work through.
matt.pearcey@midas-kapiti.com
Thank you for your help already.
Matt Pearcey
Dont suppose i could grab a look at this database? I hae been searching threads for a similar problme with combo boxes that an example might help me work through.
matt.pearcey@midas-kapiti.com
Thank you for your help already.
Matt Pearcey
Computethis
IS-IT--Management
Can someone email the sample DB that CindiN was sending out - I have an application soooo ready for that - they dropped the request on my desk just two weeks ago!!!
Thanks!
Mitch
MitchBryant@att.net
Thanks!
Mitch
MitchBryant@att.net
mattpearcey
Technical User
Could someone also send me a copy? Ive still not had any word about getting my hands on a sample.
I hope i dont sound too greedy, but like Mitch, this really would help my project.
Thank you for your help already.
Matt Pearcey
I hope i dont sound too greedy, but like Mitch, this really would help my project.
Thank you for your help already.
Matt Pearcey
mattpearcey
Technical User
I still havent got it, so if anyone does have this code. I really would appreciate an email.
matt.pearcey@midas-kapiti.com
thanks guys Thank you for your help already.
Matt Pearcey
matt.pearcey@midas-kapiti.com
thanks guys Thank you for your help already.
Matt Pearcey
LowNissan,
If you haven't had your question answered here is some sample code from my database. If I understand your question you want to populate a combo box (Mustang, Taurus, Escort) based on a different combo box (Ford). I perform this function in my "Purchase Orders" database. When an employee wants to order a part he chooses a vendor from a combo box. He then goes to a combo box on a subform. This combo box lists only the parts available from that particular vendor. After each part selection the user can modify the quanty and unit price for each item. The driving force for this process is this code which is the "Row Source" property in the second combo box:
SELECT DISTINCTROW [tblProduct].[ProductName], [tblProduct].[ProductID], [tblProduct].[PartNumber], [tblProduct].[UnitPrice], [tblProduct].[VendorName], [tblProduct].[CatNum] FROM tblProduct WHERE ((([tblProduct].[VendorName])=[Forms]![frmOrderEntry]![VendorName])) ORDER BY [tblProduct].[ProductName];
The "After Update" sub in this combo box fills in the other fields with this sub:
Private Sub cboProductName_AfterUpdate()
If Len(cboProductName.Column(1)) <> 0 Then
ProductID.Value = cboProductName.Column(1)
PartNumber.Value = cboProductName.Column(2)
txtUnitPrice.Value = cboProductName.Column(3)
txtVendorName.Value = cboProductName.Column(4)
txtCatNum.Value = cboProductName.Column(5)
Rancor
Else
Exit Sub
End If
End Sub
If user enters an item that is not already in the vendor's list he can add the item on the fly. The "On Not in List" sub takes the user to a form that already has the vendor name and part name filled in. The user just adds the part number, category, price, and clicks OK. The code takes him back to the original form with the new info (except qty) already filled out. The code looks like this:
Private Sub cboProductName_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg, VendName As String
VendName = Forms("frmOrderEntry"("VendorName"
strMsg = NewData & _
" isn't an existing item. " & _
"Add a new item for " & _
VendName & "?"
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion, "Invalid Item"
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmAddProduct", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
Response = acDataErrAdded
DoCmd.Close acForm, "frmAddProduct"
Case vbNo
Response = acDataErrContinue
End Select
End Sub
If you need further assistance email me or call. My phone number & email address (contact webmaster) is at my web site:
Tom
If you haven't had your question answered here is some sample code from my database. If I understand your question you want to populate a combo box (Mustang, Taurus, Escort) based on a different combo box (Ford). I perform this function in my "Purchase Orders" database. When an employee wants to order a part he chooses a vendor from a combo box. He then goes to a combo box on a subform. This combo box lists only the parts available from that particular vendor. After each part selection the user can modify the quanty and unit price for each item. The driving force for this process is this code which is the "Row Source" property in the second combo box:
SELECT DISTINCTROW [tblProduct].[ProductName], [tblProduct].[ProductID], [tblProduct].[PartNumber], [tblProduct].[UnitPrice], [tblProduct].[VendorName], [tblProduct].[CatNum] FROM tblProduct WHERE ((([tblProduct].[VendorName])=[Forms]![frmOrderEntry]![VendorName])) ORDER BY [tblProduct].[ProductName];
The "After Update" sub in this combo box fills in the other fields with this sub:
Private Sub cboProductName_AfterUpdate()
If Len(cboProductName.Column(1)) <> 0 Then
ProductID.Value = cboProductName.Column(1)
PartNumber.Value = cboProductName.Column(2)
txtUnitPrice.Value = cboProductName.Column(3)
txtVendorName.Value = cboProductName.Column(4)
txtCatNum.Value = cboProductName.Column(5)
Rancor
Else
Exit Sub
End If
End Sub
If user enters an item that is not already in the vendor's list he can add the item on the fly. The "On Not in List" sub takes the user to a form that already has the vendor name and part name filled in. The user just adds the part number, category, price, and clicks OK. The code takes him back to the original form with the new info (except qty) already filled out. The code looks like this:
Private Sub cboProductName_NotInList( _
NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg, VendName As String
VendName = Forms("frmOrderEntry"
("VendorName"strMsg = NewData & _
" isn't an existing item. " & _
"Add a new item for " & _
VendName & "?"
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion, "Invalid Item"
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmAddProduct", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
Response = acDataErrAdded
DoCmd.Close acForm, "frmAddProduct"
Case vbNo
Response = acDataErrContinue
End Select
End Sub
If you need further assistance email me or call. My phone number & email address (contact webmaster) is at my web site:
Tom
What a coincidence.... I've been trying to do the same thing -- limit the selections in a second combo box based on what is chosen in the first combo box -- for a couple days.
If anyone has the sample database CindiN offered to share I sure would appreciate a copy. My email is lehmank@state.mi.us
I will also snip the code tomco22 shared to see if I can get that to work. Thanks
Kerry
If anyone has the sample database CindiN offered to share I sure would appreciate a copy. My email is lehmank@state.mi.us
I will also snip the code tomco22 shared to see if I can get that to work. Thanks
Kerry
-
3
- #20
gusbrunston
Programmer
The sample database y'all want is found on in their downloads section. The self-extracting file's name is: "frmsmp00.exe". Its 845KB. Its pretty neat... I search for it using Access2000, probably another name for Access97, like "frmsmp97.exe"?
Gus Brunston
An old PICKer
padregus@home.com
Gus BrunstonAn old PICKer
padregus@home.com
- Status
Similar threads
- Locked
- Question