Link to home
Start Free TrialLog in
Avatar of bubearjr
bubearjrFlag for United States of America

asked on

Cascading combo box clean up during edit

Attached is an Access database example that I am working on.

When I open the Daily Work Scheduling form I can change to different records and cboTask is empty however there is a record for cboTask in tblProjects. What's even weirder is that cboTaskID remains populated.

What I am having trouble with is having the values stick per record I review on Daily Work Editing form based off the table AND when I choose a task from the drop down it either automatically clears cboTaskID or autopopulates it (since the query it runs only turns up 1 code each time anyway). Whichever is easier.  Thanks for your review guys!
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

What is it exactly that you want to do?  We need to know how you expect (or want) this form to work in order to fix it.
The form seems to be working OK, as far as I can see.  However, I would strongly recommend changing the Custom textbox to a combo box for selecting customers from a table.
Avatar of bubearjr


I want to be able to create new records with daily work schedule and when i choose a work center, task and task code it cascades (which it does) but when i go to reselect a work center task clears and when i choose a new task task code clears.

When I review the jobs in daily work editing and pull up a record it shows the info from the table in those combo boxes as well. Then when I change any cascaded combo box a level above another it clears down for instance

choose work center - Clears task and task code
Choose task - clears task code
Our Customer field isn't needed but more of a guide for our workers. We use the project number to reference data in dynamics AX. Customer is just throw away data.
Please post the explicit steps to see this issue in the DB you posted.

Then post exactly what you want done and why.
Open Daily work Scheduling form

Fill in all combo boxes

Then change your work center combo box again and watch it clear out task but not task code

Change task and it does not clear task code

I need task code to always clear in the event of a change in work center or task so i do not get incorrect task code entries into my projects table.


If i open daily work editing form and scroll between records task is not always populated regardless of entry. I need it to show the info from the table and be able to change it in the same manner as i could with my daily work scheduling table and update the associated records.

I'm not sure how to lay it out more plainly than asking a question by laying out my exact problem, stating step by step how to duplicate it and then telling them my how I was trying to make it function. If this is the incorrect manner in which to post please enlighten me.
I am looking at this now...
Thanks very much boag2000 you're help is very appreciated

Did you post the correct sample DB?
The one you posted does not have a " Daily work Scheduling form"

It only has a: "frmEmployeeDetails" form
(And that does not have any comboboxes)

Here is is again I thought I had.
IT should be under unassigned objects.
Still confused, ...this appears to be the same DB as before.

I see nothing named:
"Daily work Scheduling"
"unassigned objects"

Please verify the file before uploading.
Downloaded the db I opened and it appeared.

 User generated image

Fair enough!

My resolution on my laptop is such that I could not see the "Unassigned Objects" on the screen without scrolling.

I have now changed my view to: Object Type/All Access Objects

Thanks for the clarification,
Sorry for the misunderstanding...

Looking at this now...

no problem thanks for looking!
The Work center combobox (AfterUpdate Event) triggers a requery of the Task combobox.

This implicitly will "Change" the value in the task combobox (a requery will reload all the records in the Rowsource)

What this means is that the "Change" event of the task combobox fires.
Note that you also have code on the Change Event.
This means that this code fires, which does another requery of the combobox again.

So first delete the code on the change event of the task combobox.  I don't see a need for it.

Now on to your table design....

You have a table named Tasks and you have a table named TaskID.

This is very confusing...

The Task table should look like this:
TaskID (PK)

In effect, the task table should list everything have to do directly with the task.

Now any table that is a "Child" of the Task table might look like this:
TaskDetailID (PK)
TaskID (FK)

(One task can have many details in this example...)

These two tables should be related in the database window and referential integrity should be enforced.

Then this should all work normally...

I am confused as to your design as it stands now with regard to these two related tables.
(Tasks and TaskID)
The reason it is structured that way is because it generates a specific taskid based off what work center and task you choose and I cannot create a relationship between taskid and task if they are in the same table.

You are suggesting I merge the tables, change my pk and create another table I really don't have use for.

I have removed the event code you suggested.
"it generates a specific taskid based off what work center and task you choose"
I can't say that I understand what this means in the context of your design.

In the most basic sense the task table will contain the fields I listed.
I really don't see why a separate TaskID "Table" exists?
Can you explain why this design chosen?
Are you sure it is Optimal?
Are you sure it is correct?

What is the relationship between the Task table and the TaskID table (One to many, One to one, Many to many?)

IF i have task and taskid in the same table i don't know how to make the cascading combo boxes function properly.
The only way I could get the cascading to work was to have each bit of data in different tables.

I doubt it is optimal and I'm not sure about it being correct or not rather than just a matter of design preference.

This database will not be fore heavy use. I just need the functionality to work I described and I have little to no vba experience and this has been the closest I've gotten.

I guess I return to my base question which is how do I requery and clear each combo box when a new task or work center is chosen in the combo box?
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Awesome, that worked great. Now the second part of my original question was whenever i go to view the records the first and last records i view using daily work editing form show records with work center, task and taskid populated however as you cycle through records you see that it doesn't show the task data in the records. Any info on this? You've been awesome and I really appreciate the help.
I am afraid I have exhausted all the resources I can muster just to get to this point.

I suggest you first post a question explaing your goal and ask about your Task/TaskID relationships...

You really need to get that straightened out first.

partial points awarded for half answer
answered half of my question.
I'm cool with that
As an update for everyone:

 I found simply adding a requery for each combo box on the on current event fixed the last part of my question

Thanks for everyones assistance.

But remember that the current event fires each time you move through the records.

As your db gets bigger, this constant requerying will start to slow down your DB.

In other words you should only requery when absolutely necessary.
thanks for the additional information