spaces in table names and fields

[grinder332518]

My table is called "table A".
There is a date field in this table called "1st Date Issued".

My Form accesses this table, and I would like to write an expression which looks at all the entries in my table, and if "1st Date Issued" is more than 14 days ago, then to bring up a MsgBox.

I thought I would use an 'On Enter' event procedure, like

If [table A].[1st Date Issued] < (Date - 14) Then
Call MsgBox("OnEnter :- Date is before 14 days ago")
Cancel = True
Exit Sub
End If

But this gives me syntax errors.

I am new to Access, and all the training I have done has tables and fields without spaces in them.

Can someone give me the correct syntax please ?
And is the 'On Enter' event procedure the best way to handle this ?

Many thanks

 

[JPaulo]

try:

Private Sub Ctl1st_Date_Issued_AfterUpdate()
If Me.[1st Date Issued].Value < Date - 14 Then
MsgBox ("OnEnter :- Date is before 14 days ago")
DoCmd.CancelEvent
Me.Undo
Else
Exit Sub
End If
End Sub

 

[grinder332518]

Thanks JPaulo
This works beautifully when I amend the date, and then try to exit by clicking the X at the top left.
But is there a way to examine the existing value in the table when the row is first loaded in the Form, and then to output the message box, if appropriate ?

Best Regards

 

[JPaulo]

Or:

Private Sub Ctl1st_Date_Issued_AfterUpdate()
If Me.[1st Date Issued].Value < DateSerial(Year(Date), Month(Date), Day(Date) - 14) Then
MsgBox ("OnEnter :- Date is before 14 days ago")
DoCmd.CancelEvent
Me.Undo
Else
Exit Sub
End If
End Sub

 

[JPaulo]

use DLookup to check

If Me.[1st Date Issued].Value = DLookup("[1st Date Issued]", "[table A]", "ID = " & Me.ID) < DateSerial(Year(Date), Month(Date), Day(Date) - 14) Then

 

[grinder332518]

Hi JPaulo
I changed from AfterUpdate to Form_Current for the event, and it works great.
many thanks for your help.

 

[JPaulo]

Many thanks for your feed back.