Cascading Combo Problem

[scouser]

I have attempted to implement Mile-O's example of a Cascading Combo box but surprise surprise my one does not work!! (his is superb!!)

Maybe I misunderstand the concept (more than likely). Maybe I am unable to acheive what I have been trying to acheive for more than 3 days now!!

I have a main Orders form with a sub orderDetails. Both forms are designed from queries. I have attempted to make cboMakeList and cboModel cascading. So when a user selects a make the next combos show only the models for that make!! After selecting a make a query parameter box appears. I can close that or enter a value. The next combo is then blank??

What am I doing. Can somebody help or loan me a gun!!!!
Phil.

PS: Various Access versions of the database are attached.

 

[Meltdown]

Change the code in the After UpDate event of cboMakeList to ...

Me.cboModelID.RowSource = "SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE tblModels.MakeID =" & Me!cboMakeList & ";"
Me.cboModelID.SetFocus
Me.cboModelID.Dropdown

 

[scouser]

Love

I love you!! What can I say, what a good xmas present!! I still have lots to do but I am SOoooooooooooooooo chuffed.
Have a good Christmas (if you celebrate!!).
Phil.

 

[Mile-O]

Me.cboModelID.RowSource = "SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE tblModels.MakeID =" & Me!cboMakeList & ";"

Me.cboModelID.RowSource = "SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE tblModels.MakeID =" & Me.cboMakeList & ";"

Also, I'd just save the query and set that as the RowSource instead so that Jet doesn't have to recreate a new query every time.

SELECT ModelID, Model
FROM tblModels
WHERE MakeID = [Forms]![FormName]![cboMakeList]
ORDER BY Model;

 

[scouser]

Hum Bug

Hi Mile-O. I was up late last night attempting to implement your Cascading Combo example....wish I could have 5% of your knowledge (that would be 5% more than I currently have! Ha!).

I have had a play and it is not quite there. If I select a vehicle Make then yes the next combo displays the Models for that make. However if I enter a second record in the order details for this updates the previous entry to match?

Therefore. If I have an order say (ID001) and I input an order line I would select:

Make Model
Ford Mondeo

the for line 2 of the order I may select:
Make Model
Audi A3

If I do this then both lines become:
Make Model
Audi A3
Audi A3

Any ideas why?
Cheers,
Phil.

 

[Mile-O]

Are you doing this on a continuous form?

 

[scouser]

Internet!!

My works Internet crashed....power outage.....not a happy bunny!

Mile-O, yes the form is continuous.

Secondly I attempted your suggestion:

Me.cboModelID.RowSource = "SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE tblModels.MakeID =" & Me.cboMakeList & ";"

I put that on the AfterUpdate event of the cboMakeList.
On the Row Source of the same control I put:

SELECT ModelID, Model
FROM tblModels
WHERE MakeID = [Forms]![FormName]![cboMakeList]
ORDER BY Model;

This does not work for me (for whatever reason).
I get a Parameter Query window pop up requesting:
'Forms!FormName!cboMakeList'

I also tried to make the row source: qryOrderDetails. This just produced a number 8 in the cboMakeList? So I am back to the selection problem...see attached jpeg to give you an idea (I hope this will help!)

Any ideas?
Phil.

 

[scouser]

Nealrly There??

I KEEP plugging away.....The form works but it doesn't work?
The attached DB opens on frmOrders. If you select a vehicle Make from the combo (cboMakeList) then the next field (cboModelID) shows only those Models for the selected make. If you then Tab away from this field (next field = service description) an error is generated:

The Microsoft Jet database engine cannot find a record in the 'tblServices' with key matching field(s) 'ServiceID'

I read up on this but can not see why I receive the error as the ModelID field has nothing to do with the Service table? However, the field I want to tab to takes its value from the ServiceID.

It does allow me to click in the desired filed without error?

If I navigate away from the record and back again it loses the data entered for the Vehicle Model.

Enough rambling, the attcahed database explains all!!!

Help appreciated as always.....I feel that I am close!!!
Thanks,
Phil.

 

[scouser]

The Microsoft Jet database engine
cannot find a record in the 'tblServices' with key matching field(s) 'ServiceID'

OK, I appear to have got round this by swapping the field order around (probably not correct!!).

Now.... If I enter 3 lines in the frmOrderDetails and then delete one line, ALL Vehicle Model Fields are deleted also? WRONG!!!

Once again DB attached as I find it helps to view........picture paints a thousand words and all that!!

I am hoping somebody will make my Christmas!!

 

[scouser]

Lastly!!

I do not know if what I am attempting is possible the way I am attempting it!
This is what I have: (all the relevant tables etc regards this issue).

Tables:
tblCustomers / tblOrders / tblOrderDetails / tblServices / tblMakes / tblModels

Relationships:
tblCustomers (PK CustomerID) to tblOrders (CustomerID FK) 1-M
tblOrders (PK OrderID) to tblOrderDetails (OrderID FK) 1-M
tblServices (PK ServiceID) to tblOrderDetails (FK ServiceID) 1-M
tblMakes (PK MakeID) to tblOrderDetails (MakeID FK) 1-1
tblMakes (PK MakeID) to tblModels (MakeID FK) 1-M

Note: tblOrderDetails contains FK's MakeID and ModelID.

Queries
qryOrders and qryOrderDetails

Forms
frmOrders and frmOrderDetails

