Creating multiple records from one form

[rainman89]

Hi all,
I was wondering if anyone has come across making duplicate records with only one field changed. This field would be based on caseID.

My users are getting tired of splitting their expenses so they are just lumping the expense onto one case and hoping that someone(me) will realize what they did and change it to each case for them.

What i would like is to add combo boxes on the form and for each combo box that has a selection in it duplicate the record for each caseID. could probably be up to 5 different cases per expense.

I dont know how to do this for each selection, i was thinking if i did saverecord, create new record,save record. but wouldnt the new record remove what was in the txt boxes of the form?
I was thinking id do it in the beforeupdate of the form, and maybe put a if statement, or something in there but im at a loss as to how to do this the most effective way. anyone have any ideas for me?

 

[Simon_MT]

The users may still only use one combi box! This is about user discipline and some should cane them! Ooops that not particularly PC so forgive me!

 

[rainman89]

sadly i would like to be able to do that... but... i cant. this one came down from the boss!

 

[Rich]

How would you decide on the number of records you need to create?

 

[rainman89]

ideally id put 5 combo boxes, seeing as 5 is the maximum number of splits i have seen so far. so however many combo boxes are filled with a case, is how many i would need to create

 

[Rich]

I wouldn't put five combo's into one record, how about asking them first how many entries they want to make ?

 

[rainman89]

could do that. i could even use txtboxes.
i just need to know how to duplicate the record for that certain number of entries. changing only the case ID

 

[Rich]

Use a For I in conjunction with move next etc.

 

[rainman89]

record sets arent my strong point... was hoping to avoid them. but if i must..

could you point me in the right direction?

 

[Simon_MT]

I think this question has been posted on another forum and I would reiterate that no matter how you do this, if users can't be bothered to enter the all the relevant CASE_IDs no matter how you present the information you still have the problem getting the users to fill in multiple entries.

It does strike me that if users are just putting in only one CASE_ID what it this doing to the integrity of your database? Somehow it has to be impressed on the users the importance of entering the information correctly in the first place.

Simon

 

[rainman89]

What they are doing is entering the information under one case... so when it comes to pay time, and i go over our records with accounting. they are off so i have to go back through the db and figure out why they are off.

they usually enter it under one case then in the notes field i provide, they write split between case# blah, blah, blah. so then i have to go into the db and split it. sucks.
thats why i want to allow them to put more than one caseid so itll do it for me

 

[Simon_MT]

This is about education more than anything else. It would seem that your users want to do the least possible. I do have a great deal of sympathy with your predicament but goes correcting their errors yourself is not a solution. It is the responsibility of the users to correct their mistakes and this reinforces the need to be accurate.

Currently there is no cost to the users when entering their expenses incorrectly, what would happen if:

1) The users were made to correct there mistakes?
2) Suggest that the database is abandoned as the input accuracy is not good enough as the integrity of the database can not be maintained?

It is simply not your job to correct mistakes of ours, life is too short.

Please don't think I having a go at you or even suggesting that the database is not worthwhile. I'm in a fortunate situation where the MD goes ballistic if he sees something wrong. Whilst this does not eliminate mistakes it does make the users accountable.

Simon

 

[rainman89]

When they do it correctly it works like a charm. this database is also for our timesheets, with the expenses as a sort of add in. its been the hardest part of this database anyways. getting the forms to show correct info per user etc etc. I would say something about the users responsibility, but the claim is they do not want to spend the time to break it down.

I understand taht u are not taking a swing at me and i totally agree with u that i hsouldnt have to be the one to assure these are done correctly, but as the db admin, i am held utterly responsible for everyone elses mistakes. (always comes back to a db flaw, when in reality its not) and am tasked to fix them.
this change would take a load off my back by assuring that they had the option to enter multiple things at the same time.. thats why i would like this option..

if it cant be done then ill move on and make them do the entering. or advise that this function be removed totally from the db ( which i dont want to do since i spent a lot of time on it in the beginning)

 

[Rich]

Something like this
Dim db As DATABASE
Dim rst As Recordset
Dim Yr As String
Dim StartDate As Date
Dim I As Integer
Set db = CurrentDb
StartDate = Date
Set rst = db.OpenRecordset("Yrs")
rst.MoveFirst
For I = 0 To 50
rst.AddNew
rst!AccYr = DateAdd("yyyy", I + 1, StartDate)
rst.Update
rst.MoveNext

Next I


rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

MsgBox ("Done!")
End Function

But it is risky creating records that could end up with blank or redundant data

 

[rainman89]

hopefully it wouldnt be blank or redundant. since it would differ by case number-
this would duplicate each entry changing only the caseid?

 

[rainman89]

OK im gonna open this one back up seeing that the problem has come back up.

alittle more information on it.

There can be up to 5 different cases entered on one form.
each record would have the same information in each one except for the caseID.

question is, how do i tell access to create 5 records with all of the same information and change only the caseID for each one?

im really confused on this one. Thanks

 

[Rich]

5 separate records or five separate fields?

 

[rainman89]

5 records... you and i have gone over this before, but i still cant get my head around it

so the records would look like this based on how many are in the combo boxes
caseID1 all information from form
caseID2 all information from form
caseID3 all information from form

etc...

not sure if i would need to do for each ctrl addnew or something along those lines

 

[ajetrumpet]

Rainman,

I read your first post, and here is what I immediately thought of...

Is it possible to use simple commands with a form event to pass the ctrl values to any number of variables, save the current record, and then pass the variable values back to the controls for the next record??

 

[rainman89]

Rainman,

I read your first post, and here is what I immediately thought of...

Is it possible to use simple commands with a form event to pass the ctrl values to any number of variables, save the current record, and then pass the variable values back to the controls for the next record??

I did in fact think of this, but thought that there might be an easier way, perhaps with recordsets, (which i dont know, or understand even though i have read numerous posts and tutorials) so its possible that recordsets arent even the way that i want to go with this..