Newbie - Please Help

C

commexec

G'day,
Being a very inexperienced user of MS Access, I am hoping I can ge
some help on here.
I have a databsae that is used to enter all in and outgoin
correspondence. I need a way to tell the database when it opens t
append the tables to an archive table when the new year rolls around.

Basically the query looks at the year and if the year is one less tha
the current year then it will append that table. But how do I get th
DB to check the current year against the year in the table and tell th
query to do what it has to do without having to resort to manual input.
It needs to be automatic on start-up.

Field Name Data Type
Number (Autonumber)
Year (Date/Time - dd/mm/yy format)
Date Recieved (Date/Time - dd/mm/yy format)
Minute Dated (Date/Time - dd/mm/yy format)
Originator (Text)
Subject (Memo)
File (Text - List box)

Any help would be appreciated. Oh and it is MS Access 9
 
D

Dennis

Assuming your database starts by loading an opening form, in the On load
event for this form put something like the following code

If Year(DLookup("[YourDate]","YourTable")) < Year(Now()) then
DoCmd.OpenQuery "Your Append Query"
end if
 
C

commexec

Thanks for that, but I get a runtime error now

runtime error '13'
type mismatch.

Private Sub Form_Load()
If Year(DLookup("[Year]", "tbl_inlog")) < Year(Now()) Then <---
This is the line it highlights
DoCmd.OpenQuery "qry_archivein"
End If
End Sub




tx
commexe
 
C

commexec

Okay,
I adjusted the code to this

Private Sub Form_Load()
If DLookup("[Year]", "tbl_inlog") < Date Then
DoCmd.OpenQuery "qry_archivein"
Else
End If
End Sub

This works fine, but I would like to disable the "You are about to ru
blah blah blah" messages. Any ideas on how to do this??
 
C

commexec

LMAO
Amazing what you can do with the help function

I figured it out and now it works.
Thanks for the help though, I wouldn't have been able to get this far.


thanx

commexe
 
S

Speqter

I know that this is a stupid question but I'm having trouble finding the
answer:
Where do you put this code? Where is the "On Load Event"?

Thanks!
Ron
 
D

Dennis

My function was using DLookUp to return a date which is why I had the Year
function around it. Yours is returning just the year so you don't need the
Year function. This is what is causing your error 13.
If DLookup("[Year]", "tbl_inlog") < Year(Now()) Then
 
D

Dennis

When your form is in design mode, look at the form properties. One of them
will say On Load. Against this property, select [Event Procedure] from the
drop down list and then click the 3 little dots at the side of it. This will
put you into the code window for the On Load event for this form.

Speqter said:
I know that this is a stupid question but I'm having trouble finding the
answer:
Where do you put this code? Where is the "On Load Event"?

Thanks!
Ron


Dennis said:
Assuming your database starts by loading an opening form, in the On load
event for this form put something like the following code

If Year(DLookup("[YourDate]","YourTable")) < Year(Now()) then
DoCmd.OpenQuery "Your Append Query"
end if
Click to expand...
Click to expand...
 
Top