Creating multiple records from one form

[ajetrumpet]

OK Rainman, are you ready for the disection of this???

 

[rainman89]

yeap go ahead

 

[ajetrumpet]

Ray,

I'm not done with this, but check it out anyway.

I will offer you more of an explanation when I get back. I have to go out for a while.

The main point to make is that ME.DIRTY can only be satisfied if your form has controlsources set on it. Yours does, and that is the reason you are getting a duplicate record in the expense table!

 

[CraigDolphin]

Well, sorry to come in at this late stage and maybe point out something obvious but, isn't this a normalization issue?

From what I've read, it seems like you have repeating information within this table. Everything about the (up to) 5 records is identical except the caseID from your combo, no?

To me it's a classic many-to-many situation between expenses and cases.
I would imagine you need a a junction table between your Cases table and your Expenses table.

Then, all you need is a subform bound to the junction table in your form, instead of just a combo. The subform would be in continuous form view and use the Expense tables PK as the master/child key.

Or am I missing something?

 

[rainman89]

Well, sorry to come in at this late stage and maybe point out something obvious but, isn't this a normalization issue?

From what I've read, it seems like you have repeating information within this table. Everything about the (up to) 5 records is identical except the caseID from your combo, no?

To me it's a classic many-to-many situation between expenses and cases.
I would imagine you need a a junction table between your Cases table and your Expenses table.

Then, all you need is a subform bound to the junction table in your form, instead of just a combo. The subform would be in continuous form view and use the Expense tables PK as the master/child key.

Or am I missing something?

No you seem to have it all. i didn't think of a junction table as a solution. ill have to think about this one alittle bit. thanks craig!

 

[CraigDolphin]

Happy to help. Sometimes it's easy to lose sight of the woods for all the damned trees in the way

And this approach also saves you future grief if ever someone wants to put six cases on an expense...or seven...

 

[rainman89]

Happy to help. Sometimes it's easy to lose sight of the woods for all the damned trees in the way

And this approach also saves you future grief if ever someone wants to put six cases on an expense...or seven...

if i went this route, how would i update the already existing records?

 

[ajetrumpet]

Ray,

I don't know what Craig is talking about, but here is your initial problem, fixed. The code for the close button is completely different.

Craig might be right though, and restructuring to fit your needs may be in your future.

If you need an explanation of what I did, just let me know. Bottom line - The control sources that you had on your form were the cause of the duplicate record. There is no need for them if you already have the append code written.

Make sure you understand what the "Dirty" property means too.

 

[CraigDolphin]

Ray,

if you decide to normalize your data then you'd need to do a few steps along the way.

First thing...make a backup of your db in case you need to start over.

Second thing. Make a copy of your expenses table. Let's name the copy tbl_Temp_Expenses. Delete all data from tbl_Temp_Expenses, and delete the CaseId field too. I assume that your pk for the tbl_Temp_Expenses table will be called ExpensesID (autonumber). Once all the data is gone you might want to compact your db to reset the autonumber kernel.

Third thing. Make a query of your original Expenses table. Include all fields in this query except the pk and the CaseID fields. Use totals and group by for every field. Convert this to an append table query and use this to populate tbl_Temp_Expenses.

Fourth step. Make a new, empty table to serve as the junction table.
ExpenseCases
ExpenseCaseID (auto,pk)
ExpensesID (long)
CaseID (long)

Fifth step. Make a new query. Add both the original Expenses table and the tbl_Temp_Expenses to the query and join every field that the two tables have in common (except the pk). Then add the pk field from tbl_Temp_Expenses, and the CaseId field, from your original expenses table, to the query grid. Have a look at the query. It should have two fields: ExpensesID and CaseID. There ought to be the same number of records in this junction table as there were in your original expenses table.
Convert the query into an append query and populate your new junction table.

Sixth step. Delete your original Expenses table (only if you made the backup copy first though!). Rename tbl_Temp_Expenses to Expenses. Add this and the junction table to your relationships view and make the necessary joins.

Make sure that any forms or queries that were bound to Expenses are still working. Rebind them to the new table if necessary.

Lastly, Add your subform to your main form and ask your boss for a raise! If you mess something up, start over with your backup db.

It's a little bit of work to reorganize the data, but there's a lot to be said for getting the data normalized and, as I alluded to before, when the first employee needs to add 6 cases to an expense there's no problem, no code to rewrite etc. Also, if ever you get a situation where the employee wants to do something like 'Half of this expense goes again Case1, and a quarter each against cases 2 and 3', it's a much easier matter to add a weighting field to your junction table and subform than to add it to your expenses field and then have to rewrite your code again.

