This code doesn't work!

[Guest]

This drives me crazy. Someone tried to help me with the code for date check
on a subform, but I must not be getting something there.
Date is entered into the combo box on the form via a pop-up calendar (this
works fine. I got this from Martin Green website
http://www.fontstuff.com/access/acctut09.htm).

The problem is in the code of Before Update Event of the combo box:
If Not IsNull(DLookup("[School_Date]", "tabDemerits", "[School_Date]
= #" & Me.cboSchool_Date & "#" Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

The code above will check to see if the date is already recorded. If the
DLookup does not return Null, that means the date is recorded. If so, the
message box will be displayed, the update will be canceled, and the cursor
will remain in the date control.

Table name [tanDemerits], field name in that table [School_Date], combo box
in the subform where date is entered [cboSchool_Date]

Help anyone?
Thanks
JPol

 

[John Vinson]

This drives me crazy. Someone tried to help me with the code for date check
on a subform, but I must not be getting something there.
Date is entered into the combo box on the form via a pop-up calendar (this
works fine. I got this from Martin Green website
http://www.fontstuff.com/access/acctut09.htm).

The problem is in the code of Before Update Event of the combo box:
If Not IsNull(DLookup("[School_Date]", "tabDemerits", "[School_Date]
= #" & Me.cboSchool_Date & "#" Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

The code above will check to see if the date is already recorded. If the
DLookup does not return Null, that means the date is recorded. If so, the
message box will be displayed, the update will be canceled, and the cursor
will remain in the date control.

Table name [tanDemerits], field name in that table [School_Date], combo box
in the subform where date is entered [cboSchool_Date]

As written your code is missing two right parentheses - I suspect you
either typed it in rather than copying it, or have never compiled the
routine!

Try

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] = #" & Me.cboSchool_Date & "#")) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

Assuming that tabDemerits is in fact the name of the table into which
you're recording attendance.

John W. Vinson[MVP]

 

[Douglas J. Steele]

In addition to John's advice, be aware that regardless of what you've set
your Short Date format to through Regional Settings, it must be in
mm/dd/yyyy format when you use the # delimiters. (Okay, this isn't 100%
true: you can use any unambiguous format, such as dd mmm yyyy or yyyy-mm-dd.
The point is, it won't work properly if your Short Date format is
dd/mm/yyyy)

Far safer is:

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] = " &_
Format(Me.cboSchool_Date, "\#mm\/dd\/yyyy\#"))) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

 

[Guest]

John,
It doesn’t work. I have changed the date format from long to short in both
the table and form. The date is displayed 6/6/2006. Also the date can not be
entered directly into the combo box, only by clicking on a date in a calendar.
JPol

This drives me crazy. Someone tried to help me with the code for date check
on a subform, but I must not be getting something there.
Date is entered into the combo box on the form via a pop-up calendar (this
works fine. I got this from Martin Green website
http://www.fontstuff.com/access/acctut09.htm).

The problem is in the code of Before Update Event of the combo box:
If Not IsNull(DLookup("[School_Date]", "tabDemerits", "[School_Date]
= #" & Me.cboSchool_Date & "#" Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

The code above will check to see if the date is already recorded. If the
DLookup does not return Null, that means the date is recorded. If so, the
message box will be displayed, the update will be canceled, and the cursor
will remain in the date control.

Table name [tanDemerits], field name in that table [School_Date], combo box
in the subform where date is entered [cboSchool_Date]

As written your code is missing two right parentheses - I suspect you
either typed it in rather than copying it, or have never compiled the
routine!

Try

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] = #" & Me.cboSchool_Date & "#")) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

Assuming that tabDemerits is in fact the name of the table into which
you're recording attendance.

John W. Vinson[MVP]

 

[Guest]

Douglas,
It doesn’t work. I have changed the date format from long to short in both
the table and form. The date is displayed 6/6/2006. Also the date can not be
entered directly into the combo box, only by clicking on a date in a
calendar. The code you have given has errors
( _ ).
JPol

In addition to John's advice, be aware that regardless of what you've set
your Short Date format to through Regional Settings, it must be in
mm/dd/yyyy format when you use the # delimiters. (Okay, this isn't 100%
true: you can use any unambiguous format, such as dd mmm yyyy or yyyy-mm-dd.
The point is, it won't work properly if your Short Date format is
dd/mm/yyyy)

Far safer is:

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] = " &_
Format(Me.cboSchool_Date, "\#mm\/dd\/yyyy\#"))) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This drives me crazy. Someone tried to help me with the code for date
check
on a subform, but I must not be getting something there.
Date is entered into the combo box on the form via a pop-up calendar (this
works fine. I got this from Martin Green website
http://www.fontstuff.com/access/acctut09.htm).

The problem is in the code of Before Update Event of the combo box:
If Not IsNull(DLookup("[School_Date]", "tabDemerits", "[School_Date]
= #" & Me.cboSchool_Date & "#" Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

The code above will check to see if the date is already recorded. If the
DLookup does not return Null, that means the date is recorded. If so,
the
message box will be displayed, the update will be canceled, and the cursor
will remain in the date control.

Table name [tanDemerits], field name in that table [School_Date], combo
box
in the subform where date is entered [cboSchool_Date]

Help anyone?
Thanks
JPol

 

[John Vinson]

John,
It doesn’t work.

In what way does it "not work"?

I have changed the date format from long to short in both
the table and form. The date is displayed 6/6/2006.

The format is irrelevant. A Date is just a number - it can be
formatted various ways.

Also the date can not be
entered directly into the combo box, only by clicking on a date in a calendar.
JPol

Your question said "Date is entered into the combo box". I assumed
that meant what it said - that you're using a Combo Box bound to a
date/time field. Evidently my assumption was wrong, but there's no way
to tell since I cannot see what you're doing, and you haven't
explained.

John W. Vinson[MVP]

 

[Douglas J Steele]

Yeah, you're correct: I seem to have missed a space before the _ on the
second line.

The format of the date in the table and in the form is irrelevant. Under the
covers, Access treats dates as 8 byte floating point numbers (the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
while the decimal portion represents the time as a fraction of a day).

How are you populating the field School_Date in table tabDemerits? If you're
using the Now function, it will include time, not just date, so you'll have
to compensate for that:

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] Between " & _
Format(Me.cboSchool_Date, "\#mm\/dd\/yyyy\#") & " And " & _
Format(Me.cboSchool_Date + 1, "\#mm\/dd\/yyyy\#"))) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

