Creating multiple records from one form

[rainman89]

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.

Eureka i do understand! but rarely do we do a total expense amount, we are more interested in how much goes to what case,, etc.... i do however see your point!~!

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

try maybe the eye of a pinhead...... ~~~~!!!!!

Going about this would require a change in the way that we have set up the other form correct? instead of having an expense amount in the expenseform, i would have to have it in the expensecaseform? so i could still have a text box on the main form that they entered the expenseamount into, but also have one in the expensecasesubform that drew the data from the value in that form and did the calculations on it before saving.. (if i dont write this down ill forget come monday)

then in the timesheet form if i did it the exact same way and also stored the starttime endtime, i could make it still look like the starttime and endtime are being used, but infact use only the expense minutes field for my calculations....

i wish i had brought this topic up before instead of now, after over 5000 records have already been created.....

 

[CraigDolphin]

Yep...the minutes control and the cost controls would be in your subform. The start and end times would still be on you main form.

My suggestion would be to off the user a command button that would prompt them for an amount to divide up evenly between the records, then run some sql that automatically populate the values with the divided amount. Otherwise, they can input the different amount individually.

Now, when it comes to the time calculations, your only issue is to check that the sum of the minutes matches the interval indicated by the start and end time.

For example, if starttime is 8 am and end time is 9am the interval is 60 minutes. If the employee worked on 2 cases and enters minutes for each as 25 and 30 respectively then there's a mismatch. How do you want to handle this? My suggestion would be to prompt and force the user to fix the problem, but you could alter the last value entered to ensure that the sum is correct.

Or, if you don't care about that, then don't worry about that test.

I'll have a look at it a bit more this afternoon and see if I can whip up an example db.

i wish i had brought this topic up before instead of now, after over 5000 records have already been created.....

I sympathize. It's getting the data reorganized correctly that's a pain once you're already up and running. The process is going to take some thinking through depending on what the cost value field in your Expense table is actually storing (is it the total or is it an average) and whether you have multiple records in your Expenses table to handle multiple time intervals associated with your original code field.

 

[rainman89]

I have some other stuff to do today before the weekend. i appreciate EVERYTHING i really do.....



adam you too. i know u are keeping an eye on everything

 

[CraigDolphin]

adam you too. i know u are keeping an eye on everything

Spooky!

 

[CraigDolphin]

Okay Ray...

have a look at the attached (frmMultipleExpense)

Note that even though I've left the ExpenseAmount field in the Expenses table, it is not referenced on the form at all now. The total appears on the form but is generated on the fly in the forms record source.

There's probably many other things to modify/fix on your form but I don't know much about the real world constraints on what or how you're storing data. Or even just your preferences for grouping certain items.

So, this form-subform pair allows you to enter costs and times by case within expense. I've written some code to automate population of the costs/times when all cases are weighted equally. I've also written some validation code so that if a user manually modifies the times created by the auto button, then the db will check to be sure it is consistent with the time range specified.

Note, I have not performed this validation check when the user closes the form or goes to a new expense record. So it is possible to have the calculated time interval be different than the sum of minutes in the ExpenseCase table for that ExpenseID. You may or may not want to enforce this internal-data consistency.

However, I have enforced that a value must be recorded in each field of the subform before closing the form or moving to a new record.

EDIT: I have re-uploaded the db Ray. I had forgotten to update the code behind the delete button for the subform records. Have fixed that now.

 

[ajetrumpet]

adam you too. i know u are keeping an eye on everything

Am I supposed to respond to that Ray?

Or am I supposed to respond like this?

 

[rainman89]

ok so ive thought about it and i think its best to have 2 forms for the user to enter their expense and times.

ive created the form that i wanted before, but im still hitting a snag. i cant get it to divide the time on the subform, and i only have the ability to add 2 cases to the subform before it freezes.... i mean wtf

again. any and all help is appreciated

 

[CraigDolphin]

Ray, the example I posted before functioned perfectly well (AFAIK) and had all the code you needed. Why have you gone back to the original forms and all their attendant problems?

I have no idea why you MUST have two separate form/subform pairs to enter data into the same two tables, but it strikes me as a lot of make-work. It would be vastly simpler to simply hide or show certain controls on the same form as dictated by the situation.

In any case, since you've decided on a divergent path I wish you well on your journey.

 

[rainman89]

well it does work very well, it required each entry to have an expense type/amount etc associated with it. i could not just enter a users time and task for a certain period. i tried changing it so that it would allow me to do these changes, ie adding task to the employee times and others, it still required that i enter an expensetype, payment method, etc which i dont need if its a users time.

hope you understand that i dont dislike the sample you gave me and i do appreciate all of your help since you are the one that got me thinking on the right path. its just that as far as the "real world" workings of this db, that solution doesn't quite meet what i need.

 

[CraigDolphin]

i could not just enter a users time and task for a certain period.

Absolutely you could. Just alter the validation code to check only the fields in the table that apply based on the same logic as you used to make the controls viewable or not.
If you look at the table, the fields are not marked as 'required'.

 

[CraigDolphin]

Have a look at the attached Ray. One form, using openarg to set control visibility properties and to determine which fields to validate.

But, to be honest I think you have other fish to fry here. Even if you go with a two form approach you have an issue to resolve. You have it setup so that the forms are in dataentry mode (ie moves to a new record on opening). Now, imagine your employee opens the from to enter timesheet info. They then close the form and open up a form to enter expense information. It opens to a new record and they have to re-enter the same basic information that they did before. So you now have two records with the exact same information in your expense table, and you have a set of related records in the junction table with only the timesheet information stored, and another set with only the costs stored, and no way to link the two expenses.

It makes NO sense to have the timesheet and cost information entered at different times. If this is unavoidable, you need to set it up so that the user can find the expense record that they have already entered when they go to add the second lot of information.

Either that, or you are incorrectly linking timesheet information with Expenses in the first place.

 

[rainman89]

Absolutely you could. Just alter the validation code to check only the fields in the table that apply based on the same logic as you used to make the controls viewable or not.
If you look at the table, the fields are not marked as 'required'.

i was recieving an error stating it could ont find a related key in table expensetypeid. i thought because they were all tied to that same query that that was the reason for the problems.

 

[rainman89]

well i considered timesheet entries as expenseentries, with just different criteria.

im going home now. im sick and the boss doesnt want anyone else getting what i got. ill take a look tomorrow at the sample you gave me. thanks again craig!