VBA in Access XP

[Shane Nation]

I am using Access XP and on Open event in a form (FormList) I have placed
the following code:

Private Sub Form_Open(Cancel As Integer)
Dim stDate As Date
Dim stDocName As String
stDate = Now()
If stDate >= "18/07/04" Then
DoCmd.DeleteObject acTable, "LIST"
DoCmd.Close acForm, "FormList"
DoCmd.DeleteObject acForm, "FormList"
Else
End If
End Sub

It is to check if there is an out of date table and if so delete the table
and delete this form, so I can create another one linking it to the new
table.
However it all works fine, but the delete form, I get an error message
saying I can't delete the form while it is open. I guessed it was because
the code was within the form I was trying to delete, so I opened a second
form which had the code to delete the 1st, but I got the same message?

Pleasse can anyone assist?

 

[Shane Nation]

Thank you very much for your help, the code in the second
form is:
DoCmd.Close acForm, "FormList"
DoCmd.DeleteObject acForm, "FormList"

Is there a better way to compare Now()to a date?

Shane

 

[Brendan Reynolds \(MVP\)]

Thank you very much for your help, the code in the second
form is:
DoCmd.Close acForm, "FormList"
DoCmd.DeleteObject acForm, "FormList"

I can't be sure about this, because I have never wanted to delete a form at
run-time, but you could perhaps try putting a DoEvents between those two
lines ...

DoCmd.Close acForm, "FormList"
DoEvents
DoCmd.DeleteObject acForm, "FormList"

But again, my recommendation would be to change the RecordSource property
rather than deleting and recreating the form.

Is there a better way to compare Now()to a date?

The first problem is that you're not comparing Now() to a date at all,
you're comparing it to a string. In VBA, dates are not strings, they are
floating-point numbers in which the part of the number before the decimal
point represents the number of days since 30 December 1899 and the part of
the number after the decimal point represents the time as a fraction of a
24-hour day, eg. .0 is midnight, .25 is 6am, .5 is noon, and .75 is 6pm.

When you assign the result of a function that returns a date to a string
variable, you're letting VBA implicitly convert the date to a string, and it
may not convert it in the way you expect. For example, my PC is configured
to use dd/mm/yyyy format, with four-digit years, so todays date, converted
to a string, is "21/07/2003 10:53:35", and your test (>= "18/07/04") would
always return True on my PC.

There are two solutions to this. One, don't convert the result of the
function to a string, keep it as a date, and compare it to a date. To enter
a hard-coded date in VBA, surround it with # symbols and use US mm/dd/yyyy
format. Here's an example typed into the Immediate window ...

? Now() >= #07/18/2004#
False

The other is to convert the date to a string, but to do it explicitly, using
the Format$() function, so that you can better predict what the result will
be ...

? Format$(Now(),"dd/mm/yyyy")
21/07/2003

Finally, remember that Now() returns the time as well as the date. If you're
only interested in the date, Date() is usually a better choice. The result
will still include a time part, but it will always be 00:00:00.