Form to append multiple records in table

[Sam]

Apologies in advance if my post title doesn't make sense.

The issue at hand is, I have a tblBilling (image tblBilling) where some Bills have been raised on 4 clients (Bookings)


Entries to this table are made by a Form (image frmBilling) which can feed in Bills for 1 client at a time.





I would like to introduce an option of Bulk Billing, where further Bills of a percentage are raised on all 4 clients at one go, instead of filling in the Form 4 times. I don't mind a separate Form to do the job. The problem (for now) is that the BillNo field is a text field. I don't mind making this a Number field with auto increment feature and putting the text part (same on all Bills) on a report where Bills are generated and printed.

What would be the best way to go forward ? Any help will be appreciated.

[June7]

So each BookingID is a client?

You already have an autonumber field with BillID.

Generating a custom sequential unique identifier is a common topic. Simplest approach may be using DMax() function if field is a number type. Otherwise, parsing a string to extract number part and increment then rebuild string to save in field gets complicated.

Another approach is to calculate BillNo on a report by using textbox RunningSum property - no code need, just an expression in textbox and property setting.

"Batch" creating records can be done couple ways. An INSERT SELECT FROM action SQL is one. VBA looping structure is another. All records in batch will have the same percent value?

[Sam]

So each BookingID is a client?

You already have an autonumber field with BillID.

Yes each BookingID refers to a Client. The Booking ID is the FK linking the Billing and Bookings tables.

So each BookingID is a client?
"Batch" creating records can be done couple ways. An INSERT SELECT FROM action SQL is one. VBA looping structure is another. All records in batch will have the same percent value?

Yes. All records in Batch will have the same percentage.

[June7]

How do you want to choose which clients to create records for - an unbound listbox? You have only 4 clients?

[Sam]

If I have to choose a particular client, I will use the Form I attached above and bill individually. The Batch Billing will bill clients in an equal percentage.
This copy of the dB is only for trials. Actual clients may be between 50-100, so rather than make a 5% bill one by one, the ability to do it in one go will be very helpful.

[June7]

Consider:

CurrentDb.Execute "INSERT INTO tblBilling(BookingID, BillDate, BillPercent, BillDetails) SELECT ID, Date(), 10, 'Booking Amount' FROM Bookings"

WHERE clause can be added to the nested SELECT. If you want to generate BillNo and save to table, that will require a completely different approach.

If you need more assistance, will need to know more about your data schema and business process. You can attach database file to post.

[Sam]

OK. Here it is.

V 1.4 Clean.zip

[CJ_London]

looks to me like the percentage is based on bill details - so bill 007 is 10+15+5=30, bill 010 is 10+15+5+5=35

if so, why not just include a percent in the details table (but bet you are going to tell me you don't have such a table). Easy enough to aggregate these values and concatenate the detail description if required. Then you just need to decide which customers you are going to bill - or can be determined based on work done and not billed

[Sam]

looks to me like the percentage is based on bill details - so bill 007 is 10+15+5=30, bill 010 is 10+15+5+5=35

Yes. The percentage of the Bill is based on the amount of work done.

if so, why not just include a percent in the details table (but bet you are going to tell me you don't have such a table). Easy enough to aggregate these values and concatenate the detail description if required. Then you just need to decide which customers you are going to bill - or can be determined based on work done and not billed

If I understand you correctly, you are suggesting a BillDetails table where all the work to be done is listed out alongside the proportionate percentage (right up to 100) and then I can pick the rows from that and bill each client accordingly. Let me think about that for a bit and work it out.

[Welshgasman]

Now being crossposted by mike60smart at https://www.utteraccess.com/topics/2064366?post=unread

[CJ_London]

seems to be a habit, posting questions from other OP's as if they are their own. What happens to the answer I wonder?

[mike60smart]

Hi CJ
Apologies but I had posted a question regarding this question on Utter Access not realising that the OP had posted
a similar question.
I am trying to help the OP solve this question and all answers are relayed to the OP.

[Sam]

Yep. Mike is a friend and helps me from time to time when I run into problems.

[Sam]

if so, why not just include a percent in the details table (but bet you are going to tell me you don't have such a table). Easy enough to aggregate these values and concatenate the detail description if required. Then you just need to decide which customers you are going to bill - or can be determined based on work done and not billed

This approach sounds much better and I will explore this option as well.

I could have Bill Details on one side and customers on the other with check boxes for both and just choose which items to bill to which customer/s.

[Welshgasman]

Yep. Mike is a friend and helps me from time to time when I run into problems.

But he just goes off and asks the question elsewhere? Why not cut out the middleman and ask yourself? Plus you will learn bit by bit yourself?
I have found him doing that for another DB I recognised and helped a user with?

Seems to do that a hell of a lot?