Create a warning when dates are full

[Aggypants]

Being new to Access I'm not actually sure how to phrase this but here goes:

When booking lectures on particular dates I have a limited number of spaces
therefore I need a warning to come up saying a date is fully booked. I've
managed to create a query that adds up how many bookings there are for a
particular date, time and venue and get the warning to show but do not know
how to link this to the date on the inputting form. I'm using Access 2003.

 

[John W. Vinson]

Being new to Access I'm not actually sure how to phrase this but here goes:

When booking lectures on particular dates I have a limited number of spaces
therefore I need a warning to come up saying a date is fully booked. I've
managed to create a query that adds up how many bookings there are for a
particular date, time and venue and get the warning to show but do not know
how to link this to the date on the inputting form. I'm using Access 2003.

Use the Form's BeforeUpdate event to check. It can be cancelled if there are
too many entries. Something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iBookings As Integer
' count the number of bookings in a query
iBookings = DCount("*", "somequeryname", "somecriteria")
If iBookings > {the maximum} Then
MsgBox "Sorry, full up", vbOKOnly
Cancel = True
End If
End Sub

John W. Vinson [MVP]

 

[Aggypants]

Hi John

Thanks for replying to my post but I'm still lost, hopefully you can help me
some more.

I've typed the code in as you've written, including the correct query name,
however, I'm not sure what "somecriteria" means!

Anyhow, it does work, however, it seems to come up for any date and any
quantity, I know I'm doing something wrong but I just don't know what.

The query I'm running adds up a specific venue with dates and times to give
an overall total (LectureQty), I've tried including this as the
"somecriteria" but it makes no difference and every time I use {} these
symbols it comes up with a compile error: Invalid character - I need help
please.

 

[J_Goddard via AccessMonster.com]

Hi -

A couple of points to help you out.

first, since the query you are using is already a summation query, the you
don't want the DCount function, you need DLookup, something like this:

iBookings = Dlookup("[FieldName]","[queryName]","Criteria")

where FieldName is the name of the field in your query that contains the
total you want to check,
QueryName is the name of your query.

"Criteria" is a text string which specifies which record in the query you are
referring to. It is just like an SQL "where" clause, but without the word
where. It can refer to more than one field in the query, and in your case it
looks like it will need three - Date, Time and Venue - and because you are
using date, the syntax in the query is a bit tricky if the data type of the
field in the query is Type Date. Time and Venue are presumably text.

Your criteria string could be in a variable:

Dim CriteriaSQL as String

CriteriaSQL = "LectureDate = #" & format(me![LectureDate],"mm-dd-yyyy") & "#
and LectureTime = '" & me![LectureTime] & "' and Venue = '" & me!
[LectureVenue] & "'"

So you would use:
iBookings = Dlookup("[FieldName]","[queryName]",CriteriaSQL)

when John V. writes {the maximum}, he means that you have to replace {the
maximum} with a number that is the actual number of places available. If
this is a constant value, then you can just put it in, but if it can vary -
which I suspect will be true if you have different venues - then you have to
find a way to to that.

Hint: A Lookup on a table listing Venues and places available in each might
be used.

HTH

John G.

For John V. : This is what I would do; you may well know of an easier way to
do this - do let us know!

J.

Hi John

Thanks for replying to my post but I'm still lost, hopefully you can help me
some more.

I've typed the code in as you've written, including the correct query name,
however, I'm not sure what "somecriteria" means!

Anyhow, it does work, however, it seems to come up for any date and any
quantity, I know I'm doing something wrong but I just don't know what.

The query I'm running adds up a specific venue with dates and times to give
an overall total (LectureQty), I've tried including this as the
"somecriteria" but it makes no difference and every time I use {} these
symbols it comes up with a compile error: Invalid character - I need help
please.
[quoted text clipped - 18 lines]

John W. Vinson [MVP]

 

[Aggypants]

Hello - Madam Thickie back again, I'm obviously trying to do something way
above my abilities and the brain cell is not working, I still need help on
this same item.

The problem I have now is: when I enter the date the following comes up:

Run-time error '438'
Object doesn't support this property or method.

The code I've put in is:

Private Sub Lecture_Date_BeforeUpdate(Cancel As Integer)
Dim iBookings As Integer
' count the number of bookings in a query
Dim CriteriaSQL As String
CriteriaSQL = "Lecture Date = 25" & Format(Me![Lecture Date], "dd-mm-yyyy")
& "25 and Lecture Time = '" & Format(Me![Lecture Time], "12:00am") & "' and
Venue = '" & Me![Venue of Lecture] & "'"
iBookings = DLookup("[LectureQty]", "[Total Lecture for QMH Warning]",
CriteriaSQL)
If iBookings >= 25 Then
MsgBox "Sorry, Lecture Date Full", vbOKOnly
Cancel = True
End If
End Sub