Or, if Adam has got the code working you can just use that. No skin off my nose! Just don't mutter out-loud on the forum when the 6-case expense turns up 5 months from now

 

[ajetrumpet]

Just don't mutter out-loud on the forum when the 6-case expense turns up 5 months from now

Good call!

Or, if Adam has got the code working you can just use that. No skin off my nose!

I like that idea!

I'm wondering something else too Craig...how messed up was the data? I've looked at the file only a few times. I guess I treated like any other 100MB .mdb file I open; just fix the problem. I saw a DB file the other day that was like 5MB in size, but it had over 30 objects in it!! When I see that kind of a thing on opening it up, I don't think I wanna spend my entire day re-writing the script. I praise your efforts.

 

[rainman89]

Just don't mutter out-loud on the forum when the 6-case expense turns up 5 months from now

Deal! appreciate all the help and im seriously considering the redesign.

Good call!I like that idea!

I'm wondering something else too Craig...how messed up was the data? I've looked at the file only a few times. I guess I treated like any other 100MB .mdb file I open; just fix the problem. I saw a DB file the other day that was like 5MB in size, but it had over 30 objects in it!! When I see that kind of a thing on opening it up, I don't think I wanna spend my entire day re-writing the script. I praise your efforts.

i dont believe that the data is messed up at all. i realize now that there is a slight normalization problem, but this expense table is tied to not only the expenseform but the timesheet form too. seeing as time is an expense. the sample i posted has only one feature of the many that i put into this thing, thats why its so small. but again appreciate the help~!!!

 

[ajetrumpet]

Did you get the problem fixed, then? Is the problem at least out of your hair now!?

 

[rainman89]

Ray,

I don't know what Craig is talking about, but here is your initial problem, fixed. The code for the close button is completely different.

Craig might be right though, and restructuring to fit your needs may be in your future.

If you need an explanation of what I did, just let me know. Bottom line - The control sources that you had on your form were the cause of the duplicate record. There is no need for them if you already have the append code written.

I see what you did, question is, if i were to go back and want to review the entries, how would i do that seeing as no fields are bound? guess this is something id have to look into. seeing as printing the sheets is based on what one is shown on the form.

Make sure you understand what the "Dirty" property means too.

Thought dirty meant that any change to the form or control would be considered dirtying it??

 

[ajetrumpet]

I see what you did, question is, if i were to go back and want to review the entries, how would i do that seeing as no fields are bound? guess this is something id have to look into. seeing as printing the sheets is based on what one is shown on the form.

I didn't know this. Tell me more about it...Review entries? What does the review of entries have to do with bound fields?

And no, Me.Dirty refers to "touching" or modifying data/controls that actually correspond(s) to a source. Example - typing a word in an unbound box, then deleting it will not result in a Me.Dirty = True scenario.

 

[rainman89]

well what i mean by review is after i enter them, and i want to go back and see what i have entered. may it be a single record or whatever. then no records will show because of the fact that the records need to be bound to the fields in the table to show them.

After someone enters their expense, they must print it out. if they dont know what one they are printing, i can forsee some problems arising

 

[ajetrumpet]

well what i mean by review is after i enter them, and i want to go back and see what i have entered. may it be a single record or whatever. then no records will show because of the fact that the records need to be bound to the fields in the table to show them.

Who told you that!? That isn't true...

You can't print a report from a form, so why on earth would you want to view it there anyway?

YES, the data needs to be bound to the fields in the table to show them on a form, but again, if a report is being printed out, it has nothing to do with a form! Why can't you put a button somewhere on the form that says..."Print my expenses", and ONCLICK...

DoCmd.OpenReport "ReportBasedOffOfTheTableOrQueryWhichNowHoldsTheAppendedRecords"

The report, obviously, will have to be setup first, but it can sit in the DB window until you need it; there's no problem with that.

 

[rainman89]

well i have that currently, but its based on the expense ID that was on the form. that way only one was printed at a time. i had it do all but if someone enters one and comes back later to enter another. they will print some duplicates, and i like to save paper!!!!

 

[ajetrumpet]

well i have that currently, but its based on the expense ID that was on the form. that way only one was printed at a time. i had it do all but if someone enters one and comes back later to enter another. they will print some duplicates, and i like to save paper!!!!

Print duplicates?

I guess I don't get it man.

It sounds like after this stuff is printed, it (the data) is being discarded somehow.

Why don't you just create a temporary table to house all of the expense entries for the day (or whatever period you want), then when they're ready for print, append all the records to whatever permanent table you want, and then delete them from the temp table...kind of like my multiple-record entry DB.

 

[rainman89]

no nothing gets discarded. it gets approved (yes/no) entry in my field.

 

[ajetrumpet]

So what are you going to do?