Check Records After Update

[Chris]

I need to check the records in a subform after someone enters the information
(AfterUpdate on the last field I am assuming where the code will be checked).
I need to check on the status field (Field could be OUT, IN, RES, CX, adn
NS) for RES and the CHECKOUT and CHECKIN Dates. The purpose is to prevent a
reservation (RES) from being made with an existing reservation is in place.
There can be multiple reservations but there cannot be overlapping
reservations. I am thinking my conditions would be to first check status for
any existing RES and then check the CHECKIN Date of the new reservation
versus the CHECKOUT date of the existing reservations. My problem is that I
do not know how to go through the records to check.

 

[Stuart McCall]

In general, the form's After Update event is where to perform validation.

Not true. When AfterUpdate fires, the record has already been saved
(updated). The BeforeUpdate event is the place for validation, then if the
validation fails, you can cancel the update like this:

Cancel = True

 

[Chris]

There is no actual reservation number. The reservation is identified by the
piece of equipment (each piece of equipment has a unique name). And the
piece of equipment can have multiple reservations (i.e. 3/15 to 3/19 and 3/22
to 3/29 but not 3/15 to 3/19 and 3/17 to 3/24). I hope this clairfies this.

 

[Chris]

Bruce,

The database is set up as you described. The equipment and reservations are
in different tables and the form does show the reservations. We've been
encountering problems with people still overlapping reservations, that's why
I would like a check to pop up a message box and alert the user.

We are using Access 2007 (2003 database) so we already have the calendar.

Please note that I erred in mentioning the form's After Update event. I
meant to say Before Update. This error was pointed out to me in another
posting.

Best would be to have Equipment in one table, with Reservations in a related
table. You would set up a form based on the Equipment table, with a
(continuous) subform based on the Reservations table. It may be simple
enough in that case to see just by looking if the equipment is reserved for
the time period in question. Another option may be to use a calendar control
to block off the dates. One example is here:
http://allenbrowne.com/ser-51.html

It would be possible to perform validation without using a calendar, but the
first thing is to get the related tables, and the form/subform, set up
correctly.

There is no actual reservation number. The reservation is identified by the
piece of equipment (each piece of equipment has a unique name). And the
piece of equipment can have multiple reservations (i.e. 3/15 to 3/19 and 3/22
to 3/29 but not 3/15 to 3/19 and 3/17 to 3/24). I hope this clairfies this.

In general, the form's After Update event is where to perform validation.

[quoted text clipped - 14 lines]

versus the CHECKOUT date of the existing reservations. My problem is that I
do not know how to go through the records to check.

--
Message posted via AccessMonster.com


.

 

[Mike Painter]

I need to check the records in a subform after someone enters the
information (AfterUpdate on the last field I am assuming where the
code will be checked). I need to check on the status field (Field
could be OUT, IN, RES, CX, adn NS) for RES and the CHECKOUT and
CHECKIN Dates. The purpose is to prevent a reservation (RES) from
being made with an existing reservation is in place. There can be
multiple reservations but there cannot be overlapping reservations.
I am thinking my conditions would be to first check status for any
existing RES and then check the CHECKIN Date of the new reservation
versus the CHECKOUT date of the existing reservations. My problem is
that I do not know how to go through the records to check.

You could form a three part key and make it unique. Then all you have to do
is intercept the warning message and put one of your own in.

There are problems with this.
What if a person checks in for a week without a reservation and somebody
tries to reserve the room during that period.
What if work is scheduled for that time period?

 

[Mike Painter]

I was going to tell you but now I have to blame my cat for writing the
message as it would only work on rare occasions.

If you have an end date for an item that is greater than the new start date
I think that will indicate a conflict in all cases. The start date doe not
matter.
So a query or dLookup with " ItemID = " & me.itemID & " EndDate >= #" &
me.startDate & "#"

should work

unless Iget another idea after I push send.

The reservations are for equipment, not rooms, if that matters. If
it does not, what are the three fields in the key? I tried to
picture something of the sort, but could not do so in a way that
accomodated overlapping reservations.

