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

    OK. Will keep that in mind.

  2. #17
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Sam View Post
    OK. Will keep that in mind.
    Well I do not know about you, but I still like to learn, despite my age (65+) and the fact I do not use Access in any resonable serious setting anymore.
    That is why I ask about code other people post (such as that posted for Mike to help you in UA, so that I can understand it, and hopefully remember it, even with my bad memory.
    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

  3. #18
    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

    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
    I am tying to pursue this line of action. So I have removed the BillPercent and BillDetails from the tblBilling and shifted them to a newly created table tblBillDetails.

    To carry on I would need the frmBilling (image above) amended in such a way that the Form would have only the Date field, and 2 subforms, one for the BillDetails and the other for Bookings (Customers). Something like this

    Click image for larger version. 

Name:	Untitled.jpg 
Views:	23 
Size:	29.6 KB 
ID:	49537

    Then, User only selects checkboxes from both tables and applies selected Bill Percent (ages) to selected Customers. Is this the correct approach and how do I go forward with this idea ? Another thing, the BookingID (FK) field on the tblBillDetails will be a multi value field indicating to which customers the concerned percentage has been applied.

    Thanks

  4. #19
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't know your table structure and relationships, so difficult to comment. but you might have a structure similar to this

    tblCustomers
    CustomerPK
    CustomerName
    CustomerAddress
    ....
    ...

    tblWorks
    WorkPK
    WorkName
    WorkPCent

    tblQuoteHdrs

    QuoteHdrPK
    CustomerFK
    QuoteTtl
    QuoteDate

    tblQuoteLines
    QuoteLinePK
    QuoteHdrFK
    WorkFK
    WorkCompletedDate

    tblInvoiceHdrs
    InvoiceHdrPK
    QuoteFK
    InvoiceDate

    tblInvoiceLines
    InvoiceLinePK
    InvoiceHdrFK
    QuoteLineFK


    you can then create a query to list all quotes/lines with a WorkCompletedDate and no matching invoiceDate - these can then be invoiced at a click of a button.

  5. #20
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    My dB zip file is attached on the previous page. Since I'm not into contracting, there are no quotes. The price would be settled through a brief personal interaction.
    I will take some time trying out your line of thought and see if that works for me.

  6. #21
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Click image for larger version. 

Name:	Screenshot 2023-01-26 140505.jpg 
Views:	21 
Size:	204.4 KB 
ID:	49562

    A little explanation on the process flow

    We have people who make enquiries whose details are entered in the tblEnquiries. Each enquiry could consist of multiple options of Units (till one is finally Chosen) and their choices go into the tblEnquiryChoices. The UnitDetailsID is the FK here linking the table to the tblUnitDetails.

    Once a Choice has been made, it is entered in the tblBookings where each BookingID belongs to a Customer. Hereon, the Bills need to be raised for each customer (BookingID) from time to time. At present the BillPercent and BillDetails (Items) are manually entered through the Form for Billing. The BillNo is a number field which auto increments.

    There is a standardized set of Billing Items (BillDetails) with a corresponding Percentage and as CJ_London suggested I am toying with the idea of having an intersecting table for Bill Details. If I do that I will need a BookingID (FK) in there which will have to be a multi value field listing all the BookingIDs already billed for that item and percentage so that no duplicate bills are raised on the same BookingID.

    Then the Billing Form would need to have 2 listboxes BillDetails and Bookings (I have a query Booking Values which can be used in place of the table Bookings). Both of these lists would need to be multi select. The percentages for the selected BillDetails need to be summed and applied to each of the selected BookingIDs. So the Form would need to raise multiple bills at one time in which case how do I handle the BillNo field.

    This all looks like a staggering and monumental task to me with my skills, so much so that I am tempted to continue with the current process flow at hand.

    Please advise me if the above is doable and if so how do I go about it.

    Thanks in advance.

  7. #22
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    In the alternative would it be possible to type in the Bill Percent on the Form and have a multi select listbox for customers and generate that many bills at one go.

  8. #23
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If I do that I will need a BookingID (FK) in there which will have to be a multi value field listing all the BookingIDs already billed for that item and percentage so that no duplicate bills are raised on the same BookingID.
    No, you need a joining table to do that

  9. #24
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Quote Originally Posted by CJ_London View Post
    No, you need a joining table to do that
    Would it be something like this ?

    Click image for larger version. 

Name:	Screenshot 2023-01-27 155641.jpg 
Views:	16 
Size:	90.2 KB 
ID:	49571

  10. #25
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Quote Originally Posted by Sam View Post
    In the alternative would it be possible to type in the Bill Percent on the Form and have a multi select listbox for customers and generate that many bills at one go.
    Wouldn't this be a lot easier. I just need to lay my hands on some VB to pick up the multi selected customers and raise that many bills.

  11. #26
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Looping multi-select listbox selected items and doing something with each item is a common topic. Here is one example: https://stackoverflow.com/questions/...x-into-a-table
    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.

  12. #27
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    VB Error

    Hi June

    I am trying to help Sam in resolving this issue and in the On Click Event he has the following Code but when he runs it
    he gets the following Error.

    Any help appreciated

    The code is:-

    Code:
    Private Sub cmdInsert_Click()
    Dim db As Database
        Dim rs As Recordset
    
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblBilling")
        For Each itm In List9.ItemsSelected
            rs.AddNew
            rs!BillNo = BillNo.ItemData(itm)
            rs!BookingID = itm
            rs.Update
        Next
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Sub
    It highlights the itm in line @For each itm in List9.ItemsSelected
    Attached Thumbnails Attached Thumbnails Error.png  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #28
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    error message is telling you the problem - you have not declared itm

  14. #29
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi CJ

    I tried this but it gives the same error?

    Code:
    Private Sub cmdInsert_Click()
    Dim db As Database
        Dim rs As Recordset
        Dim lngItm As Long
        
        lngItm = Me.BookingID
        
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblBilling")
        For Each itm In List9.ItemsSelected
            rs.AddNew
            rs!BillNo = BillNo.ItemData(itm)
            rs!BookingID = itm
            rs.Update
        Next
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Sub
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #30
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    look again

Page 2 of 3 FirstFirst 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