Creating multiple records from one form

[rainman89]

I've fallen behind now Ray, I can't get back in. I'll just wait until you two guys screw it up again.

Although, Craig IS competent, so I might not get my wish!

haha but u wanted back in!!

 

[ajetrumpet]

Yeah, well I don't have the time to disect all the BS you have no put together. I created a workaround for you, but that obviously wasn't good enough.

There is a rule that I follow, and that says:

If managers are the cause of an initial screw-up of design, don't "workaround" the screw-up until you can't untie yourself from your own chains. Fix it completely right now, and pi** them off right now rather than later.

 

[rainman89]

If managers are the cause of an initial screw-up of design, don't "workaround" the screw-up until you can't untie yourself from your own chains. Fix it completely right now, and pi** them off right now rather than later.

Which i have done! and if craig didnt come along i would still be using the workaround

 

[ajetrumpet]

Well, I guess that puts me in my place, now doesn't it!?

 

[CraigDolphin]

Hmm. What on earth? Strange! Ray, I am unable to unable to open (even in designview) many of the forms in your db (except for the ones we've worked on previously, the switchboard, and frmTimesheetExpenseCase).

Is there anything about these forms that would cause this in Access 2000? Also, you might need to be specific about which forms you want me to look at and what it is that you are trying to achieve with that form (remember, I know nothing about the real world situation you're working with)

[Edit] I think some of the objects in the db you posted are corrupted. Any chance you could repost a non-corrupted version?

 

[rainman89]

ok let me try again. the form i am having trouble with is the frmtimesheet and its subforms.
notice when you open the form and select a case it is fine, except for the error given by the expenseID. but if you eneter information above before hand, it will not allow for me to change the caseid.

 

[CraigDolphin]

I'm sorry Ray....the same forms still will not open in any view. I think the problem is in frm_TimeSheet because when I browse the class modules I notice that the module for this form is completely blank....not even an 'Option Compare Database' line at the top.

I had a look back at one of your old files to see if I could find an uncorrupted version there but none of those forms were present in that file.

 

[rainman89]

I'm sorry Ray....the same forms still will not open in any view. I think the problem is in frm_TimeSheet because when I browse the class modules I notice that the module for this form is completely blank....not even an 'Option Compare Database' line at the top.

I had a look back at one of your old files to see if I could find an uncorrupted version there but none of those forms were present in that file.

If you're still reading Adam, could you DL the db and confirm whether you can open frm_TimeSheet?

i can open it before i zip it. ill try re-converting it to ac2000 for ya. maybe its something when that happened.

edit:i get errors when converting maybe thats the reason. trying again

 

[CraigDolphin]

Well, now I'm getting a 'unable to create the database window' error.

I think your db file is corrupted and this is really showing up during the conversion somehow. My suspicion is that it's something in frmTimesheet that's bad. You'll either need to fix that (I assume you're aware of the available methods?) or go back to an earlier version and redo the new forms that are corrupted. If it's any consolation, the forms we had worked on previously are still uncorrupted so you can import them into the uncorrupted version easily enough. (or you could just use the version I posted back to you a couple days ago)

 

[rainman89]

Well, now I'm getting a 'unable to create the database window' error.

I think your db file is corrupted and this is really showing up during the conversion somehow. My suspicion is that it's something in frmTimesheet that's bad. You'll either need to fix that (I assume you're aware of the available methods?) or go back to an earlier version and redo the new forms that are corrupted. If it's any consolation, the forms we had worked on previously are still uncorrupted so you can import them into the uncorrupted version easily enough. (or you could just use the version I posted back to you a couple days ago)

i have a few backups from before. ill post one of them up. sorry!!

 

[rainman89]

my main man Craig,
i created this in 2000 and imported everything. HOPEFULLY it will work this time

 

[CraigDolphin]

Phew...ok...seems to be ok this time Now...what the heck was I supposed to be doing again....(starts skipping back a few pages)

 

[rainman89]

Phew...ok...seems to be ok this time Now...what the heck was I supposed to be doing again....(starts skipping back a few pages)

see post 166!!!

 

[CraigDolphin]

Ray, there's a lot of compile errors on frmTimesheetSubform. Looks like you created the form by copying one of you old frmMultipleExpenseSubform where you still have cboCaseNumber etc and the code is having issues because it's looking for coontrols that no longer exist.

There's also
Private Sub ExpenseID_AfterUpdate()
Me.ExpenseCase.ExpenseID = Me.ExpenseID
End Sub

Which won't work because Me.ExpenseCase.ExpenseID is nonsensical.

I struggle to fix this because I don't actually know what you are trying to do with all this code, or with the form in general.

It looks to me like you're trying to duplicate the entry process for the exact same tables (with only some different fields in the main form), as entered into the previous form/subform that we were looking at.

Why don't you just add the extra fields to the other main form? Maybe on a tab control if you want to provide some kind of visual separation of the fields in that table?

And something elese to think about here. Can a single expense involve more than one employee? More than one time frame? If so...you know what I'm going to start talking about again don't you?

 

[rainman89]

Why don't you just add the extra fields to the other main form? Maybe on a tab control if you want to provide some kind of visual separation of the fields in that table?

And something elese to think about here. Can a single expense involve more than one employee? More than one time frame? If so...you know what I'm going to start talking about again don't you?

i copied and pasted part of the whole database in there. there is a lot of other stuff that is missing. tahts probably why there are a bunch of errors.

what i did want though, is say from timeframe 9:00am-11:00 am someone is traveling for say 4 cases. instead of having them enter 9-9:30 9:30 -10 10-10:30 11-11:30 they just enter 9:00-11:00 and pick the cases that travel for it will break it up for them

