• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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!
Timesheet-v2.accdb
0
bubearjr
Asked:
bubearjr
  • 17
  • 11
  • 2
1 Solution
 
Helen FeddemaCommented:
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.
0
 
Helen FeddemaCommented:
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.
0
 
bubearjrAuthor Commented:
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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
bubearjrAuthor Commented:
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.
0
 
bubearjrAuthor Commented:
Anyone?
0
 
Jeffrey CoachmanMIS LiasonCommented:
Please post the explicit steps to see this issue in the DB you posted.

Then post exactly what you want done and why.
0
 
bubearjrAuthor Commented:
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.


ALSO


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.

0
 
bubearjrAuthor Commented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
I am looking at this now...
0
 
bubearjrAuthor Commented:
Thanks very much boag2000 you're help is very appreciated
0
 
Jeffrey CoachmanMIS LiasonCommented:
bubearjr,

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)




untitled.JPG
0
 
bubearjrAuthor Commented:
Here is is again I thought I had.
Timesheet-v2.accdb
0
 
bubearjrAuthor Commented:
IT should be under unassigned objects.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Still confused, ...this appears to be the same DB as before.

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

Please verify the file before uploading.
0
 
bubearjrAuthor Commented:
Downloaded the db I opened and it appeared.

 db screenshot
0
 
Jeffrey CoachmanMIS LiasonCommented:
LOL!

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

Jeff
0
 
bubearjrAuthor Commented:
no problem thanks for looking!
0
 
Jeffrey CoachmanMIS LiasonCommented:
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)
TaskName
...etc

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:
TaskDetailsTable:
TaskDetailID (PK)
TaskID (FK)
TaskDetailName
...etc

(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)
0
 
bubearjrAuthor Commented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
"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?)

0
 
bubearjrAuthor Commented:
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?
0
 
Jeffrey CoachmanMIS LiasonCommented:
You can have cascading comboboxes function just fine with one table or two related tables.

Typically it is done with two related tables, because this is the more "normalized" structure".
In your case I still hazy on how your tried to Normalize your one table into two related tables...

<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?>
If this is all you need then do something like this:

Sub AfterUpdate Combo1()
   me.combo2=""
   me.combo3=""
   me.combo2.requery
   me.combo3.requery
end sub

This will do what you asked *If* your tables are normalized properly...
0
 
bubearjrAuthor Commented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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.

JeffCoachman
0
 
bubearjrAuthor Commented:
partial points awarded for half answer
0
 
bubearjrAuthor Commented:
answered half of my question.
0
 
Jeffrey CoachmanMIS LiasonCommented:
I'm cool with that
0
 
bubearjrAuthor Commented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK,

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.
0
 
bubearjrAuthor Commented:
thanks for the additional information
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 17
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now