How to Autogenerate new records???? 1

[beverlee]

Have been searching through the threads but can't find anything that seems to be close. I need to see if there is a way to have Access autogenerate new records on the 1st of each month. It could be done through a button click.

I have tables called Client and Fees. They are in a One to Many relationship. On the 1st of each month, a monthly fee needs to be assessed to all active clients who meet certain other criteria. Once I have those clients queried out, how would I go about having Access add a record to the fees table for each client?

Table - Client
ClientID
FirstName
LastName
Etc.....

Table - Fees
FeeID
ClientID
Date
FeeType
FeeAmount

Any hints would be appreciated!

 

[LittleSmudge]

First off it is really bad practice to use key words as user defined object names. So change the Date field in table Fees to FeeDate ( Makes it consistent with the others too )

Its also good practice to distinguish Foreign keys from Primary keys by using Ref instead of ID on the Foreign Key ( because it REFers to the PK )


In code on the command button's On_Click event

If IsNull(DLookUp("FeeDate","Fees","FeeDate = #" & Date & "#") ) Then
' You don't have any records in the Fee table for this month so it is okay to continue.

DoCmd.RunSQL _
      "INSERT INTO Fees (ClientRef, FeeDate, FeeType, FeeAmount) " _
    & "SELECT ClientId, Date(), "Monthly Fees", 4.56 " _
    & "FROM Client " _
    & "WHERE {add selection clause here} ;

Change the values as required.


'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[formerTexan]

Hi Beverlee,

Something along the lines of the following will work if you want to use a DAO version. As in all things Access, it isn't the only way. It assumes that your primary key (FeeID) is an autonumber and that you want to charge every client a monthly fee. If you will need to restrict the clients affected, then amend the SELECT statement accordingly. You will need to modify the INSERT INTO statement for whatever info you actually need to append and you should declare some variables and use them instead of actual values.

Otherwise please forgive any "on the fly" typos.

Cheers,
Bill
=====================

Dim db as dao.database
Dim rs as dao.recordset
Set db = currentdb
Set rs = db.openrecordset "Select * FROM Client;"

If not rs.bof and not rs.eof then
rs.movefirst

Do while not rs.eof

db.execute "INSERT INTO Fees (ClientID, FeeAmount, etc.. )
VALUES (rs!ClientID, 100, etc....);

rs.movenext
Loop

rs.close
db.close
set rs = nothing
set db = nothing
End if

 

[beverlee]

Bill-
Tried putting this code on a command button. I don't know coding very well. (Obviously) Keep getting an "Expected End of Statement" error message. Any thoughts? ....and thanks for your time.

Private Sub CmdApplyFees_Click()
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordset "SELECT [tbl Client].* FROM [tbl Client]WHERE ((([tbl Client].Status)='Active'));"
If Not rs.BOF And Not rs.EOF Then

rs.MoveFirst

Do While Not rs.EOF

db.Execute "INSERT INTO [tbl Drug Court Fee] (ClientID, Fee Amount, FeeDate, Type of Fee )"
VALUES (rs!ClientID, 10, Date(), Supervision I);

rs.MoveNext
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End If

End Sub

 

[LittleSmudge]

Missing open and close brackets for a start and you've also renamed your objects since your original post. Is that for real ? If so then rename them again because it is really bad news to have space characters in ANY object names in a Windoz environment. See faq700-2190


Set rs = db.openrecordset( "SELECT * FROM Client WHERE Status = 'Active';" )

You also have quotes in the wrong place in the db.Execute line.
Sort the object names out and have a look at the quotes. If you can't get it to run then post back where you've got to.


'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[beverlee]

Little Smudge-
Thanks for responding. I think I have made some progress but am getting stuck with a "Too few parameters" error. As far as the table and field names with spaces.......I know better but unfortunately I inherited this big ugly project from someone else. Here's my code now:

Private Sub CmdApplyFees_Click()
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordset("Select * FROM [tbl Client]WHERE Status = 'Active';")

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst

Do While Not rs.EOF

db.Execute ("INSERT INTO [tbl Drug Court Fee](ClientID, FeeAmount) VALUES (rs!ClientID, 10);")


rs.MoveNext
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End If

End Sub

It's giving me the error on the db.execute line.

Thanks so much for your brains and your time.

Beverlee

 

[PHV]

And why not simply something like this ?
Private Sub CmdApplyFees_Click()
CurrentDB.Execute "INSERT INTO [tbl Drug Court Fee](ClientID, FeeAmount) SELECT ClientID, 10 FROM [tbl Client]WHERE Status = 'Active'"
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[beverlee]

Same error as above. "Too Few Parameters. Expected 1." Thanks for the suggestion though. Any other ideas?

 

[PHV]

Double check the spelling of the tables/fields.
Add a space before the WHERE keyword.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[beverlee]

Same error!

 

[PHV]

Can you please post your actual code raising the error and the REAL table layout of the 2 tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[beverlee]

I got it to work! Still being haunted by my predecessor's spaces in object names. Thanks a million for your time!

Beverlee