The form frmOrders contains a sub form frmOrderDetails. (Master / Child Link is OrderID). The forms are designed from the queries qryOrder and qryOrderDetails.

I have included MakeID and ModelID in the qryOrderDetails and have attempted to make these fields on the form ‘Cascading Combos’. (cboMakeList and cboModelID)

Form-frmOrders (Master) is a Single Form. Form-frmOrderDetails (Sub) is a Continuous Form.

On the frmOrderDetails MakeID I have made the Row Source for cboMakeList:

SELECT tblMakes.MakeID, [Make] AS Expr FROM tblMakes ORDER BY [Make];

I have put the following on the After Update Event of the combo cboMakeList

Me.cboModelID.RowSource = "SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE tblModels.MakeID =" & Me!cboMakeList & ";"
Me.cboModelID.SetFocus
'Me.cboModelID.Dropdown

On the cboModelID I have made the row source:

SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE tblModels.MakeID;

I want the user to be able to enter ‘Many’ order details lines on a single order.
I want the user to be able to select a Vehicle Make from the cboMakeList.
I want the cboModelID to display only those Vehicle Models relative to the Make selected. (ii.e. If Ford is the selected Make, then the cboModelID will show only Mondeo, Galaxy, Sierra).

I want the user to be able to select different Makes and Models for each order detail line if required.

Well I hope that is comprehensive!!

My last posted DB does not function as it should.

I am hoping for a solution for my Christmas Pressie!!
Many thanks to all that have helped me over the past 12 months.
Kind Regards,
Phil.

 

[scouser]

Nearly 2005

Hi guys. Hope you all had a good Xmas.
I am still stumped by this one. Can anybody advise?
Thanks in advance,
Phil.

 

[jim11]

Im stuck in a similar situation.
I really really need help please.

This is my prob:

http://www.access-programmers.co.uk/forums/showthread.php?t=78764

Thanks
Jim

 

[kDreamer]

Me too - but different

I also had problems with cascading combo boxes that were on tables linked together. I didn't put the select statement on the event - I created unbound combo boxes that when updated put the new data in the bound boxes. However when it goes to perform update I get the error:

Unable to update table due to PK or index multiple key violations. (not exactly word for word but you get the drift.

I may test out adding the select statement for the event and see if I can solve the problem.

KG

 

[scouser]

Stumped

So that's 3 of us stumped!!!!

 

[unclejoe]

Hi scouser,

I don't know what are you trying to do to the sub form combos, anyway, you did not bound the field "MakeID" and "ModelID", that's why they disappear.

As for the "[Forms]![frmOrderDetails]![cboMakeList]" delete it for the query of MakeID rowsource.

I had a hard time at first, but at the second time seems to work.

PS: Also you need to adjust your Tab Order, i think is the cause of the error message. Your Service Description field was skipped, this may be the cause.

 

[Pat Hartman]

Cascading combos won't work properly on continuous forms. The problem is that Access only keeps a single set of properties for the form. A continuous form/subform may show 10 records. Each of those would be shown on an instance of the form but there is ony 1 set of properties for the 10 records. If you can live with some number of the entries going blank at times (don't worry the data is still there), you can use them. The visible value of the combo comes from the RowSource query. It displays the ControlSource value. So if your controlSource is Stratford and the RowSource is selecting cities in Connecticut, Stratford is visible. However, if on the next record, you choose New York as the state, only cities in New York will be included in the RowSource so the Stratford in the previous record will disappear. It is still in the ControlSource, it is just that since it is no longer in the RowSource, you can't see it. Your onCurrent event should requery the city combo. If it does and you move the cursor back to a row with Connecticut as the state, rows with the connecticut cities will now become visible and the New York ones will go blank.

OK, Confused? You have to play with it to see what is happening. I have never discovered a good way around it. The problem is even worse if the combos are not bound.

 

[scouser]

Thanks

Many thanks to all those that have responded to my plight. With what is going on in the world at the moment this is of little relevance.

Particular thanks to the last 2 posts 'unclejoe' and 'Pat'. Joe thanks for taking time to look at the DB, I will implement your suggestions the first chance I get.

Pat great explanation.....I may choose to shelve the idea.....I will speak with my brother and see if he can live with the glitch!!!

Hope 2005 is a good year for us all.

Once again, many thanks.
Phil.

 

[scouser]

Requery Problem?

Right.....I am ready to compromise!!

I have made the frmOrderDetails a Single Form as opposed continuous.
However each new order will not retain the selected Vehicle Details?

Any ideas on how I can do this? I tried the following on the On Current Event of frmOrderDetails:

Private Sub Form_Current()
Me.cboMakeList.Requery
Me.cboModelID.Requery
End Sub

The above did not have the desired affect!
Thanks Guys,
Phil.

 

[Pat Hartman]

The query for the model combo was incorrect. It had a hardcoded value of 3 as selection criterial for the make. The criteria needs to reference the Make combo.

SELECT tblModels.ModelID, tblModels.Model
FROM tblModels
WHERE (((tblModels.MakeID)=[Forms]![frmOrders]![frmOrderDetails]![MakeID]));

 

[scouser]

Row Source

Hi Pat, thanks for taking time to reply.

I replaced the 'Row Source' on the cboModelID with your code.
However it defaults back to:

SELECT tblModels.ModelID, tblModels.Model

The selected value is then lost when a new record is entered?
What am I doing wrong? Must be something simple...........(he says in hope!)
Many Thanks,
Phil.