Cascading Combo Problem

[scouser]

Requery not working as I wish?

I am muddling through......
A user can input a New Order and select a Vehicle Make and Model.
However if they naviagte away from the record the Vehicle Model selected is lost?

I have attached a screen dump to better explain. Also I ahve tried numerous variations of On Current Event Requery. My latest offering being:

frmOrders is the Master Form
frmOrderDetails is the sub

The code below is on frmOrderDetails On Current Event

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

As with the others the above does not work!!

I simply want the user to be able to select a Vehicle Make and Model for each Order Input.

Can anybody complete the puzzle?
Thanks in advance,
Phil.

 

[unclejoe]

hi scouser,

Pat had mentioned that this will happened. If you have bound that text field, you can see that the table field is set correctly.

What i can suggest is a command button for a popup form to insert new rows for the "frmOrderDetails" and make it as a single form, set properties to - Data - Data Entry to "Yes".

First, create a new popup form from the "tblOrderDetails" with wizard and save. Then on the main form, create a command button to open the popup form. The wizard will show you the list of form to open. And at the last textbox entry, use VB to close the popup form and refresh the main form.

As for the "frmOrderDetails", i would make as a query form for viewing the data entered.

HTH

 

[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.

 

[Pat Hartman]

When I fixed the query, your form worked for me. I copied it and pasted it here so there shouldn't be any typos.

 

[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.

 

[Pat Hartman]

I deleted it. Yes you can just paste the SQL string into the combo's RowSource.

 

[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!!!!

 

[Pat Hartman]

get rid of the code that changes the rowsource and get rid of the literal criteria in the queries. I'll try to explain in simple English how the cascade works. It really is simple. It takes only two lines of code. You'll need to figure out what you did wrong or post the db. Make sure that both combos have the correct column count, bound field, and column widths. If all else fails, delete the two combos and start again. It will probably be easier than finding the problem.

1. The RowSource for combo2 MUST be a query that references the form field for combo1 as its criteria.
2. requery combo2 in the AfterUpdate event of combo1.
3. requery combo2 in the onCurrent event of the form.

 

[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!!

 

[Pat Hartman]

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

 

[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.

 

[Pat Hartman]

I fixed the db. You had deleted the rowsources from the combos so I rebuilt the querydefs. You also didn't have the correct number of columns in the model combo which was probably what was causing all the confusion originally.

Combos DO NOT WORK properly if the column count is wrong!!!!! File this away and make it one of the first things to check.

I should send you a bill for services -

Make sure that both combos have the correct column count, bound field, and column widths.

 

[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.

 

[Pat Hartman]

It should be 2, not 12. Looks like I was trying to type over the 1 and failed. Also, we learned someting else, the combo will work with the wrong column count as long as the count is > than the actual number of columns rather than <.

The column count is the number of columns selected by the RowSource query or RowSource table. You can have many more columns than are shown in the combo if the RowSource is a table. Usually when the RowSource is a query, it only includes the minimum number of fields. In the case of the Model combo, you need ModelID, ModelName, AND MakeID. Without MakeID, you can't make the combo's cascade.

 

[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.