Validation from multiple records

[jdlewin1]

Hi,

I am building a database to check out and check in equipment as a log (like a lending library). One aspect is to reserve equipment days, weeks, months in advance. I have the system set up to do this.

My issues is when the user wants to reserve some equipment they fill out a from with Job Number, Check Out Date, Due Date, Contact. Which is then added to a table called Reservations. This table is filtered against the equipment and any reservations already booked are displayed in a subform.

I want the Check Out Date to have a validation so that you can not select a date which lies between any of the Check Out Date (3 days before) and Due Dates (1 day after) in the reservations table.

I can get it to use the first record in the table to validate the entry by using the Validation property on the text box (see below).....

Not Between [Reservations subform].[Form]![Checked Out Date]-3 And [Reservations subform].[Form]![Due Date]+1

....How can i get it to use all the records so effectively the equipment can not be booked out once it is already reserved as shown in the validation text above.

Many thanks for any help :banghead:

 

[pbaldy]

This might help:

http://www.baldyweb.com/OverLap.htm

I think you need to check both dates, so I would likely do it after both are entered, rather than with a validation rule on the first.

 

[jdlewin1]

thanks for the post.....im not great with VBA etc how would i use that to make the text box only allow a date to be selected thats outside the ones in the table?

 

[pbaldy]

"A date"? You're not thinking far enough ahead, but you can use DCount() to test whether a given date falls between existing dates in the table.

http://www.theaccessweb.com/general/gen0018.htm

 

[jdlewin1]

yes A Date. The user adds the date they want to check the equipment out and then a second for when they expect it back in.

What i want it to do is not allow a date (and 4 days before) to be added to the text box (Checked Out Date) on the form which would already be in the reservation table as reserved. And then the same for the Due Date (and 1 day after).

As stated i managed it with just using the validation property on the text box but that only checks it against the dates in the first record of the reservations where as i need it to do that for all records in the filtered reservation table.

Sorry im just not getting it haha

 

[pbaldy]

Okay, let's use your logic and say you have an existing record that reserves a piece of equipment from May 1 through May 10. You enter a start date for a new reservation for April 25. That's not within an existing reservation plus 4 days, so no problem. You enter an end date of May 15, which is also not within an existing reservation, so again no problem...right?

Like I said, if you want to continue with your method, look at the DCount() and test each date using your criteria.

 

[jdlewin1]

Ah yes I see that would be an issue. Any chance you can put a solution down in novice terms for me to implement lol

 

[jdlewin1]

i think i made a little progress on this. I now have a query which will use the Check Out Date & Due Date and see if their is any overlaps in the Reservation table for that asset.

I have pulled the SQL string from the query and want to set it as a recordset. If i run a record count on that record set and it comes back as 0 records then the dates are ok to enter and the user can continue. If the recordset is not 0 and records exist in the recordset then a message appears and the dates need to be changed.

This is what i have so far but it throws up an error.........

any HELP..........


Private Sub Due_Date_AfterUpdate()

On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Reservations.Asset, Reservations.[Checked Out Date], Reservations.[Due Date]" _
& "FROM Reservations" _
& "WHERE (((Reservations.Asset)=Forms!Reserve!Asset) And ((Reservations.[Checked Out Date])<=Forms!Reserve![Due Date]+2)" _
& "And ((Reservations.[Due Date])>=Forms.Reserve![Checked Out Date]-2));")

If rs.EOF Then
FindRecordCount = 0
Else
rs.MoveLast
FindRecordCount = rs.RecordCount
MsgBox ("Date Not Available - Equipment Already Booked Out. Please select another date or piece of equipment.")
End If


Set rs = Nothing
Set db = Nothing

ExitSub:
Exit Sub

ErrorHandler:
MsgBox "There has been an error. Please reload the form"
Resume ExitSub

End Sub

 

[Cronk]

BTW, you'll get an error when you try to open the recordset because your sql does not contain necessary spaces

SELECT Reservations.Asset, Reservations.[Checked Out Date], Reservations.[Due Date]FROM ReservationsWHERE (((Reservations.Asset)=Forms!Reserve!Asset) And ((Reservations.[Checked Out Date])<=Forms!Reserve![Due Date]+2)And ((Reservations.[Due Date])>=Forms.Reserve![Checked Out Date]-2));

 

[jdlewin1]

hi Cronk. The code in the vba does have the spaces in the copy and paste altered it for some reason.

strSQL = "SELECT Reservations.Asset, Reservations.Checked Out Date, Reservations.Due Date " _
& "FROM Reservations " _
& "WHERE (((Reservations.Asset)=Forms!Reserve!Asset) And ((Reservations.Checked Out Date)<=Forms!Reserve!Due Date+2) And ((Reservations.Due Date)>=Forms.Reserve!Checked Out Date-2));"

i think this part works. I need to basically say if the recordset is empty (no records) then its ok to proceed with the entry. If there are records then msg flags up and does not allow entry into the table.


