B
blln
Guest
I’m trying to create a database that will automatically bill each customer on a monthly basis for services provided by our company.
I have 3 tables: The CUSTOMERS table with Customer ID (primary key), Name, Address, Telephone, etc.; the INVOICES table with Invoice Number (primary key), Customer ID, Billing Date; and the INVOICE DETAILS table with Invoice Number (primary key), Service Code (primary key), Service Start Date, Service End Date, Monthly Charge. The Invoice Number field for both the INVOICES and the INVOICE DETAILS tables is AutoNumber.
I created a main form with a subform. The main form displays the customer information while the subform displays all the past and present invoices of that particular customer. This subform displays all invoice details such as the invoice number, service code, service start date, service end date, and monthly charge.
I also have another form with just one button. How can I set it up so that if I click this button the database goes through all records in both the INVOICES and the INVOICE DETAILS tables and compare the Service End Date field with today’s date. If any matches is found, a new invoice, thus a new record, would be automatically created for that particular customer in the INVOICES and the INVOICES DETAILS tables. Meaning, a new invoice would be created with a new invoice number, a service start date that is tomorrow’s date (today’s date plus one day), a service end date that is 30 days from tomorrow’s date (today’s date plus 31 days). All these should be filled in automatically without any user input.
I tried to create a macro to do this but it is not working. If anyone has a code for this and don’t mind sharing please help me. Thank you in advance.
I have 3 tables: The CUSTOMERS table with Customer ID (primary key), Name, Address, Telephone, etc.; the INVOICES table with Invoice Number (primary key), Customer ID, Billing Date; and the INVOICE DETAILS table with Invoice Number (primary key), Service Code (primary key), Service Start Date, Service End Date, Monthly Charge. The Invoice Number field for both the INVOICES and the INVOICE DETAILS tables is AutoNumber.
I created a main form with a subform. The main form displays the customer information while the subform displays all the past and present invoices of that particular customer. This subform displays all invoice details such as the invoice number, service code, service start date, service end date, and monthly charge.
I also have another form with just one button. How can I set it up so that if I click this button the database goes through all records in both the INVOICES and the INVOICE DETAILS tables and compare the Service End Date field with today’s date. If any matches is found, a new invoice, thus a new record, would be automatically created for that particular customer in the INVOICES and the INVOICES DETAILS tables. Meaning, a new invoice would be created with a new invoice number, a service start date that is tomorrow’s date (today’s date plus one day), a service end date that is 30 days from tomorrow’s date (today’s date plus 31 days). All these should be filled in automatically without any user input.
I tried to create a macro to do this but it is not working. If anyone has a code for this and don’t mind sharing please help me. Thank you in advance.
