Cascading Combo Problem

[scouser]

Pop up Form

Thanks for replying.
I have kind of done what you suggested but did not fully understand what you maent (if I am honest).

I created frmOrderDetailsPU (Pop Up).
Stuck a cmdEnterOrderDetails button on frmOrders which opens above form.

Take a look at what I have done and advise.

To be honest I do not really want the user to have to generate a Pop up Form in order to be able to enter Order Details. Also my effort does not work!!

I would much prefer frmOrderDetails being a single form. However I am unable to get the form to retain the vehicle Model for individual orders.

Any one help me close this one down as it's been messing with my tiny brain for well over a week now!! (Can not afford any more hair loss!!)
Cheers,
Phil.

 

[scouser]

Hello Again

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]));

Hello again Pat. Sorry if I sound brainless ....

Should I paste the above into as the row source for the cboModleID?

I want the sub frmOrderDetails to act a single form (as you explained the problems that lie with having continuous).

I want this to retain individual Order details for each Order Record (Namely the Vehicle Make and Model) for which the service was provided.

If you pasted the code into your copy of the DB and it worked, could you re-post your copy of the DB?

Must have nearly nailed this one?
Many Thanks,
Phil.

 

[scouser]

Well I never.....

Thanks Pat, it worked this time!!

 

[scouser]

Why oh Why oh Why!

OK so I am now officially frustrated. It WAS working for 3 or 4 orders.

Pat (if you are viewing) I did as you suggested and made the row source of the cboModelID:

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

I input 4 test orders and then navigated back througth the records using the record selectors. The ModelID has dissapeared from each order. I checked the row source and it had changed to:

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

I then re-pasted your code. This pasted in but as soon as I click out of the row source the row source value defaults to:

SELECT tblModels.ModelID, tblModels.Model

Does anybody have ANY idea what might be causing this behaviour on the Cascading combo?

Thanks once again for any help.

 

[scouser]

This might help

I have the following on the After Update event of cboMakeList:

Private Sub cboMakeList_AfterUpdate()
Me.cboModelID.RowSource = "SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE tblModels.MakeID =" & Me!cboMakeList & ";"
Me.cboModelID.SetFocus
'Me.cboModelID.Dropdown
End Sub

So now I know why the row source defaults to:
SELECT tblModels.ModelID, tblModels.Model

Question is, how can I get the cascading combo to work using Pats code?
Well at least I know why its not working!!

 

[scouser]

Changed After Update Event

I have changed After Update Event to:

Private Sub cboMakeList_AfterUpdate()
Me.cboModelID.RowSource = "SELECT tblModels.ModelID, tblModels.Model FROM tblModels WHERE (((tblModels.MakeID)= [Forms]![frmOrders]![frmOrderDetails]![MakeID]));"
Me.cboModelID.SetFocus
Me.cboModelID.Dropdown
End Sub

The row source still converts back to:
SELECT tblModels.ModelID, tblModels.Model ???

HOW CAN I STOP THIS AND RETAIN MY SANITY!!!!

 

[scouser]

Requery

I have tried Requery but I guess my problem is a little more complex!!

Got to keep smiling even through the wifes nagging me for spending all night on the PC!!

 

[scouser]

Ha!

Your problem may be complex. But the solution is simple. Delete the combos and start again.

Pat I'm SIMPLE!!

I deleted the combos as suggested. I then attempted to repaste your code into the row source fro cboModelID.....

To be honest Pat my brain is strained and I can't think logically.....I have been at this for hours..........

The column widths / Count + Bound all look good to the untrained eye.

I have re-posted my latest DB as requested and hope you can advise the "Simple2 2 lines of code?

Many Thanks,
Phil.

 

[scouser]

Back Again

Pat:

The RowSource for combo2 MUST be a query that references the form field for combo1 as its criteria.

Does this mean I have to write a query? Or add some code to the row source?
I get alittle confused with all the termanology?

Thanks,
Phil.

 

[scouser]

Diamond

Pat as we say in the UK 'You are a diamond'. I will have a look at the DB now. My uderstanding of the column count was this:

As the combo was bound to MakeID and I wanted the combo to display the Vehicle Make (the second field in tblMake) I thought this meant 2 columns. I set first one to 0 as I did not want to display it in the combo.

I will now have a look at how it should be done!!
Many Thanks,
Phil.

 

[scouser]

Superb

Pat I doubt I would have got there.........
For a start the row source......never thought of that one.......
Column Count 12 on cboMakeID? Why 12? Will not argue as it is obviously correct.

cboModelID, column count 3...again will not argue!!

Many thanks Pat for having patience!! Boy this is a long thread.......
Phil.

 

[scouser]

Thanks Pat

Thanks for that Pat.
It got me thinking regards your reply to my post:
http://www.access-programmers.co.uk/forums/showthread.php?t=79144

You made the cboMake and cboModel row source = to queries.
The control source is taken from qryOrderDetails: MakeID and ModelID.

So what is GOOD design to overcome this?

My understanding of Access is limited. I linked tblOrderDetails to tblMakes and tblModels via MakeID and ModelID. Maybe this was incorrect design?

Thanks for all your help on this matter.
Regards,
Phil.

 

[scouser]

You are Brilliant!!

Pat you are a superstar. I have implemented your solution. I have only one (OK maybe 2 questions!!).

After inputting a second record via the modified combo, if I choose to revisit the first record entered the list items are relative to the LAST entry.

i.e.
Record One -
Chose Make 'Ford' second combo displays all associated Models for Ford - Selected 'Focus'

Record Two-
Chose Make 'Bentley' second combo displays all associated Models for Bentley - selected 'Vanquish'.

If I then revisit the first entry it shows only models for 'Bentley' and not 'Ford'. This is only a minor blip, I presume it would be the same for your example?

Also (not your problem!). You helped me out ENORMOUSLY with this issue.

Rich pointed out that the value being saved in tblModels for MakeID is the text value and
not the MakeID? I can not see how to resolve this? i.e.
ModelID = 1
MakeID = Ford (this should be a numerical value?)
Model = Focus

Anyhow, I have attached my DB so you can see how the combo is behaving.

Once again, great solution!!
Many Thanks,
Phil

PS: I have added some custom nav buttons to spice things up!!

 

[scouser]

Woops Sorry!

Sorry Pat.......having to hold the shift key is a pain I know. I forget each time I post! (promise not to again!!). I am modifying a DB for my brother. I prevent the menus from showing so he doesn't meddle!

Attention to detail is usually good........honest!!

No it is not the same for my example. My example works.

Should have known that!!!!!!

Cheers Pat....getting late here!!
I will implement your suggested changes and then get some much needed beauty sleep!!
Phil.

 

[scouser]

All Done

Many thnaks Pat, all working A-OK.