Looking a little close at that, though, it probably makes more sense to
reflect the content of the combo box in the message:

MsgBox "Attendance for " & Me.cboSchool_Date & " Already Recorded"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,
It doesn't work. I have changed the date format from long to short in both
the table and form. The date is displayed 6/6/2006. Also the date can not be
entered directly into the combo box, only by clicking on a date in a
calendar. The code you have given has errors
( _ ).
JPol

In addition to John's advice, be aware that regardless of what you've set
your Short Date format to through Regional Settings, it must be in
mm/dd/yyyy format when you use the # delimiters. (Okay, this isn't 100%
true: you can use any unambiguous format, such as dd mmm yyyy or yyyy-mm-dd.
The point is, it won't work properly if your Short Date format is
dd/mm/yyyy)

Far safer is:

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] = " &_
Format(Me.cboSchool_Date, "\#mm\/dd\/yyyy\#"))) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This drives me crazy. Someone tried to help me with the code for date
check
on a subform, but I must not be getting something there.
Date is entered into the combo box on the form via a pop-up calendar (this
works fine. I got this from Martin Green website
http://www.fontstuff.com/access/acctut09.htm).

The problem is in the code of Before Update Event of the combo box:
If Not IsNull(DLookup("[School_Date]", "tabDemerits", "[School_Date]
= #" & Me.cboSchool_Date & "#" Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

The code above will check to see if the date is already recorded. If the
DLookup does not return Null, that means the date is recorded. If so,
the
message box will be displayed, the update will be canceled, and the cursor
will remain in the date control.

Table name [tanDemerits], field name in that table [School_Date], combo
box
in the subform where date is entered [cboSchool_Date]

Help anyone?
Thanks
JPol

 

[Guest]

John,
Nothing happens. I am duplicating the date without any pop-ups, just like
there is no code. I am populating cboSchool_Date by pressing a date button on
a pop-up calendar.
Mouse Down cboSchool_Date Code:
Private Sub cboSchool_Date_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
ocxCalendar0.Visible = True
ocxCalendar0.SetFocus
If Not IsNull(cboSchool_Date) Then
ocxCalendar0.Value = cboSchool_Date.Value
Else
ocxCalendar0.Value = Date
End If
End Sub

Calendar on Click Code:
Private Sub ocxCalendar0_Click()
cboSchool_Date.Value = ocxCalendar0.Value
cboSchool_Date.SetFocus
ocxCalendar0.Visible = False
End Sub

That is how its done.
JPol

 

[Guest]

Douglas,
Nothing happens. I am duplicating the date without any pop-ups, just like
there is no code. I am populating cboSchool_Date by pressing a date button on
a pop-up calendar.
Mouse Down cboSchool_Date Code:
Private Sub cboSchool_Date_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
ocxCalendar0.Visible = True
ocxCalendar0.SetFocus
If Not IsNull(cboSchool_Date) Then
ocxCalendar0.Value = cboSchool_Date.Value
Else
ocxCalendar0.Value = Date
End If
End Sub

Calendar on Click Code:
Private Sub ocxCalendar0_Click()
cboSchool_Date.Value = ocxCalendar0.Value
cboSchool_Date.SetFocus
ocxCalendar0.Visible = False
End Sub

That is how its done.
JPol

Yeah, you're correct: I seem to have missed a space before the _ on the
second line.

The format of the date in the table and in the form is irrelevant. Under the
covers, Access treats dates as 8 byte floating point numbers (the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
while the decimal portion represents the time as a fraction of a day).

How are you populating the field School_Date in table tabDemerits? If you're
using the Now function, it will include time, not just date, so you'll have
to compensate for that:

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] Between " & _
Format(Me.cboSchool_Date, "\#mm\/dd\/yyyy\#") & " And " & _
Format(Me.cboSchool_Date + 1, "\#mm\/dd\/yyyy\#"))) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