I need to check the records in a subform after someone enters the
information (AfterUpdate on the last field I am assuming where the

[quoted text clipped - 7 lines]

versus the CHECKOUT date of the existing reservations. My problem
is that I do not know how to go through the records to check.

You could form a three part key and make it unique. Then all you
have to do is intercept the warning message and put one of your own
in.

There are problems with this.
What if a person checks in for a week without a reservation and
somebody tries to reserve the room during that period.
What if work is scheduled for that time period?

 

[Chris]

Bruce,

I tried the code but it failed. At one point I received the Error Code
2471. Part of the problem was the quotes. It should open with a single
quote and double quote ' " and close with a double quote and single quote "
'. However, even this fix that allowed the code to run did not produce the
desired results. I was still able to enter overlapping reservations without
any message box appearing. I did update the table and field names to match
our database.

You should be able to do something like this in the form's Before Update
event:

If DCount("*","tblReservations","[EquipID] = " & Me.EquipID & _
" AND [Checkout] < #" & Me.Checkout & _
"# AND [Checkout] > #" & Me.Checkin & "#") > 0 Then
MsgBox "Checkout date is taken"
Cancel = True
Else
If DCount("*","tblReservations","[EquipID] = " & Me.EquipID & _
" AND [Checkin] < #" & Me.Checkout & _
"# AND [Checkin] > #" & Me.Checkin & "#") > 0 Then
MsgBox "Checkin date is taken"
Cancel = True
End If

This assumes EquipID is a number field for the Equipment record. If you are
using a text description only it would be (I think I have the quotes arranged
correctly):

If DCount("*","tblReservations","[Equip] = " " " & Me.Equip & _
" " " AND [Checkout] < #" & Me.Checkout & _
"# AND [Checkout] > #" & Me.Checkin & "#") > 0 Then
etc.

There may be a simpler way, but the idea is you are counting records in which
either the checkout date or checkin date is within the checkout/checkin range
of another record for the piece of equipment. The example assumes EquipID or
Equip is the linking field between the Equipment table and the Reservations
table (tblReservations).

Bruce,

The database is set up as you described. The equipment and reservations are
in different tables and the form does show the reservations. We've been
encountering problems with people still overlapping reservations, that's why
I would like a check to pop up a message box and alert the user.

We are using Access 2007 (2003 database) so we already have the calendar.

Please note that I erred in mentioning the form's After Update event. I
meant to say Before Update. This error was pointed out to me in another

[quoted text clipped - 22 lines]

versus the CHECKOUT date of the existing reservations. My problem is that I
do not know how to go through the records to check.

--
Message posted via AccessMonster.com


.

 

[Chris]

One other issue with this database is that the original creators put spaces
in the field names. How do I account for that in the code Me.EquipID? (Our
code would be Me.Equipment Name)

Unfortunately we cannot change the field names in this databsase.

You should be able to do something like this in the form's Before Update
event:

If DCount("*","tblReservations","[EquipID] = " & Me.EquipID & _
" AND [Checkout] < #" & Me.Checkout & _
"# AND [Checkout] > #" & Me.Checkin & "#") > 0 Then
MsgBox "Checkout date is taken"
Cancel = True
Else
If DCount("*","tblReservations","[EquipID] = " & Me.EquipID & _
" AND [Checkin] < #" & Me.Checkout & _
"# AND [Checkin] > #" & Me.Checkin & "#") > 0 Then
MsgBox "Checkin date is taken"
Cancel = True
End If

This assumes EquipID is a number field for the Equipment record. If you are
using a text description only it would be (I think I have the quotes arranged
correctly):

If DCount("*","tblReservations","[Equip] = " " " & Me.Equip & _
" " " AND [Checkout] < #" & Me.Checkout & _
"# AND [Checkout] > #" & Me.Checkin & "#") > 0 Then
etc.

There may be a simpler way, but the idea is you are counting records in which
either the checkout date or checkin date is within the checkout/checkin range
of another record for the piece of equipment. The example assumes EquipID or
Equip is the linking field between the Equipment table and the Reservations
table (tblReservations).

Bruce,

The database is set up as you described. The equipment and reservations are
in different tables and the form does show the reservations. We've been
encountering problems with people still overlapping reservations, that's why
I would like a check to pop up a message box and alert the user.

We are using Access 2007 (2003 database) so we already have the calendar.

Please note that I erred in mentioning the form's After Update event. I
meant to say Before Update. This error was pointed out to me in another

[quoted text clipped - 22 lines]

versus the CHECKOUT date of the existing reservations. My problem is that I
do not know how to go through the records to check.

--
Message posted via AccessMonster.com


.

 

[Chris]

Bruce here is the code before running your debug:

Private Sub Expected_Return_AfterUpdate()
If DCount("*", "Reservations", "[Equipment Name] = ' " & Me.[Equipment Name]
& _
" ' AND [Checkout Date] < #" & Me.[Checkout Date] & _
"# AND [Checkout Date] > #" & Me.[Expected Return] & "#") > 0 Then
MsgBox "Checkout date is taken"
Cancel = True
Else
If DCount("*", "Reservations", "[Equipment Name] = " & Me.Equipment
Name & _
" AND [Expected Return] < #" & Me.[Checkout Date] & _
"# AND [Expected Return] > #" & Me.[Expected Return] & "#") > 0 Then
MsgBox "Checkin date is taken"
Cancel = True
End If
End If
End Sub

I'll run your debug code to find otu what the strings are. The 2471 said
the parameter OPIE (that was the name of the equipment) was the failed cause.

Failed in what way? Error 2471 suggests a problem with the parameter, but it
would have helped to see any accompanying verbiage. Using three double
quotes in a row should work the same as what you described, so the quotes
were not a problem unless the Equipment text itself contains a quotation mark.
Likewise, the apostrophe would not work if the text contains an apostrophe.

The code worked in my test, so I do not know why it does not work for you.
Please post your actual code, and the event in which it is running (should be
the form's Before Update event).

You could try something like this:

Dim strOut as String, strIn as String

strOut = ","[EquipID] = '" & Me.EquipID & _
"' AND [Checkout] < #" & Me.Checkout & _
"# AND [Checkout] > #" & Me.Checkin & "#"

Debug.Print strOut

strIn = "[EquipID] = '" & Me.EquipID & _
"' AND [Checkin] < #" & Me.Checkout & _
"# AND [Checkin] > #" & Me.Checkin & "#"

Debug.Print strIn

If DCount("*","tblReservations",strWhere) > 0 Then
MsgBox "Checkout date is taken"
Cancel = True
Else
If DCount("*","tblReservations",strIn) > 0 Then
MsgBox "Checkin date is taken"
Cancel = True
End If

After running the code, press Ctrl + G to open the immediate window. Check
the resulting strings to see if they are what they should be. In general, if
strings are giving you problems you should make sure the string is what you
expect it to be.

You may need to accomodate Nulls in the date fields unless you have taken
steps to prevent them.

For names (fields, controls, or whatever) with spaces or other special
characters, enclose the name in square brackets.

Bruce,

I tried the code but it failed. At one point I received the Error Code
2471. Part of the problem was the quotes. It should open with a single
quote and double quote ' " and close with a double quote and single quote "
'. However, even this fix that allowed the code to run did not produce the
desired results. I was still able to enter overlapping reservations without
any message box appearing. I did update the table and field names to match
our database.

You should be able to do something like this in the form's Before Update
event:

[quoted text clipped - 41 lines]

versus the CHECKOUT date of the existing reservations. My problem is that I
do not know how to go through the records to check.

 

[Chris]

Here is the string text from the Immediate Window:

[Equipment Name] = 'OPIE' AND [Expected Return] < #3/17/2010# AND [Expected
Return] > #3/29/2010#
[Equipment Name] = 'OPIE' AND [Checkout Date] < #3/17/2010# AND [Checkout
Date] > #3/28/2010#

