Hi NeoPa
You wer right, we had crossed messages and I had not seen your advice. Have placed the code in my database and it appears to work fine. I did get a funny but I think it was relating to the fact that I was editing and re-editing the same record each time and this resulted in some funny results (think some variables had been left behind). I have set the event to close the form after each edit now in order that we get a full...
Cyd44
Last Activity: Nov 30 '11, 04:13 PM
Joined: Oct 16 '11
Location: Chester, Cheshire UK
-
OK neoPa I am working on a simple database for proof of concept and have the following table containing the two records only. You will see that I have used a long variable for StartNo and EndNo (being the BookTime and BookEndTime fields multiplied by 1440 to give a number of minutes. I substituted the StartNo and EndNo variables to see if it was the Time that was causing a problem.
...Code:BookID BookName BookTime BookStartDate BookNotes BookLocation
Leave a comment:
-
Hi NeoPa,
Thanks again. I have researched a number of algorithms for this and the one I favour most of all is the one I have posted above. I had originally thought this was OK but I then developed a test plan and had totally emptied the database of any records.
Part of my testing was to create 2 bookings (same date, same room) with a 2 hour gap between them. I then tried to add another booking to fit between the two...Leave a comment:
-
Ok Rabbit, this is driving me up the wall. I have now added <= and >= to the condition as I would like to be able to book a room when times = so End at 10Am for i booking and Start at 10Am for another.
Here is the current Table:-
...Code:BookStartDate BookLocation BookTime_ BookEndTime_ BookID 06/11/2011 Room 1 10:30 AM 01:30 PM 1 06/11/2011 Room 2 09:00 AM 06:30 PM 2 06/11/2011 Room
Leave a comment:
-
Validating a Booking between Date/Times
I am afraid I am back to an old question which I thought I had resolved. I am now UAT testing and have found that the logic of my SQL statement does not work.
I have the following records in a table and want to edit a record and validate the change before saving it. This is the result of the qryBookings that my Dcount function evaluates the SQL against.
...Code:BookStartDate BookLocation BookTime_ BookEndTime_ BookID
-
Thats very well explained.Many thanksLeave a comment:
-
Hi NeoPa
Thank you, I was aware that my problem was a Syntax one and I can see that I had missed out the =" & LoginID. I was not aware of the JetEngine parse requirement but am assuming the quotes an ampersand do this.
Thank you for your adviceLeave a comment:
-
Thank You SmileyCoder, that works just fine. I had tried almost every combination in the syntax but could not get it to work.
It does now......ThanksLeave a comment:
-
Syntax problem using DLookup
I am getting a runtime error 2471 on the expression LoginID using Dlookup and dont know how to correct it.
My code is:-
...Code:Dim LoginID As Integer Dim Priv As Variant LoginID = [basMyEmpID].lngMyEmpID On Error GoTo Ad_Err MsgBox (LoginID) Priv = DLookup("[strAccess]", _ "[tblEmployees]", _ "[lngEmpID] -
Thanks a lot guys. In the end I used a query to open the form by setting OpenForm "form" Filter = query
This works fine.
Thanks again guysLeave a comment:
-
Have look at Property Sheet control for each Date & Time variable and they are set correctly. I am not exactly sure waht you mean by look at Time Format under Regional Settings. I have assumed propert sheet data source controlsLeave a comment:
-
Did what you said and received Record Saved MessageLeave a comment:
-
I am using your Bookings_3 zip and I attach same so that you can see. I am simply using your form and selecting any of the (now) 3 records, changing time to conflict and it still says it is valid?
Dont know what I am doing wrong, but if you can make it work them I must be doing something wrong!!!!...Leave a comment:
-
I have gone back to your Zip and see there are 2 records for 11/3/2011 for Room 1.
Rec 1 is 8am - 2pm
Rec 2 is 2pm - 7pm
If I do not change anything I messages Not Saved so the Dirty is OK
If I change Rec 1 to finish at 3pm (not 2pm) it should conflict with Rec 2 which starts at 2pm. However it passes through the SQL and messages Record Saved. This is a nightmare isnt it?Leave a comment:
-
Sorry Guys but I spoke too soon and it is totaly illogical. When I spoke last night the procedure had invalidated a booking but I did not try a valid change.It appears that it will either Invalidate everything by putting the IF condition outside of the SQL or it will Validate everything if I put the ID <> MyId condition Inside the SQL.
I am thinking that the best solution here would be to take a copy of the Table before editing...Leave a comment:
-
ADezii,
Great news! I finally got it to work with a little alteration, here is the final code
...Code:If [BookID] <> Me.BookID Then strSQL = "([BookStartDate] = #%D#) AND " & _ "([BookLocation] = '%R') AND " & _ "([BookTime] < #%E#) AND " & _ "([BookEndTime] > #%S#)" strSQLLeave a comment:
-
Thanks ADezii,
I suspect it is because Access will automatically save a record when you move away from it to click the Save Edit button and therefore it is checking agianst itself! I also think Rabbit is correct as we need to eliminate the pre-edit from the SQL check.
I will try your recommeded solution and get back to you.This might be tomorrow now as it is getting very late and I am almost at the point where I cannot...Leave a comment:
-
ADezii
I placed 2 records for Room 1 with dates of 11/3/2011 I then tried to Edit record 1 and changed the Start Time to conflict with Record 2. When I clicked Validate booking it comes back Record Saved.
I think that Rabbit has the right Idea in that we need to eliminate the record being changed from the search so that it appears to be a new record?Leave a comment:
-
Hi Rabbit,
I think you are correct but unfortunitely I dont know how to exclude the record from the search?Leave a comment:
-
Hi ADezii
Many thanks for this but I think you have misunderstood what I am trying to achieve. I already have a scripty that successfully checks and adds a NEW record. What I now want to do is edit the old record and check the new values for validity.
I suspect the way I am trying to do it is not possible as I am using a Bound Form. I have trapped by MsgBox both the [BookStartDate] and the Me.StartDate and they appear...Leave a comment:
No activity results to display
Leave a comment: