create new record in tables automatically when button is pressed

[blln]

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).

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.

 

[hermanlaksko]

You can not do this via the macros! sry.
you should do this via VBA - you could make a button that the user would press to get a new invoice or the new invoice would be triggered by some event.

The date problem.
Could solved by me!InvDate=date + 1 and me!servDate=me!invdate + 30

Rgds

 

[blln]

I don't have a clue as how to write VBA code let alone write one for this process. Could you please provide the code? Or provide some help on how to write it? Thank you.

 

[TrojanRabbit]

Are any of your primary keys AutoNumber type? That will affect how you can do it. AutoNumber causes problems with code since Access doesn't like to let users edit what's next. If no AutoNumber, it's much easier.

 

[blln]

Yes, the Invoice Number field for both the INVOICES and the INVOICE DETAILS tables is AutoNumber.

I set it that way so that if I have to enter a new customer with a new invoice, I don't have to keep track of the last invoice number. But if the code you are thinking of does not use autonumber, I will be happy to change the data type as long as the code works.

Please help with the code. Thanks!

 

[TrojanRabbit]

Dealing with it in code would be risky, but an experiment showed it possible. I tried adding a new record to a table with an AutoNumber via code, and the AutoNumber picked up after the number I set in the code. Thus, it apparently is possible.

Having Invoice Number as AutoNumber in both INVOICES and INVOICE DETAILS can be risky, too. If you know that they will always have a record added together, then it's fine.

Either way, you need a recordset object to do it. I'm assuming that Customer ID is a string.
Dim rsta As Recordset
Dim rstb As Recordset
Dim rstc As Recordset
Dim str As String
Dim i As Long
Set rsta = CurrentDb.OpenRecordset("INVOICES", dbOpenDynaset)
Set rstb = CurrentDb.OpenRecordset("INVOICE DETAILS", dbOpenDynaset)
Set rstc = CurrentDb.OpenRecordset("INVOICE DETAILS", dbOpenDynaset)

'Set primary key value for new record
rsta.MoveLast
i = rsta![Invoice Number] + 1

'Find Service End Date matching today
rstb.FindFirst "[Service End Date] = #" & Date & "#"
While Not rstb.NoMatch
rsta.FindFirst"[Invoice Number] = " & rstb![Invoice Number]
str = rsta![Customer ID]
rsta.Addnew
rsta![Invoice Number] = i
rsta![Customer ID] = str
rsta![Billing Date] = ...
rsta.Update
rstc.Addnew
rstc![Invoice Number] = i
rstc![Service Code] = ...
rstc![Service Start Date] = Date + 1
rstc![Service End Date] = rstc![Service Start Date] + 30
rstc![Monthly Charge] = ...
rstc.Update
rstb.FindNext "[Service End Date] = #" & Date & "#"
Wend

What you do after adding the records is up to you. Note that I didn't account for errors here, such as creating duplicate records. I only gave skeleton code to go off. There may be a better method, but here's one.

 

[blln]

I'm very grateful for your answer to my question. I placed your code in the OnClick of my button. When it got to
rstb.FindFirst, it displays an compile error message saying "Method or data member not found". I changed it to
rstb.Find, which I don't know if it's the right thing to do, but it did get passed that but then got another similar compile method at
rstb.NoMatch

I could not seem to change that to any other option. Your response to this is extremely appreciated. Thank you.

 

[TrojanRabbit]

Sounds like it may be a conflict with references. It's in the Microsoft DAO 3.6 Object Library. If you don't have that, try the latest DAO Object Library that you do have.

You may want to check my spelling of field names with the table. Check for errors in spelling, whitespaces, etc. I'm also assuming that [Service End Date] is a Date/Time field.

 

[blln]

TrojanRabbit, thanks so much! Your code works great after I went in and chose Microsoft DAO 3.6 Object Library like you suggested.

Thanks for your prompt reply and suggestions. You are the best!

This problem has been resolved.