Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50

    Form to append multiple records in table

    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)



    Click image for larger version. 

Name:	tblBilling.jpg 
Views:	45 
Size:	110.4 KB 
ID:	49512
    Entries to this table are made by a Form (image frmBilling) which can feed in Bills for 1 client at a time.

    Click image for larger version. 

Name:	frmBilling.jpg 
Views:	46 
Size:	56.0 KB 
ID:	49513

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Quote Originally Posted by June7 View Post
    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.

    Quote Originally Posted by June7 View Post
    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.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How do you want to choose which clients to create records for - an unbound listbox? You have only 4 clients?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    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.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    OK. Here it is.

    V 1.4 Clean.zip

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

  9. #9
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Quote Originally Posted by CJ_London View Post
    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.

    Quote Originally Posted by CJ_London View Post
    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.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Now being crossposted by mike60smart at https://www.utteraccess.com/topics/2064366?post=unread
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    seems to be a habit, posting questions from other OP's as if they are their own. What happens to the answer I wonder?

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    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.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Yep. Mike is a friend and helps me from time to time when I run into problems.

  14. #14
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Quote Originally Posted by CJ_London View Post
    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.

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Sam View Post
    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?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-21-2020, 03:49 PM
  2. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  3. Replies: 3
    Last Post: 01-26-2015, 04:25 AM
  4. Append Query Saving Multiple Records
    By jewll in forum Queries
    Replies: 5
    Last Post: 12-13-2014, 03:54 AM
  5. Replies: 5
    Last Post: 12-12-2011, 08:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums