By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,369 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

CODE FOR QUERY?

P: n/a
hey guys,

Could someone please provide me with some code that would peform the
following:
I need a query to be done everytime the Database is opened that checks
a field "DueDate", if the current Date falls between the two months
prior to the "DueDate", then it will run a command that I have written,
titled "command38", this command button can be found on each record, so
if the dueDate is within the next two months then I want to database to
execute this command38! any suggestions?

Jun 1 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
If you want to stay with macros - you can use the Autoexec macro. This
macro executes only when An Access mdb is opened. You can create this
macro by opening a new macro in the macro window - choosing what
commands you want it to run and then just save the macro with the name

Autoexec

That is how you create an Autoexec macro. In the macro designer - you
have a dropdown with several selections of stuff you can perform.

If you want to use VBA, you can use the Access StartUp properties - go
to the Tools Menu/Startup and select a form to open. When the form
opens you can write some code in the Form_Open event or Form_load event
to do stuff like check your DueDate. Here is how you would do that.

First, I assume DueDate is a field in a table with some date and that is
the only field in that table and there is only one row/one column which
contains this duedate (if that is not the case - you need to change your
application so that it is the case - this is the conventional way to
store a unique date). In the Form_Load event you can write code like
this:

Private Sub Form_Load()
Dim RS As DAO.Recordset
Set RS = CurrentDB.OpenRecordset("Select DueDate From yourTable")

If DateDiff("m", RS(0), Date) < 2 Then
'do something - send an email, flag something...
End If
End Sub

RS is a recordset object. You set it to the table that contains your
Date Info. RS(0) refers to the first field in the table - 0 refers to
the ordinal number of the first field (everythings starts with 0 in VB).
If a table has more than one field then RS(0) is the first field, RS(1)
is the second field. RS.Fields.Count will return the normal count of
all the fields in a table.

Note: If a table has 10 fields, RS.Fields.Count returns 10. But if you
want to reference the field count in a For Looop, you have to subtract
one column because the counter starts at 0 -- 0 to 9 (10 fields)
0,1,2,3,4,5,6,7,8,9

For i = 0 to RS.Fields.Count - 1
'--Do something
Next

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.