There are a number of problems. You have gone very far with a bad schema and you've written a lot of code that shouldn't have had to be written to deal with the issues. The question is - how far are you willing to go to fix the problems? My problem of course is that I don't know why you made the design choices you made and so I cannot help you without spending hours understanding the business model and figuring out what the schema should have been.
I don't expect you to answer the questions. You need to think about them because your data model is full of holes and you need to plug some before moving on.
For example
-Why are Selling and Bills separate tables? I haven't looked at the queries or logic for any form except the one in question but you seem to have a field named Row which may be how you were intending to connect Bills and Selling. This is not how it should be done BTW.
-Why is Notes associated with Invoice rather than Selling (which should probably be named InvoiceItem)?
-Why are you only allowing one note per invoice? At a minimum, you would commonly have two payments. A down payment when the travel is booked and a final payment due before departure.
-What is the logic for handling the purchase currency for the InvoiceItem vs the payment currency received from the Customer? When do you do the conversion? Any application that deals with multiple currencies for transactions needs to have a single currency that is always converted from/to and some logic for the point in time when this happens. You also need currency conversions for each day for each currency you are working with. There are websites you can get a subscription to or even use for free if you only need a couple of transactions per day.
-Are your customers expecting a fixed price when you quote $150 for a room per night? Or, if the currency conversion rate changes, does the price change when they check in? I suppose, you could play the currency market and hope to make money on the conversion rate but it is probably better to be conservative and convert the supplier's prices when the customer places the order so you can give him a fixed price. OR, you can give him an estimate and then a final price before he makes the final payment. That covers you if the rates swing the wrong way for you.
-Why does the search form return a separate row for each item? Is the customer really paying individually by item?
As I said a long time ago, the logic in your button is totally wrong. Testing this app is a serious pain for me so it must be just as bad for you and that is probably why you haven't discovered the errors with your data. PERSONALLY, I never use popup forms except in rare cases. When I do, the popups are model as yours are but they do not interfere with testing. You may be familiar enough with all the objects to be OK with not ever being able to see two objects at once but I'm guessing that this design is keeping you from actually testing the application just as it kept me from testing. In order to make any progress at all, I had to change the properties of the menu form so I could get it out of the way to look at the tables or the code.
This is what you would have discovered had you headed my earlier advice and walked through the code to see what was actually happening. Here are some debug prints so you can see that the logic is not working.
print invoicenumbers - after the string is filled
'A00001', 'A00002', 'A00003', 'A00004', 'A00004', 'A00005', 'A00006', 'A00007', 'A00008', 'A00009', 'A00010', 'A00010', 'A00011', 'A00012', 'A00012',
print invoicenumbers - after the last two characters are removed
'A00001', 'A00002', 'A00003', 'A00004', 'A00004', 'A00005', 'A00006', 'A00007', 'A00008', 'A00009', 'A00010', 'A00010', 'A00011', 'A00012', 'A00012'
print invoiceidlist - after the string is filled
39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,
print invoiceidlist - after the last two characters are removed.
39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50
So -
1. Your code says to search the filtered records. What are you doing to filter them?
2. There is absolutely no reason to use two loops. One to get the invoice name and the second to get the ID. The code isn't needed at all but if there really was some selection method, and you realllllllly like to write unnecessary code, you could use a single loop.
3. Here is the Update query -- As you can see, it is updating every row in the recordsource of the form because I didn't see how to filter it.
print sql
UPDATE tblInvoice SET isPaid = True WHERE InvoiceNumber IN ('A00001', 'A00002', 'A00003', 'A00004', 'A00004', 'A00005', 'A00006', 'A00007', 'A00008', 'A00009', 'A00010', 'A00010', 'A00011', 'A00012', 'A00012')
4. Here is the append query for the notes -- As you can see it is inserting a note for every row in the recordsource of the form?
print sql --- runs for each Id in InvoiceIDList
INSERT INTO tblNotes (InvoiceID, PayNote) VALUES (50, 'Pay note 1')
You can remove All the code from the button click event. All you have to do is click the paid checkbox for each row you want to mark paid. NO CODE IS REQUIRED at all - except for the notes. I can't give you advice on the notes at this point because the schema makes no sense so I don't know what you need to do. Normally, there is no reason to add a paid note. All the code in the BeforeUpdate event of the form should do is to add a paidDate to the record.
My advice - think about the answers to the questions. If you want help with fixing the schema, post the answers and we'll try to get the schema fixed. And just remove all that code from the click event and let Access just update the record because you checked the paid box.
!