Looking a little close at that, though, it probably makes more sense to
reflect the content of the combo box in the message:

MsgBox "Attendance for " & Me.cboSchool_Date & " Already Recorded"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,
It doesn't work. I have changed the date format from long to short in both
the table and form. The date is displayed 6/6/2006. Also the date can not be
entered directly into the combo box, only by clicking on a date in a
calendar. The code you have given has errors
( _ ).
JPol

In addition to John's advice, be aware that regardless of what you've set
your Short Date format to through Regional Settings, it must be in
mm/dd/yyyy format when you use the # delimiters. (Okay, this isn't 100%
true: you can use any unambiguous format, such as dd mmm yyyy or yyyy-mm-dd.
The point is, it won't work properly if your Short Date format is
dd/mm/yyyy)

Far safer is:

If Not IsNull(DLookup("[School_Date]", "tabDemerits", _
"[School_Date] = " &_
Format(Me.cboSchool_Date, "\#mm\/dd\/yyyy\#"))) Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


This drives me crazy. Someone tried to help me with the code for date
check
on a subform, but I must not be getting something there.
Date is entered into the combo box on the form via a pop-up calendar (this
works fine. I got this from Martin Green website
http://www.fontstuff.com/access/acctut09.htm).

The problem is in the code of Before Update Event of the combo box:
If Not IsNull(DLookup("[School_Date]", "tabDemerits", "[School_Date]
= #" & Me.cboSchool_Date & "#" Then
MsgBox "Attendance for " & Me.School_Date & " Already Recorded"
Cancel = True
End If

The code above will check to see if the date is already recorded. If the
DLookup does not return Null, that means the date is recorded. If so,
the
message box will be displayed, the update will be canceled, and the cursor
will remain in the date control.

Table name [tanDemerits], field name in that table [School_Date], combo
box
in the subform where date is entered [cboSchool_Date]

Help anyone?
Thanks
JPol