I've checked the names of the fields etc and they seem fine, I'm still lost,
it's incredibly frustrating not having anyone at work who can show me how
it's done so I appreciate all the help I'm currently being given, thank you.

Hi -

A couple of points to help you out.

first, since the query you are using is already a summation query, the you
don't want the DCount function, you need DLookup, something like this:

iBookings = Dlookup("[FieldName]","[queryName]","Criteria")

where FieldName is the name of the field in your query that contains the
total you want to check,
QueryName is the name of your query.

"Criteria" is a text string which specifies which record in the query you are
referring to. It is just like an SQL "where" clause, but without the word
where. It can refer to more than one field in the query, and in your case it
looks like it will need three - Date, Time and Venue - and because you are
using date, the syntax in the query is a bit tricky if the data type of the
field in the query is Type Date. Time and Venue are presumably text.

Your criteria string could be in a variable:

Dim CriteriaSQL as String

CriteriaSQL = "LectureDate = #" & format(me![LectureDate],"mm-dd-yyyy") & "#
and LectureTime = '" & me![LectureTime] & "' and Venue = '" & me!
[LectureVenue] & "'"

So you would use:
iBookings = Dlookup("[FieldName]","[queryName]",CriteriaSQL)

when John V. writes {the maximum}, he means that you have to replace {the
maximum} with a number that is the actual number of places available. If
this is a constant value, then you can just put it in, but if it can vary -
which I suspect will be true if you have different venues - then you have to
find a way to to that.

Hint: A Lookup on a table listing Venues and places available in each might
be used.

HTH

John G.

For John V. : This is what I would do; you may well know of an easier way to
do this - do let us know!

J.

Hi John

Thanks for replying to my post but I'm still lost, hopefully you can help me
some more.

I've typed the code in as you've written, including the correct query name,
however, I'm not sure what "somecriteria" means!

Anyhow, it does work, however, it seems to come up for any date and any
quantity, I know I'm doing something wrong but I just don't know what.

The query I'm running adds up a specific venue with dates and times to give
an overall total (LectureQty), I've tried including this as the
"somecriteria" but it makes no difference and every time I use {} these
symbols it comes up with a compile error: Invalid character - I need help
please.

Being new to Access I'm not actually sure how to phrase this but here goes:

[quoted text clipped - 18 lines]

John W. Vinson [MVP]

 

[J_Goddard via AccessMonster.com]

Hi -

I think the problem is in the CriteriaSQL statement:
- I don't see why the 25's are in there - they should be # marks. This is
how dates are delimited in cases like this.
- Since Lecture Date and Lecture Time contain blanks in the field names,
they must be enclosed in []
- If the data type of the Lecture Time field in your table is 'text', then
you can't use the Format function with it.

Try this:

CriteriaSQL = "[Lecture Date] = #" & Format(Me![Lecture Date], "dd-mm-yyyy")
& "# and [Lecture Time] = '" & Me![Lecture Time] & "' and
Venue = '" & Me![Venue of Lecture] & "'"

John

Hello - Madam Thickie back again, I'm obviously trying to do something way
above my abilities and the brain cell is not working, I still need help on
this same item.

The problem I have now is: when I enter the date the following comes up:

Run-time error '438'
Object doesn't support this property or method.

The code I've put in is:

Private Sub Lecture_Date_BeforeUpdate(Cancel As Integer)
Dim iBookings As Integer
' count the number of bookings in a query
Dim CriteriaSQL As String
CriteriaSQL = "Lecture Date = 25" & Format(Me![Lecture Date], "dd-mm-yyyy")
& "25 and Lecture Time = '" & Format(Me![Lecture Time], "12:00am") & "' and
Venue = '" & Me![Venue of Lecture] & "'"
iBookings = DLookup("[LectureQty]", "[Total Lecture for QMH Warning]",
CriteriaSQL)
If iBookings >= 25 Then
MsgBox "Sorry, Lecture Date Full", vbOKOnly
Cancel = True
End If
End Sub

I've checked the names of the fields etc and they seem fine, I'm still lost,
it's incredibly frustrating not having anyone at work who can show me how
it's done so I appreciate all the help I'm currently being given, thank you.

Hi -

[quoted text clipped - 67 lines]

 

[Douglas J. Steele]

There are a few problems with your code, but I don't see one that should
raise that specific error.

