Combo boxes 11

[LowNissan]

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.

 

[Telsa]

Yes, it can be done. You can create a query that will point to the make to filter the models with. I've seen it done but can't quite remember exactly how it worked (as I do things like that in VBA coding.)

 

[LowNissan]

Is there a simple VB way to do it?

 

[Bernadette]

Check out article Q97624 on Microsoft's Knowledge Base. It tells you exactly how to do what you are looking for.

Good Luck
Bernadette

 

[LowNissan]

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.

 

[jfhewitt]

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.

 

[CindiN]

I have a sample DB from MS Web site that has exactly what your trying to do. (Although with different subjects) If you can give me your e-mail address, I'll send it to you.

 

[LowNissan]

CindiN, my e-mail is lownslownissan@home.com
Thanks!

 

[CindiN]

Ok, sending!

 

[Guest_imported]

Hi CindiN, could I also get the sample DB from the MS Web site. My email address is rt2423@ureach.com

 

[mattpearcey]

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

 

[Computethis]

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

 

[mattpearcey]

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

 

[dolfin013]

would love to see the code myself.
mtrudel0000@hotmail.com

 

[racerboy]

Can I jump on the bandwagon too? )

frank.bosley@roche.com

 

[mattpearcey]

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

 

[tomco22]

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 &quot;On Not in List&quot; 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(&quot;frmOrderEntry&quot(&quot;VendorName&quot
strMsg = NewData & _
&quot; isn't an existing item. &quot; & _
&quot;Add a new item for &quot; & _
VendName & &quot;?&quot;
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion, &quot;Invalid Item&quot

Select Case mbrResponse
Case vbYes
DoCmd.OpenForm &quot;frmAddProduct&quot;, _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
Response = acDataErrAdded
DoCmd.Close acForm, &quot;frmAddProduct&quot;
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:

http://www.vectormachinery.com

Tom

 

[KerryL]

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

 

[Basia]

One more person in need of this sample database code. CindiN or anyone else, please, please email this code to basiawork@aol.com.

Many thanks in advance!

 

[gusbrunston]

The sample database y'all want is found on

http://www.microsoft.com

in their downloads section. The self-extracting file's name is: &quot;frmsmp00.exe&quot;. Its 845KB. Its pretty neat... I search for it using Access2000, probably another name for Access97, like &quot;frmsmp97.exe&quot;?
Gus Brunston
An old PICKer
padregus@home.com