I have to insert what into what!?

[Underling]

I have been asked to see if I can automate some of the monthly out goings for our outgoings report each month.
This is a report generated off a query of all the bills that have come in over the month, and need paying.
What I have been asked is can some of them, lets say the rent on the building, be automatically inserted into the query, and there for the report, each month. And adjusted or updated infrequently.

So what I would need is [Trader][Amount][Date][Description] inserted automatically on the 20th of each month.

All the fields are already being use at the moment. Just updated manually.

I know there are update queries out there but I have never used one before.

Could anyone help out?
Thanks
Underling

 

[dpatrickcollins]

Could you please explain what you mean by the fact that all the fields "are being used" presently but "just manually"? This will be critical in providing a solution.

 

[Underling]

The fields are from a form that is updated when a bill arrives. And the main information form the bill; who its form, how much they we owe, when it arrived and what its for; is recorded in the appropriate fields.

[Trader][Amount][Date][Description]

This then generates a report that lets them know how much we owe at the end of each month.
What they want is some of the bill, which are the same each month, like automatic payments on equipment, to be updated without a human sitting down and typing it in each month.


So we could add [HB Power Co][$1,189.00][20/6/03][HP Flat bed planer] and it could incert its self into the query, each month.

I hope this is a better description of what I am asking. I have been all through the FAQ's and could find nothing that could help me.
Thanks for any help.
Underling

 

[SgtJarrow]

How much work do you want to do??? This really wouldn't take much. What you need is the following:

A query that displays all the fields you need to include, probably just [Trader][Amount][Date][Description] in your case. You will need to include in the critera for this query that which makes these record recurring...the [Trader] name or whatever. You will also need to put the following in the critera of the date field DateSerial(Year(Date()), Month(Date()) - 1, 20). Next in the query, add a new field and put the follwing in the field line NewDate: DateSerial(Year(Date()), Month(Date()), 20) Finally set this query to be an append query back into the same table, using the fields but set the NewDate field to the [Date] field as this is for the new month.

Last bit of work is the code to make this happen. In the OnLoad event of the first form to open in your database put:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", DbOpenSnapshot)

Do Until rs.EOF
If rs.Fields("Date&quot >= Date() Then Exit Do
Loop

If Day(Date())>=20 And Not rs.EOF Then DoCmd.OpenQuery "AppendQueryName"

Set rs = Nothing
Set db = Nothing

And make sure you have the Microsoft DAO 3.6 Objects referenced in you VBA references.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[Underling]

Thanks for that 'mage.
I have yet to try this out as I have been moved on to more pressing yet mundane work.
I will let you know how I get on.

PS: Like the custom sig'

 

[Underling]

Ok, I got so far.
All was going well, I now have the apppend query set up and I can see how this is going to work.
Next moved on to adding the code to the OnLoad event list in the form properties.
I set up the Private Sub.. as below.

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordest

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPayment", dbOpenSnapshot)

Do Until rs.EOF
If re.Fields("Date&quot > Date Then Exit Do
Loop

If Day(Date) >= 20 And Not re.EFO Then DoCmd.OpenQuery "qryUpdate"

Set rs = Nothing
Set db = Nothing
End Sub

I did how ever get a compile error for
rs As DAO.Recordset, in the third line.

Now is this because I don't have DAO 3.6 Objects refereneded in my VBA references?

Because I don't know if I have or haven't and I don't know how to check.
I am running Access'97 if thats any help.
Sorry to be a pain, but my VBA an't that hot.

 

[SgtJarrow]

It shouldn't matter....

but since you are using Access 97, just remove the DAO. from the second and third line....they are not really necessary.

if that still doesn't work, let m eknow and we'll keep working on it.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[Underling]

Well that seems to work. That and the fact I cleaned up two typos in the code.
But now when I try to run the form, I just get the “hour glass” and it never loads.
So unsure what to do now.

So take it form the top. The append query looks like this…

INSERT INTO tblPayment ( TraderKey, Amount, Discription, [Date] )
SELECT tblPayment.TraderKey, tblPayment.Amount, tblPayment.Discription, DateSerial(Year(Date()),Month(Date()),26) AS NewDate
FROM tblPayment
WHERE (((tblPayment.TraderKey) Like 1) AND ((tblPayment.Date)=DateSerial(Year(Date()),Month(Date())-1,26)));

And the code on frmPayment OnLoad form looks like this.

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPayment", dbOpenSnapshot)

Do Until rs.EOF
If rs.Fields("Date&quot > Date Then Exit Do
Loop

If Day(Date) >= 26 And Not rs.EFO Then DoCmd.OpenQuery "qryUpdate"

Set rs = Nothing
Set db = Nothing
End Sub

The only thing that I have changed is that its now looking at the 26th not the 20th.
I am getting no compile errors at all, it just hangs up.

 

[Underling]

Hold everything.
The code runs! Now I just don't know if it will update.
I can see I have done somthing wrong with the way that I have set up the query. I will re-create it and see how I get on.

 

[Underling]

I can so see how this is going to work, that its driving me nuts that it will not work right now.
I have had a bit of a look at append queries and how they work so I set up a new table call tblUpdates that hold the same field types as those in the tblPayment.
I then wrote a new query (“qryUpdate”) that will update tblPayment when it’s run…

INSERT INTO tblPayment ( TraderKey, Amount, Discription, [Date] )
SELECT tblUpdate.TraderUpdate, tblUpdate.Amount, tblUpdate.Description, DateSerial(Year(Date()),Month(Date()),26) AS NewDate
FROM tblUpdate
WHERE (((tblUpdate.TraderUpdate) Like 4));

As you can see I’ve used the DateSerial function to insert the date.

Now the code that you have given me, remembering that my VBA is about as good as my ability to lick my own elbow, for the OnLoad event on the payment form. Does this code check what day it is every time that the form is opened? And if it’s the 26th it will run the “qryUpdate” and insert a new record into the tblPayment and show as updated 26-Month-2003?

So here is the code from the OnLoad event from the frmPayment

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPayment", dbOpenSnapshot)

Do Until rs.EOF
If rs.Fields("Date&quot >= Date Then Exit Do
Loop

If Day(Date) >= 26 And Not rs.EOF Then DoCmd.OpenQuery "qryUpdate"

Set rs = Nothing
Set db = Nothing
End Sub

Because this is exactly what we need and that would be fantastic if I can get it running.
At the moment when I open the form nothing updates. Even if I change all the dates to 27th. The form opens and shows only the records that we have entered ourselves. If I run the qryUpdate, it will insert a new record.
So there you have it.
Where am I going wrong?
Thanks for any help.

 

[SgtJarrow]

Quick look at the code would lead me to this....

What you are trying to do is take last month's data and append it into this month as a new record....

So the SQL for your append query would have to be something like:

INSERT INTO tblPayment ( TraderKey, Amount, Discription, DateSerial(Year(Date()),Month(Date()),26))
SELECT tblUpdate.TraderUpdate, tblUpdate.Amount, tblUpdate.Description, DateSerial(Year(Date()),Month(Date()) - 1,26) AS NewDate
FROM tblUpdate
WHERE (((tblUpdate.TraderUpdate) Like 4));

Notice the change of [Date] to the date serial statement, becuase you want to put the current month/26/03 into your record. Also notice the addition of the -1 in the second date serail, because you want to find last months record to carry forward....


****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com