Combobox setting other combobox values problem

[maxmaggot]

My database uses a form that allows the user to choose an expense type from a list. Based on their selection in the afterupdate function, I populate a second combobox with values related to the first. If the first selection doesn't need to use the second combobox then I set its visibility off and store NA in its place. For some reason for which I cannot remember I have a temporary combobox (not visible) at the end which stores the users second selected item to write to the db. This all works fine until you start navigating through records.

If I look at a record I've set already the first combobox is correct but the second defaults to the default option for that list. It will not allow the vaules to be changed. As i navigate through my db I am changing all the second combobox items to their defaults.



I've been messing around with some succes. Firstly I removed the call to the function that generates the values in Form_OnCurrent. This solved some issues.

Problem now is when the user is on a record and tries to change the second combobox the list is empty.

If someone has some time I can PM the DB. I think I've exhausted all my abilities and I'm pointless playing with code frustrating myself.

Any help much appreciated

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[maxmaggot]

Attachment 12885


Plese see the attached. It is the combos on the expense page that are the issues as described previously.

[maxmaggot]

So I thought about this further and using list values in combo boxes seems to be a big no no. So I created to new tables Expense Type and Expense Sub Type. I populated and linked these to the Expense table.

I think I need to create a query for the after update event for each of the combo boxes on the Expense form but I'm unsure if I'm taking the right approach or not.

I've attached my work.
Any ideas/suggestion are most welcome

Thank you

[maxmaggot]

I found this MS tutorial which seems to almost have me there.
The tutorial is located here

http://office.microsoft.com/en-us/ac...001173058.aspx

My situation is slightly different so I had to adapt the tutorial as I went along.

I've set up two new tables Expense Types and Expense Sub Types. They hold the values that were in the value lists in the previous uploads. I've linked them to fields in the Expenses table.

My issue now is that it asks me for the expense subtype primary key as a parameter when I switch between records.

Please see attached and again any help is much appreciated. (Some totalling fields are flashing as I haven't updated their source yet)

[maxmaggot]

Changed relationship type for expense type and expense sub type and when I run the expenses form it works pretty well.

However if I run the navigation form and go to the expenses tab it seems to have the same problem as before.

Please see attached.

If anyone can have a look I'd most appreciate it. I feel that MS have not thought Navigation buttons through fully. Referencing seems to be a massive issue with them. Or maybe I'm just becoming bitter.

[maxmaggot]

Over 60 views, no suggestions. Is it a very complicated situation or am I updating my solutions too often?

[June7]

I use list values in combobox only when I know the list will Never Never (I know, Famous Last Words) change.

Unless you want to allow the subtype to be empty, don't need to save expense type in Expenses. Just save the expense subtype ID. Certainly don't need to save expense name to Expenses.

This RowSource for the cboExpenseSubtype works:
SELECT ID, [Expense Sub Type] FROM [Expense Sub Type] WHERE ExpenseType=[cboExpenseType] ORDER BY [Expense Sub Type];

[maxmaggot]

Thanks so much... Can't believe you solved it in 5 mins. This was torturing me.. Shouldn't have stored values in a list in the first place. Painful lesson.

Anyway, thanks for your help June7. It's working now.

Just to note, June7, The system tells me I'm not allowed to add any more to your reputation until I spread the wealth a bit.... Goes to show how much you have helped me. Thanks again, it is much appreciated.