Error

[Steve]

I get an error when I try to run it. The error is on the
first line (Compile error - user-defined type not defined)
I understand the code but am very new to this access
environment. I put the code in a module (access 2002)
and called it Sub d(). How do I get this to run
automatically when the db opens? call it in an autoexec
macro?

Thanks Maurice

Sub d()

Dim mydb As Database
Dim myset As Recordset
Dim i
Dim firstdate As Date
Dim lastdate As Date
firstdate = Date
lastdate = Date + 14
Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("table1")

For i = firstdate To lastdate
myset.AddNew
myset![BookingDate] = i
myset.Update
Next i
myset.Close
MsgBox "Date creation done..."

End Sub

 

[Dave]

To get code to run when you db starts add it to the onopen event of a form
and set that form to be the startup form
just make sure the form does not get closed and reopened during normal use
otherwise it will run the code again

the code should un ok then
But if you want only 14 dates then change
lastdate = Date + 14
to
lastdate = Date + 13

Dave

 

[Martin Seelhofer]

Hi Maurice

I get an error when I try to run it. The error is on the
first line (Compile error - user-defined type not defined)
I understand the code but am very new to this access
environment. I put the code in a module (access 2002)
and called it Sub d(). How do I get this to run
automatically when the db opens? call it in an autoexec
macro?
[...]
Dim mydb As Database
Dim myset As Recordset
[...]

The error comes most probably from a missing reference to
the DAO-object library (Access 2002 uses ADO by default
and leaves out the DAO-reference).

To add a reference to DAO, open the module, goto Tools-
References and select "Microsoft DAO 3.6" (or something like
that). Afterwards, your code should work properly...


Cheers,

Martin

 

[Another Error]

There is a type mismatch error on the following line.

Set myset = mydb.OpenRecordset("table1")

I created [table1] with attribute [BookingDate](date/time
data type)

Any clues. Thanks again.

 

[Allen Browne]

Above that line, enter:
Dim myset as DAO.Recordset

Then choose Compile from the Debug menu.
If it fails to compile, choose References from the Tools menu, and check the
box beside:
Microsoft DAO 3.6 Library.

Explanation:
Both the ADO and DAO libraries have a Recordset object.
Since you appear to be opening a recordset on a Database object, you need to
explicitly request a DAO recordset.

More about references:
http://allenbrowne.com/ser-38.html

 

[John Spencer (MVP)]

I would make several changes to this routine.

Change it to a function so you can call it from an autoexec macro on startup.

Declare your objects as DAO objects (make sure you have a reference to the DAO library)

Loop through and set the dates as shown (your code was setting the dates to
dates in the year 1900.

Also, I am assuming that what you would want is to DELETE any existing records
in Table1. Your code does not do that.

Plus there is no error checking code in this routine.

Sample code.

Public Function d()

Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim i as Integer

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("table1")

MyDb.Execute "DELETE * FROM Table1", dbfailOnError


For i = 0 to 13
myset.AddNew
myset![BookingDate] = Date + i
myset.Update
Next i

myset.Close
MsgBox "Date creation done..."

End Function

To get it to run on startup I would either use an autoexec macro to call it OR
have a form load on startup and use the form's load event to call the function.

I still prefer the solution I posted earlier, but if this is what you want then
this is what you want.