Automated billing procedure - is it possible?

[Gavroche00]

Hello,

I would like to generate on a weekly basis an automated billing schedule. For example, I have 5 rentals (room01 - room05) that are rented weekly to the same people (john, wilson, karen, doug, brian) for the price of $10 weekly - every week it is the same exact info. Rather then entering 5 records weekly I would like it done automatically.

Can I programmatically get Access to enter the date, the rentalID, the renter and the price if the info is always the same. I know how to open the record set in ADO, but I have no clue as to how to go about getting these records generated weekly. Can this be done? If so, can someone tell me what subject to read about or refer me to articles dealing with that? I don't even know the name of such procedure.

Thank you

David

 

[scriverb]

I would create a table with the necessary fields and manually enter the records that you want updated weekly. This table would be a list of your rental units. You would just change the name and price if necessary. You can add new people, change the info, and delete the records at any time. Then on a weekly basis just run an append query that adds the records in the table to the master table.

The automatic running of this query can be triggered by code that looks for the first time the database has been opened for each week. If that is so then run the query.

To do this we need to store a LastRunWeekNumber in a table. Create a table called tblDBParameters. Create the field named LastRunWeekNumber as a Integer. Save and open the table and put the value 0 in the field.

Now use the following code in OnLoad event procedure of the first form opened in the database:

If DLookup("LastRunWeekNumber", "tblDBParameters") <> DatePart("ww",Date()) then
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryAppRentalRecords"
   DoCmd.SetWarnings True   
   Dim db as DAO.database, rs as DAO.Recordset
   Set db = currentDB
   Set rs = db.OpenRecordset("tblDBParameters", dbOpenDynaset)
   rs.edit
   rs("LastRunWeekNumber") = DatePart("ww", Date())
   rs.Update
   rs.close
   db.close
Else
   'query already run this week.
End if

Createa an append query to perform the adding of these records to your master billing table:

INSERT INTO [i]yourmasterbillingtable[/i] ( RentalID, Name, Price )
SELECT A.RentalID, A.Name, A.Price 
FROM [i]yourRentalUnitsTable[/i] as A;

This should work for you. If the query has not been run when the form opens it will run it and update the tblDBParameters with the current week number. If already run it bypasses the query process and opens the form.

Post back if you have any questions.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Craig0201]

Bob,

Why the recordset when an Update query would do?

If DLookup("LastRunWeekNumber", "tblDBParameters") <> DatePart("ww",Date()) then
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryAppRentalRecords"
   DoCmd.OpenQuery "qryUpdateWeekNo"
End If

qryUpdateWeekNo

UPDATE tblDBParameters SET LastRunWeekNumber = DatePart("ww", Date())

Craig

 

[scriverb]

Craig: That will work just fine also.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Gavroche00]

Thank you very much for the info. Now I undesrtand that the logic is to create a 'template' and update it every week. Something came up and I can't start yet, but I will try it as soon as I can, it doesn't seem that hard to make work. Thank you for the help.

David