and yes occasionally more than one employee can be doing something the same time as another, but they have different jobs for each thing, so thats not really a concern atm

 

[CraigDolphin]

Dude...again we're coming back to this issue of normalization.

Are you suggesting you want to take a single expense, add multiple case id's to it, and then add multiple time frames to that expense (one for each case id)? If that's so, then you need to store the time information in the ExpenseCases table not the Expenses table. However, if the number of time intervals is not strictly one-to-one with the number of cases, but may still be multiple for an expense, then you need a new table to store that indepenent one-to-many relationship.

 

[rainman89]

well no i have a field called txtexpenseminutes that i would take the endtime-starttime and create that as minutes. not worry about the time intervals, just keep the minutes =30 mins therefore it would only be one expense. everyother one, assigned to a single case would be a time interval (starttime, endtime)

if this isnt right.... i guess ill have to do major changes again

 

[CraigDolphin]

Actually Ray I think we both missed something here.

The cost value you are storing in Expenses is actually the cost per CaseID for that Expense, not the cost of the Expense. Now it looks as if you're trying to do the same for minutes per caseID. It can be done (obviously) but the better question is whether it SHOULD be done?

I think the correct answer is no and here's why.

1. It violates the normalization concept that information in a field of a table depends solely on the pk of that table. (this information involves both expenseID and the number of cases associated with that expenseid)

2.It makes it more difficult to assemble the total actual cost for an expense, or the total amount of time spent on an expense by the employee. (Think about how you plan to do this...it's going to involve hardcoding some more math at some point to achieve those goals)

3. What if the weighting of cost or time is not evenly distributed across the caseID's?
Consider what happens if someone works on two cases for one expense but spends 5 minutes on the first and 3 hours on the second? The data entry person is going to need to be able to change the calculated result, and probably need to grab a calculator to figure out the right value to use. And then how will you be able to reconstruct it later? Even if you can figure out what they did, you won't know which of the two cases got the 5 minute interval.

Consider the alternative scheme.
ExpenseCases
ExpenseCaseID
ExpenseID
CaseID
ExpenseCaseCost
ExpenseCaseMinutes

Here, you simply add two fields to your expensecases table to hold the relevant information. You can write code to populate the values automatically using the similar approach as before, but users can edit and modify the data as necessary and it remains crystal clear as to what is going on. Better yet, you only need an ordinary select query to figure out the total time spent on an Expense.

You can still store the overall Time start and Time end in your Expenses table (and use that as inputs for the code that calculates the initial breakdown of time to populate the subform values), and you can also store the total amount for the expense in the Expenses table (to simplify the coding somewhat even though, strictly speaking, it is redundant information in this scheme).

 

[rainman89]

1. It violates the normalization concept that information in a field of a table depends solely on the pk of that table. (this information involves both expenseID and the number of cases associated with that expenseid)

Correct i understand this.

2.It makes it more difficult to assemble the total actual cost for an expense, or the total amount of time spent on an expense by the employee. (Think about how you plan to do this...it's going to involve hardcoding some more math at some point to achieve those goals)

This is where i dont get it. why cant the total for that one expenseID be used to find the total amount? wouldn't querying based on that expenseID and summing on the amount give me the total amount?

3. What if the weighting of cost or time is not evenly distributed across the caseID's?
Consider what happens if someone works on two cases for one expense but spends 5 minutes on the first and 3 hours on the second? The data entry person is going to need to be able to change the calculated result, and probably need to grab a calculator to figure out the right value to use. And then how will you be able to reconstruct it later? Even if you can figure out what they did, you won't know which of the two cases got the 5 minute interval.

Well if that was the case they wouldnt be putting that in the same entry, they would have to put in in a seperate entry (which im starting to realize will violate normalization i think?)

Consider the alternative scheme.
ExpenseCases
ExpenseCaseID
ExpenseID
CaseID
ExpenseCaseCost
ExpenseCaseMinutes

Here, you simply add two fields to your expensecases table to hold the relevant information. You can write code to populate the values automatically using the similar approach as before, but users can edit and modify the data as necessary and it remains crystal clear as to what is going on. Better yet, you only need an ordinary select query to figure out the total time spent on an Expense.

I thought that once it was entered into the table i could always go back and change it seperately if needed, you got me thinking otherwise. i understand now that if i changed it for one case, it would change for all cases... (i might be getting it, but dont hold your breath)

You can still store the overall Time start and Time end in your Expenses table (and use that as inputs for the code that calculates the initial breakdown of time to populate the subform values), and you can also store the total amount for the expense in the Expenses table (to simplify the coding somewhat even though, strictly speaking, it is redundant information in this scheme).

only reason i would store it is so when they print out an activity sheet, they will be able to account for all the time that they have spent during the day, with no gaps. this also makes sense to me because i was wondering how to show 9-11 and also break it up between cases.

 

[CraigDolphin]

This is where i dont get it. why cant the total for that one expenseID be used to find the total amount? wouldn't querying based on that expenseID and summing on the amount give me the total amount?

Think about it....one expense with two cases. Initial amount entered was $100....your code runs when the second case gets added and changes it to $50.

When you query that field for that expense you get the value $50. Now, unless you then multiply that value by the number of records in ExpenseCases with the same ExpenseID, you have the wrong answer.

The scheme I suggested, you could just sum ExpenseCodesCost where ExpenseID = whatever and you have the answer.

Or if you redundantly store the total in Expenses then you just look it up.

i understand now that if i changed it for one case, it would change for all cases... (i might be getting it, but dont hold your breath)

I think I see a tiny sliver of light starting to peek throught he clouds...