Solved

Cascading combo box clean up during edit

Posted on 2011-02-11
31
352 Views
Last Modified: 2012-08-13
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
Comment
Question by:bubearjr
  • 17
  • 11
  • 2
31 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34872328
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34872359
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34872415
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34872428
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34874569
Anyone?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34899370
Please post the explicit steps to see this issue in the DB you posted.

Then post exactly what you want done and why.
0
 
LVL 1

Author Comment

by:bubearjr
ID: 34900245
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34906791
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34906852
I am looking at this now...
0
 
LVL 1

Author Comment

by:bubearjr
ID: 34906890
Thanks very much boag2000 you're help is very appreciated
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34906938
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34906973
Here is is again I thought I had.
Timesheet-v2.accdb
0
 
LVL 1

Author Comment

by:bubearjr
ID: 34906979
IT should be under unassigned objects.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34909359
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34910073
Downloaded the db I opened and it appeared.

 db screenshot
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34910805
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34910876
no problem thanks for looking!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34911160
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34918255
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34918420
"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
 
LVL 1

Author Comment

by:bubearjr
ID: 34919473
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 34921023
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34921238
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34921605
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34921650
partial points awarded for half answer
0
 
LVL 1

Author Closing Comment

by:bubearjr
ID: 34921666
answered half of my question.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34921843
I'm cool with that
0
 
LVL 1

Author Comment

by:bubearjr
ID: 34926699
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34926884
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
 
LVL 1

Author Comment

by:bubearjr
ID: 34927629
thanks for the additional information
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now