Creating multiple records from one form

[rainman89]

So what are you going to do?

dont know. i have other things to do today so ill have to think about it. talk to the bosses about what they want done...

ill definately keep ya updated though

 

[ajetrumpet]

talk to the bosses about what they want done...

ill definately keep ya updated though

You know what I would say!! So, don't say that!!

 

[CraigDolphin]

Adam, I never actually downloaded the db (I'm on dialup). I just looked at the relationship diagram and read the description of the records he was wanting to add and applied the rules of normalization to what I was seeing.

Each of the (<=) 5 records Ray was wanting to add per expense had identical values in every field except the CaseID field. One of the rules of normalization is that you should not have repeating information within a table. This design has repeating information for all the fields bar one.

So it wasn't that the data was messed up irretrievably....just that there was a one to many relationship that had not been recognized in the original design: i.e., one expense can have many cases associated with it.

Any time you have a one to many relationship, you need two tables to handle it properly. If you force all that information into one table, then you either end up either forcing the 'many' part into a single cell (like listing more than one name in a cell in excel) or else forcing the user/code to enter in the 'one' side many times (probably why the users were unhappy about doing the necessary data entry to make that work). The latter is what you are doing via code.

Thankfully, the information is all there, and the design can be fixed without loosing the data. Fixing the design would allow the form/subform solution to his immediate problem without the need for a lot of coding, and improve the file size of the db, and proof him against some of potential violations of the assumptions he's using to scope his problem.

I think that, with a few exceptions, when you have to spend a lot of time and effort 'forcing' access to do something it isn't designed to do, chances are pretty good you're attempting to overcome a design flaw in the relationships. Not that this is always the case but, this time it is.

So using code treats the symptoms, but fixing the relationships cures the disease. Quite honestly, it shouldn't take that long to fix the table structure. I would have thought it doable in less than an hour (unless I'm missing something?)

 

[ajetrumpet]

Very good point Craig, and maybe Ray should take your advice over mine??

I certainly appreciate your thoughtfulness. I used to work for a company that had all their IT work outsourced to India, and everytime people called them, they would say; "We will have it patched tonight". You have no idea how much "patchwork" there is out there (or maybe you do!). Welcome to another example of it. Do you think I was wrong??

Regardless though, it sure was fun!

 

[CraigDolphin]

Do I think you were wrong...about what? I have confidence that the code you wrote does the job admirably.

If I were in Ray's shoes I'd fix the underlying problem and study the code to see how you achieved the workaround in case I can learn something from it. I try to treat difficulties like this as learning opportunities.

I also would have just fixed the db before talking to the bosses....I'm of the opinion that the surest way to make the wrong technical decision is let managers get involved As my boss tells me: it's a lot easier to ask forgiveness than it is to get permission

 

[rainman89]

ok craig,
ive gone and done it!!!

i think ive got it figured out finally. ill update. thanks again for the help

 

[rainman89]

ok so i did it and all my data got messed up. im starting over. it was assigning weird cases to entries that should have had different cases in them. not sure why. i followed the instructions verbatim...... oh well.

ill have to try again.

 

[CraigDolphin]

How big is the file Ray? I could have a go at it if I can DL from work (in AC2000 format).

 

[rainman89]

i did it another way where i copied and pasted the 2 columns into the ExpenseCases talbe and it "SEEMS" to be working now... ill have to look at a few more queries to be sure..

btw is the site pissing you off today? i cant even see new posts

 

[ajetrumpet]

I feel so NOT "IN" right now...

 

[rainman89]

I feel so NOT "IN" right now...

haha... if it wasnt for you keeping this thread alive for me we never would have gotten to this point in the first place.

PLUS you put me in the right direction with recordsets so.. THANKS

 

[CraigDolphin]

Just don't discard your backup til you're sure all is well

btw is the site pissing you off today?

And yeah...the site is playing up today. But 'tis better to be pissed off than pissed on!

Adam, you sound like you need a chocolate fish

 

[rainman89]

Just don't discard your backup til you're sure all is well

And yeah...the site is playing up today. But 'tis better to be pissed off than pissed on!

Adam, you sound like you need a chocolate fish

OH I WILL NOT DO THAT!

im having trouble getting it to save though. its taking the entries and the expense id is filling in, but its only saving the first entry... thinking it has something to do with my save buttons. im running out of time today, but im moving forward. i should have more time tomorrow. thanks.

 

[ajetrumpet]

Adam, you sound like you need a chocolate fish

What a gift...I'll be expecting that in the mail Craig. Thanks!

 

[CraigDolphin]

The main form should be bound to the Expenses table. The subform should be bound to the ExpensesCodes junction table (make sure the data entry property is set to no for the subform) and be in continuous form or datasheet view. The ExpensesID field should be the Master/child field between the form and subform.

You shouldn't need a save button to save the codes once you've added them to the subform.

I'll be expecting that in the mail Craig.

Along with the cheque, right?

 

[ajetrumpet]

Along with the cheque, right?

Actually, it's called "check" where I come from.

And yes, I should be getting one of those, after all the custom modules I've written for people on this friggin' site.

 

[CraigDolphin]

I know the feeling...I'm still hoping our friend Colin will send me one of those nifty Prius hybrids someday

Incidentally....'New Posts' seems to be working again

 

[rainman89]

ok craig. its going good so far, if i wanted to divide the expenseamount by the total number of combos chosen in the subform, how would i go about doing that? i thouhgt maybe getting a recordcount from the subform, but im not sure if i am going at it the correct way.
ive tried

Private Sub saveRecord_Click()
On Error GoTo Err_saveRecord_Click
Dim i As Integer
                  Dim rs As Recordset
                Set rs = Me.RecordsetClone
                i = rs.RecordCount()
                
                Me.ExpenseAmount = txtExpenseAmount / i

like ive said before, im not good with recordsets, so be gentle if this is totally wrong.

ive placed this in the save button click, because i needed it in an event to put it in for testing, ill eventually move it to the correct event once i get it working.
thanks

 

[ajetrumpet]

                Me.ExpenseAmount = txtExpenseAmount / i

like ive said before, im not good with recordsets, so be gentle if this is totally wrong.

YOU'VE GOT IT WRONG!!!

Mind if I chime in here?

 

[rainman89]

hell no i dont!!!!!!!


knew it had to be wrong cuz it wasnt working!