Form to append multiple records in table

[Sam]

OK. Will keep that in mind.

[Welshgasman]

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.

[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

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



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

[CJ_London]

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.

[Sam]

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.

[Sam]

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.

[Sam]

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.

[CJ_London]

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

[Sam]

No, you need a joining table to do that

Would it be something like this ?

[Sam]

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.

[June7]

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

[mike60smart]

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:-

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

[CJ_London]

error message is telling you the problem - you have not declared itm

[mike60smart]

Hi CJ

I tried this but it gives the same error?

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

[CJ_London]

look again