Failed in what way? Error 2471 suggests a problem with the parameter, but it
would have helped to see any accompanying verbiage. Using three double
quotes in a row should work the same as what you described, so the quotes
were not a problem unless the Equipment text itself contains a quotation mark.
Likewise, the apostrophe would not work if the text contains an apostrophe.

The code worked in my test, so I do not know why it does not work for you.
Please post your actual code, and the event in which it is running (should be
the form's Before Update event).

You could try something like this:

Dim strOut as String, strIn as String

strOut = ","[EquipID] = '" & Me.EquipID & _
"' AND [Checkout] < #" & Me.Checkout & _
"# AND [Checkout] > #" & Me.Checkin & "#"

Debug.Print strOut

strIn = "[EquipID] = '" & Me.EquipID & _
"' AND [Checkin] < #" & Me.Checkout & _
"# AND [Checkin] > #" & Me.Checkin & "#"

Debug.Print strIn

If DCount("*","tblReservations",strWhere) > 0 Then
MsgBox "Checkout date is taken"
Cancel = True
Else
If DCount("*","tblReservations",strIn) > 0 Then
MsgBox "Checkin date is taken"
Cancel = True
End If

After running the code, press Ctrl + G to open the immediate window. Check
the resulting strings to see if they are what they should be. In general, if
strings are giving you problems you should make sure the string is what you
expect it to be.

You may need to accomodate Nulls in the date fields unless you have taken
steps to prevent them.

For names (fields, controls, or whatever) with spaces or other special
characters, enclose the name in square brackets.

Bruce,

I tried the code but it failed. At one point I received the Error Code
2471. Part of the problem was the quotes. It should open with a single
quote and double quote ' " and close with a double quote and single quote "
'. However, even this fix that allowed the code to run did not produce the
desired results. I was still able to enter overlapping reservations without
any message box appearing. I did update the table and field names to match
our database.

You should be able to do something like this in the form's Before Update
event:

[quoted text clipped - 41 lines]

versus the CHECKOUT date of the existing reservations. My problem is that I
do not know how to go through the records to check.

 

[Mike Painter]

One other issue with this database is that the original creators put
spaces in the field names. How do I account for that in the code
Me.EquipID? (Our code would be Me.Equipment Name)

Unfortunately we cannot change the field names in this databsase.

Put them in brackets. [My Field Name].
Allowing spaces seemd like such a good idea.
but [My Field Name] is not equal to [My Field Name]