beginner needs help with tables & comboboxes - I think!

[Lac]

Hi all,
I'm hoping someone can help but I'm not even all that sure what my question is...

I need to create an Access97 database to track time spent on specific tasks. Each task is part of a general category and I need to be able to get total times for each category and sub-category (the tasks).

That would be simple enough even for me (three tables: categories, sub-categories, and time sheets) if it weren't for the fact that I need the sub-category drop-down list on the form to only display the tasks for the larger category chosen...

I've been reading the posts here and it looks like it might be a matter for cascading comboboxes, right? BUT I haven't found (or haven't understood) how to set up my "time sheet" table correctly so that it contains the date (easy), time spent (easy), and category and sub-category (this is where I'm stuck).

Can anyone point me in the right direction? What should I be trying to figure out how to do?

Thanks in advance!
Lac

 

[Mile-O]

Do you not need to store info on whose timesheet it is too?

 

[Lac]

No, single-user database... all the info will belong to only one person.

PS But if adding "user" info will make it easier to structure the whole thing and get it to work, I'm perfectly willing to do so...

 

[Mile-O]

WHat sort of plan do you currently have for your time sheet? i.e TimeStarted, Time Finished.

 

[Lac]

Time sheet:
date
numer of hours spent
category
sub-category (task)

Oh, and the final reports that I need only have to give:
the total number of hours (and days=8hrs) for each category and sub-category, since the beginning and between start and end dates (that I know how to do already).

 

[Mile-O]

This is all you need then. No need to store the category with the time sheet.

 

[Lac]

Thanks!

But... um... (warning: stupid questions coming) when I build the form to be filled out, how do I go about adding the drop-down list for categories that will filter (not sure that's the proper technical term) the contents of the drop-down list for sub-categories? Do I need to define a query (request? - my version of Access is in French) and use it as the source for the the form? Or will cascading comboboxes do that?
But I suppose I'll have to make a query/request anyway so I can pull up the form for only one day at a time (in continuous display mode)...
ARGH...
Every question answered raises more questions!

 

[Mile-O]

Firstly, you should make a query of all tables and sort them accordingly. The attachment below does this and fixes something from my previous attachment.

You'll need two forms - one a menu and one for data entry.

• Form 1: This will be a menu that will allow you to add, edit, delete time sheet entries, open the report you've mentioned, and close the database.
• Form 2: A data entry form (bound to qryTimeSheets) into which you type your data.

On form 2, you'll have two comboboxes cboCategory and cboSubcategory. Set the former's RowSource to qryCategories and it's ColumnCount to 2 and it's ColumnWidths to 0;2.

Create a new query called qryCascadedSubcategories and ensure that its SQL is:

SELECT SubcategoryID, Subcategory
FROM qrySubcategories
WHERE CategoryID = [Forms]![MyForm]![cboSubcategory]
ORDER BY Subcategory;

On the AfterUpdate event of the cboCategory combo put the following code:

Me.cboSubcategory.Enabled = Not IsNull(Me.cboCategory)
Me.cboSubcategory.Locked = IsNull(Me.cboCategory)
Me.cboSubcategory.Requery

Other things to consider - at table level - is setting the hours spent, date field, and subcategoryID field as being Required.

 

[Lac]

Making progress (thanks!) but...

I must be missing something because even though I *think* I followed your instructions, it isn't quite working...

I'm stuck on "Form 2" and the comboboxes... (Haven't tackled the menu form yet...)

The data entry form is set to display in continuous mode so I can see the "next" record while filling out the first...

I can enter date and time fine, and the 1st combobox perfectly displays the list of categories. However, when I select one, the category selected appears in the same field on the next record but no list of sub-categories appears in the second combobox...

Where should I be looking to figure out what I've done wrong?
(I did enter the sub-categories in the appropriate table... BUT, I modified the table from the file you sent so that the CategoryID field gives me a drop-down list automatically from the tblCategory... It works fine, but could this be causing the problem with the comboboxes?)

PS I also set hours spent, date field, and subcategoryID field as being Required at table level...

 

[Lac]

Well, I've gotten it to offer the proper choices in the sub-category box...
I set its source to qryCascadedSubcategories.
And changed the WHERE line of the SQL for that query to:
WHERE CategoryID = [Forms]![MyForm]![cboCategory]

But when I try to enter a second (or third, or 4th) time data record, if I try to select a different category, all the previous category fields in previous records automatically change to my selection... Same thing with the sub-category fields...
AND, the tblTimeSheets is not recording the sub-category ID... (I turned off required, but will turn it back on...)

Any ideas as to what I've done wrong now?

Thanks in advance...

 

[Lac]

Further progress...
I've been fiddling and futzing... and am not even completely sure what I've done but it now works (with one major glitch):

It lets me choose a Category, displays the appropriate list under Sub-Category... and even records the sub-category in tblTimeSheets as it should. However, when viewing the form in continuous mode, the category field in all (previously recorded) records changes to display the category selected in the record being recorded. The sub-category field in all records blanks out, except for those records whose sub-categories match the current category.

Data seems to be properly recorded and stored but this "display glitch" makes it very NON-user-friendly because you can't see what you've already entered.

Any ideas where I should be looking to fix this? (Or do you want to see what I've got so far first????)

 

[Mile-O]

Let's see what you have.

Although it seems that a continuous form is showing lots of different records, it only has one set of controls. Thus when you change the details in one field, the representations of the same field in different records will be affected. It's a display thing. Do you really need this as a continuous form? I'd have thought a single form would have been better/safer.

 

[Lac]

The zipped file appears to be too large to attach... even when I delete most of the data and all but the form in question... how should I go about getting it to you for you to see?

I do hope there is some way to get a continuous display going because there are usually multiple TimeSheets per day (at least one for each sub-category) and it is easier to fill in the records for the day when you can see the list of what has already been entered... If you see what I mean...

Any ideas would be welcome!

And, thank you ever so much for your help and patience!!

 

[Mile-O]

Tools -> Compact and Repair the database
Then .zip it.

 

[Lac]

Here you go...