One form to create multiple data entries (1 Viewer)

[RSS705]

Good morning everyone,

What the database currently has: A payment entry form consistenting of many fields. This form populates a payment entry table. Some of the fields within the payment entry form are linked to other tables and queries for data (such as a recipient list).


What the database now needs: A group payment option. Should ten people attend a dinner, the total cost needs to be divided among the attendees, and then the payment entry form table populated with ten different entries and the subdivided cost of the dinner per person.

My thoughts: The most ideal thing to do is to have a hidden recipients window show up when the user indicates that this is for a group event. The user could then add all the additional recipients (beyond the primary which is already collected on the form), and the total amount for the meal. The database would then generate an entry for each recipient listed, dividing the total cost among them, and then simply duplicating the rest of the information as is.

Is this possible? Any direction will help. Just please be thorough with your explanation. I'm using/learning VBA and queries as I go. So far I've accomplished quite a bit, but I'd be amiss if I said I was confident.

Thank you!

 

[Mihail]

I suspect a wrong setup for your DB.
Is your DB properly normalized ?
Database Normalization and Table Structures - Microsoft Access / VBA

 

[David R]

Did you delete a thread about this from earlier today? I swear I answered this one already...

Anyway, we need a little more information to help you. In particular, what do you want to do with the 'remainder' of a bill - if the total is $250.76 and there were ten diners, who gets to pay for the extra six cents?

 

[RSS705]

Hi, thanks for replying.

Mihail - Yes I'm 90% sure the database is normalized correctly. I assume it would be running into functional issues if it were not. It's been in use for half a year now and we have extracted CSVs of the transfer of value data several times before.

David - The remaining cents can be omitted. I know that sounds odd from a financial point of view, but this database keeps the company compliant with regulatory requirements, and is meant to flag extreme values for government reporting, more so than to track every dollar and cent spent.

 

[David R]

Is this form bound or unbound? In other words, does it store the record automatically when you leave the form, or do you have code that stores everything for you when you push a button to 'confirm', etc? The usual method is bound forms, but it affects the answer/solution.

Also, will all 'diners' always be existing records (employee table, or similar)? Or is there a chance for example that you paid for ten diners, but four of them were consultants/judges/Congresscritters that will never appear in your database again?

 

[RSS705]

It stores the record automatically when you leave the record and proceed to a new blank form.

Thanks again.

 

[David R]

One more question - are you working in .mdb or .accdb format?

 

[RSS705]

Can ask as many questions as you'd like, I really appreciate the help.

.accdb format

 

[David R]

Did you see the one about the diners who might be outside your system? Right now I am proceeding as if that cannot happen...

 

[RSS705]

Hi David,

My mistake I missed that. All of the recipients will be pre-entered by the users and then reused.

So no, it's not possible an outside attendee to occur. They will be added into the recipient table prior to the completion of the payment entry.

Thanks!

 

[David R]

Sorry, took me a bit longer than I expected to get the bugs worked out. See the attached and read over the code carefully... any part you don't understand, ASK because you'll have to adapt this somewhat to your database (form names, field and control names, etc). I tried to use queries more than VBA because you said you were fairly new to coding... I tried to comment extensively but I may have missed something, depending on your actual experience level.

Essentially what this is doing though is popping up a form to enter the additional users who it will be split among, then using an append query to add new expense records for them. It also directly modifies the 'primary' record (the one you already made before asking for the split) so it has the correct values as well.

There's some rudimentary error checking in here, but you may run into cases where you want to add additional checks. Also I have disabled the 'you are about to run a data-destroying query!' messages, but you may want to turn those back on, depending on how intelligent your end users are. :banghead:

 

[RSS705]

David, you're example is amazing. I'll need more time to fully explore it, and this will probably need to happen tomorrow but I'll reply back if I have any questions.

Thank you again for your help! Really appreciated.

 

[RSS705]

Hi David,

I've gone over your solution and again, I have to say thank you. I've been able to learn a lot from it, but I do have a couple follow up questions.

1) Within the FormExpenses VBA, I see DoCmd.OpenForm "formSplit", , , , , acDialog Why are the commas with spaces necessary?

2) Could you tell me what these options mean? Option Compare Database and Option Explicit

3) Referring to the FormSplit VBA, I've seen this many times before, could you explain to me what RecordsetClone locates? Haven't been able to understand that one.

4) Also Forms!formExpenses.Dirty = False, not sure I quite understand how the Dirty function acts.

 

[David R]

1) The commas leading up to acDialog tell Access to ignore/take the default value of those parameters. If you put your cursor amongst them and move left/right I think you'll see what the default values are - things like acNormal, etc.

2) Option Explicit is good coding practice - it'll check more variable errors for you. Option Compare Database should be in every VBA window by default, I've never messed with it but http://www.access-programmers.co.uk/forums/showthread.php?t=101752 says it has to do with character sets.

3) RecordsetClone works on a COPY (clone) of your current recordset... in other words what your form is based on, in this case. It's a good way to look at something in the current recordset without CHANGING the current recordset. In this case it's a quirk of Access that you won't always get an accurate RecordCount on the main recordset, so you have to use a clone. I have no idea why. :banghead:

4) Sorry, I should have explained that one better. A form is "dirty" when it has unsaved changes in the current record. Setting Dirty to false forces Access to try to save the record. Essentially it does much the same thing as DoCmd.RunCommand acCmdSaveRecord but is a lot easier to remember.

 

[David R]

Oh, I forgot to mention... if you're using a split database (shared BE on the server, desktop FEs for each user) with linked tables, tableSplit should NOT be moved to the backend. You want it to be local, so that when a user starts a split process, they don't collide with someone else's process.

 

[RSS705]

Will remember that.

But another question.

While implementing your technique, I am having an issue with the queries. I am modifying the appendCurrentToTemp query and am running into a error I'm not familar with.

What I've done:
Within the appendCurrentToTemp query, I modified this line Expr1: [Forms]![formExpenses]![EmpID] to read Expr1: [Forms]![formPayment_Form]![Payment_ID]

(assuming where spaces exist we need underscores). I left the remainder of the query as is.

The tempSplit table was brought into the current database, and is unchanged, while I attempt to prove it works prior to my further tinkering.

What's Happening:
When the code is trigger I receive a message pane which says "Enter Parameter Value".

If I type in a random number like "1" it will load the subsequent formSplit window, displaying it partially correct. However, if I press cancel or leave it blank, it links me to an error in the code which says "Microsoft Access cannot find the referenced form 'Payment_Form'.

Do you know why the query would be having difficulties locating the forum? Even with it described as above?

Thanks, and have a great new year.

 

[RSS705]

Just wanted to say not to worry about it, I realized my syntax was incorrect. The underscores were not necessary :banghead:

 

[RSS705]

Hey David,

Just one last thank you. Successfully implemented your solution into our database system this morning. Working perfectly.

Best of luck to you in the new year!
Rob

 

[David R]

Awesome, glad it worked out for you. It was a fun trick to learn for myself, too... (as Mihail said, it looks badly normalized at first, but there is a use for this sort of behavior in just such a situation as this)