Any thoughts as the rest fo the code doesn't seem to work for this bit

 

[Cronk]

"i think this part works"
Did you test it with data that both gives a positive and negative result?

"rest fo the code doesn't seem to work"
What does not seem to work? Which line gives the error? Have you stepped through the code?

What's the purpose of FindRecordCount? You can just test rs.recordcount. If it's greater than zero, there is at least one record, otherwise there are no records.

 

[jdlewin1]

it worked in query form....not sure how to test in it vba form.....

I have slighted amended the code now to use the .recordcount (again i think thats the correct usage. i ran through it by dropping the red dot on the private sub line and the yellow band moves onto the Error Handler, then on the strSQL, then Set db = CurrentDb and Set rs = db.OpenRecordset(strSQL)....then it jumps to the error handler at the bottom.

If the record set is not empty then i want the entry to stop and then delete the dates (Checked Out Date & Due Date fields on the form which is controled by the Reservation table) so they have to select another valid date.


Code below......PLEASE SOMEONE HELP....lol :banghead:



Private Sub Due_Date_AfterUpdate()

On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String


strSQL = "SELECT Reservations.Asset, Reservations.Checked Out Date, Reservations.Due Date " _
& "FROM Reservations " _
& "WHERE (((Reservations.Asset)=Forms!Reserve!Asset) And ((Reservations.Checked Out Date)<=Forms!Reserve!Due Date+2) And ((Reservations.Due Date)>=Forms.Reserve!Checked Out Date-2));"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


If rs.RecordCount <> 0 Then
MsgBox ("Date Not Available - Equipment Already Booked Out. Please select another date or piece of equipment.")
Cancel = True
End If


ExitSub:
Exit Sub

ErrorHandler:
MsgBox "There has been an error. Please reload the form"
Resume ExitSub

End Sub

 

[Cronk]

If you replace the error message with

MsgBox "There has been an error. Please reload the form" * " Error:" & error

you will get a description of the error occurring.

There is obviously something wrong with your sql. Add the following line before you open the recordset

debug.print strSQL

That will show you what you are generating. Paste the sql into the sql window of a new query and try to open the query. That will give you a hint about what is wrong.

Incidentally, I see you have dropped the square brackets around the field names with spaces. Experiened Access developers will normally never have field names with spaces.

 

[jdlewin1]

I added the error coding and it returns Run-time Error '13': Type Missmatch

The Debug.Print strSQL returns the below in the Immediate window:

SELECT Reservations.Asset, Reservations.[Checked Out Date], Reservations.[Due Date] FROM Reservations WHERE (((Reservations.Asset)=Forms!Reserve!Asset) And ((Reservations.[Checked Out Date])<=Forms!Reserve![Due Date]+2) And ((Reservations.[Due Date])>=Forms.Reserve![Checked Out Date]-2));

I have added the brackets back in on the sql.....

 

[jdlewin1]

i copied the sql string into the sql query and saved it as a test then ran it from a button on the form once the date fields had been filled in and it showed 1 record which the dates overlapped which is what i was expecting to see so seems like the sql statement is ok?

 

[Cronk]

Are you sure the code you are posting is what is in your procedure? I'd expect

Forms.Reserve!...

would generate an error. It should be Forms!Reserve....

Also you need to evaluate the parameter values eg
& "WHERE (((Reservations.Asset)=" & Forms!Reserve!Asset) & " And ((Reservations.Checked Out Date)<=" & Forms!Reserve!Due Date+2) & " And ((Reservations.Due Date)>=" & Forms.Reserve!Checked Out Date-2 & "));"

If Reserve.Asset is a text field you will need to insert single quotes around the value and the dates need to have # around them and be in mm/dd/yyyy format.

 

[Galaxiom]

strSQL = "SELECT Reservations.Asset, Reservations.Checked Out Date, Reservations.Due Date " _
& "FROM Reservations " _
& "WHERE (((Reservations.Asset)=Forms!Reserve!Asset) And ((Reservations.Checked Out Date)<=Forms!Reserve!Due Date+2) And ((Reservations.Due Date)>=Forms.Reserve!Checked Out Date-2));"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

Currentdb is the database. Its scope does not include the Application, where the Forms and their controls are located.

You must concatenate the values of the controls into strSQL.

 

[Galaxiom]

Are you sure the code you are posting is what is in your procedure? I'd expect

Forms.Reserve!...

would generate an error. It should be Forms!Reserve....

No. The items in the Forms Collection can be referenced via the dot.

 

[Cronk]

No. The items in the Forms Collection can be referenced via the dot.

That comment set me back because I'd been bitten once before on this when working on an update of a working system I had not developed.

There was a line with something like
forms.[My Form]!txtBox
which was giving an error.

Checking now, I still get an error if there is a space in the form name. However, no space, no problem.

 

[Galaxiom]

No. The items in the Forms Collection can be referenced via the dot.

That comment set me back because I'd been bitten once before on this when working on an update of a working system I had not developed.

Maybe something that changed between Access versions along the way?