Issues, though, include the fact that date and time values need to be
delimited with #, not '. (I have no idea what those 25s are supposed to be
in your code!), dates need to be in mm/dd/yyyy format, regardless of what
your Short Date format may have been set to through Regional Settings, and
DLookup will return a Null value if no rows exist for the given criteria,
and integer fields cannot be assigned Null.

See if this works any better:

Private Sub Lecture_Date_BeforeUpdate(Cancel As Integer)
Dim iBookings As Integer
' count the number of bookings in a query
Dim CriteriaSQL As String

CriteriaSQL = "Lecture Date = " & _
Format(Me![Lecture Date], "\#mm\/dd\/yyyy\#") & _
" and Lecture Time = " & _
Format(Me![Lecture Time], "\#hh\:nn\#") & _
" and Venue = '" & Me![Venue of Lecture] & "'"
iBookings = Nz(DLookup("[LectureQty]", _
"[Total Lecture for QMH Warning]", _
CriteriaSQL), 0)
If iBookings >= 25 Then
MsgBox "Sorry, Lecture Date Full", vbOKOnly
Cancel = True
End If

End Sub

Actually, I may have found the cause of the error. "12:00am" isn't a valid
format property.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello - Madam Thickie back again, I'm obviously trying to do something way
above my abilities and the brain cell is not working, I still need help on
this same item.

The problem I have now is: when I enter the date the following comes up:

Run-time error '438'
Object doesn't support this property or method.

The code I've put in is:

Private Sub Lecture_Date_BeforeUpdate(Cancel As Integer)
Dim iBookings As Integer
' count the number of bookings in a query
Dim CriteriaSQL As String
CriteriaSQL = "Lecture Date = 25" & Format(Me![Lecture Date],
"dd-mm-yyyy")
& "25 and Lecture Time = '" & Format(Me![Lecture Time], "12:00am") & "'
and
Venue = '" & Me![Venue of Lecture] & "'"
iBookings = DLookup("[LectureQty]", "[Total Lecture for QMH Warning]",
CriteriaSQL)
If iBookings >= 25 Then
MsgBox "Sorry, Lecture Date Full", vbOKOnly
Cancel = True
End If
End Sub

I've checked the names of the fields etc and they seem fine, I'm still
lost,
it's incredibly frustrating not having anyone at work who can show me how
it's done so I appreciate all the help I'm currently being given, thank
you.

Hi -

A couple of points to help you out.

first, since the query you are using is already a summation query, the
you
don't want the DCount function, you need DLookup, something like this:

iBookings = Dlookup("[FieldName]","[queryName]","Criteria")

where FieldName is the name of the field in your query that contains the
total you want to check,
QueryName is the name of your query.

"Criteria" is a text string which specifies which record in the query you
are
referring to. It is just like an SQL "where" clause, but without the
word
where. It can refer to more than one field in the query, and in your
case it
looks like it will need three - Date, Time and Venue - and because you
are
using date, the syntax in the query is a bit tricky if the data type of
the
field in the query is Type Date. Time and Venue are presumably text.

Your criteria string could be in a variable:

Dim CriteriaSQL as String

CriteriaSQL = "LectureDate = #" & format(me![LectureDate],"mm-dd-yyyy") &
"#
and LectureTime = '" & me![LectureTime] & "' and Venue = '" & me!
[LectureVenue] & "'"

So you would use:
iBookings = Dlookup("[FieldName]","[queryName]",CriteriaSQL)

when John V. writes {the maximum}, he means that you have to replace {the
maximum} with a number that is the actual number of places available. If
this is a constant value, then you can just put it in, but if it can
vary -
which I suspect will be true if you have different venues - then you have
to
find a way to to that.

Hint: A Lookup on a table listing Venues and places available in each
might
be used.

HTH

John G.

For John V. : This is what I would do; you may well know of an easier way
to
do this - do let us know!

J.

Hi John

Thanks for replying to my post but I'm still lost, hopefully you can
help me
some more.

I've typed the code in as you've written, including the correct query
name,
however, I'm not sure what "somecriteria" means!

Anyhow, it does work, however, it seems to come up for any date and any
quantity, I know I'm doing something wrong but I just don't know what.

The query I'm running adds up a specific venue with dates and times to
give
an overall total (LectureQty), I've tried including this as the
"somecriteria" but it makes no difference and every time I use {} these
symbols it comes up with a compile error: Invalid character - I need
help
please.

Being new to Access I'm not actually sure how to phrase this but here
goes:

[quoted text clipped - 18 lines]

John W. Vinson [MVP]