VBA/SQL Logic problem editing a record in a form

[cyd44]

I have a logic problem that is driving me insane and wonder if someone can help.

I have a bound form listing a number of room booking records and I wish to select one of them and change the booking. However, before I save the edit I need to check that the edit will not conflict with any other bookings within the table. My problem comes when I try to check the edit and my code for this is as follows:-

dteDate = Me!BookStartDate
         strRoom = Me!BookLocation
         dteStartTime = Me!BookTime
         dteEndTime = Me!BookEndTime
         BookRef = Me.BookID
 
 
 
         Valid = 0
         MsgBox ([BookID] & BookRef)
 
 
'        The SQL String Variable below takes the info entered in the booking form to compare a table record.
'        WILL NOT WORK ?????
 
 
        strSQL = "([BookStartDate] = #%D#) AND " & _
       "([BookLocation] = '%R') AND " & _
       "([BookTime] < #%E#) AND " & _
       "([BookEndTime] > #%S#) AND " & "([BookID] <> '%T')"
        strSQL = Replace(strSQL, "%D", Format(dteDate, "d/m/yyyy")) ' Substitues form date to % variables
        strSQL = Replace(strSQL, "%R", strRoom)
        strSQL = Replace(strSQL, "%E", Format(dteEndTime, "H:m:s"))
        strSQL = Replace(strSQL, "%S", Format(dteStartTime, "H:m:s"))
        strSQL = Replace(strSQL, "%T", BookRef)
 
         Valid = (DCount("*", "tblRoomsBooking", strSQL))
 
 
         If Valid > 0 Then
         MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed")
         Me.Undo
         Invalid = True
 
         GoTo Ender
         Exit Sub
 
         Else
         MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted")
 
         DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
         GoTo Delete_Old_Appointment
         End If

I am using the record ID to try and ignore the record being edited from the SQL search by putting [BookID] <> Me.BookID within the SQL. My perception here was the the search would ignore the current record and simply test the remaining ones? However, the SQL results in Validation Success each time (even though I try a change I know will conflict).
The results are illogical but I am obvioiusly missing something here.

I never realised that editing in a bound form could be so complicated and am wondering if my approach to this is completely wrong.

Any advice would be greatly appreciated.

 

[vbaInet]

I don't know why you need the Replace() function in there and why the Go To, so here:

    dteDate = Me!BookStartDate
    strRoom = Me!BookLocation
    dteStartTime = Me!BookTime
    dteEndTime = Me!BookEndTime
    BookRef = Me.BookID

    MsgBox ([BookID] & BookRef)

    strSQL = "([BookStartDate] = #" & Format(dteDate, "\#mm\/dd\/yyyy\#") & "#) AND " & _
             "([BookLocation] = '" & strRoom & "') AND " & _
             "([BookTime] < #" & Format(dteEndTime, "H:m:s") & "#) AND " & _
             "([BookEndTime] > #" & Format(dteStartTime, "H:m:s") & "#) AND " & "([BookID] <> '" & BookRef & "')"

    If Nz(DCount("*", "tblRoomsBooking", strSQL), 0) <> 0 Then
        MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed")
        Me.Undo
        Invalid = True
'            GoTo Ender
        Exit Sub
    Else
        MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted")

        DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
        GoTo Delete_Old_Appointment
    End If

 

[cyd44]

Hi vbaInet,

I take your point about the replace and have tried your version and mine but get the same result. IE it validates everything. The GoTo by the way is because this procedure is only part of the Edit process and if the SQL validates a booking it will go to the next part. For the purpose of this query you can just forhet it because it is the SQL that is not validating as I would expect. There are no complie errors, simply giving false result.

 

[vbaInet]

Oops... replace this line:

    strSQL = "([BookStartDate] = " & Format(dteDate, "\#mm\/dd\/yyyy\#") & ") AND " & _

I got rid of the hash (#) characters surrounding the Format(). It wasn't needed.

Also if BookTime and BookEndTime are not the same Format() as the criteria, then it will likely fail. Are they the same format?

 

[cyd44]

Have substituted your line and results are still the same....Validates every change regardless of conflict.

Both Time variables are set as Date/Time medium time and Date variable is short Date

 

[vbaInet]

Use a DLookup to look at the field that it returns and see if you criteria matches.

Also Debug.Print the sql string to see what it outputs.

 

[cyd44]

Am now getting Error 3075 Syntax error (missing operator) with the SQL all of the Form Variables return correct values

 

[vbaInet]

What did you change in your code?

Are you validating your controls to ensure that they contain values before concatenating them?

What code line does it highlight when you debug it?

 

[cyd44]

Hi vbaInet

Have sorted the Error (I had a typo). I have attached the part of the form and table so That you can see what I mean (hope this helps) Try Changing one of the records to see what happens and you will see the sql does not appear to trap the confilct.

 

[vbaInet]

Try Changing one of the records to see what happens and you will see the sql does not appear to trap the confilct.

Without looking at your db, from what you mentioned do you realise that whatever changes you make to the current record is not commited (or saved) to the database until you programmatically save it or move to another record?

 

[cyd44]

That is what I thought, but when I captured the variables using MsgBox to show the table field and the form field they were showing as Identical. I am therefore assuming that Access automatically saves a record when it loses focus. this is why we are trying to exclude the edit record from the search by saying <> to the record ID.

 

[vbaInet]

Before running the DCount() function, you can do one of:

DoCmd.RunCommand acCmdSaveRecord

If Me.Dirty Then Me.Dirty = False

 

[cyd44]

This is beyond logic and driving me nuts. I tried both the save and the Me.Dirty placing then just before the Dcount function. The results are just the same IE Record Valid!!!!!!!!

 

[vbaInet]

On two occasions you didn't follow what you were advised. One the strSQL is incorrect and two you didn't use the Me.Dirty code I gave you above. It just seems that we are not advancing with this because you are changing things.

 

[cyd44]

Can I say that I am appreciative of your help and am trying your recomendations. I used both the DoCmd.SaveRecord and the Me.Dirty that you gave me and niether of them made any difference. As to the code I am using what you gave me and any change made was simply to reflect a declaration change to any variable prior to getting your code. This is the code that is running. you will see that I used Save and Me.Dirty on different occasions

BookRef = Me.BookID
With Me
  dteStartDate = Me!BookStartDate
  strLocation = Me!BookLocation
  dteStartTime = Me!BookTime
  dteEndTime = Me!BookEndTime
End With
strSQL = "[BookID] <> " & Me![BookID] & " AND " & _
         "([BookStartDate] = #" & dteStartDate & "#) AND ([BookLocation] = '" & strLocation & "') AND (" & _
         "(#" & dteStartTime & "# BETWEEN [BookTime] AND [BookEndTime]) OR (#" & _
         dteEndTime & "# BETWEEN [BookTime] AND [BookEndTime]) OR (#" & dteStartTime & _
         "# < [BookTime] AND #" & dteEndTime & "# > [BookEndTime]))"
        
'Any changes to this Record?
If Not Me.Dirty Then MsgBox "Record has not been changed": Exit Sub
'DoCmd.RunCommand acCmdSaveRecord
If Me.Dirty Then Me.Dirty = False
If DCount("*", "tblRoomsBooking", strSQL) > 0 Then
  MsgBox "Invalid Booking"
    DoCmd.RunCommand acCmdUndo      'UNDO
Else
   
     MsgBox "Record has been Saved"
End If
Exit_cmdTest_Click:
  Exit Sub
Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click
End Sub

 

[vbaInet]

This:

strSQL = "[BookID] <> " & Me![BookID] & " AND " & _
"([BookStartDate] = #" & dteStartDate & "#) AND ([BookLocation] = '" & strLocation & "') AND (" & _
"(#" & dteStartTime & "# BETWEEN [BookTime] AND [BookEndTime]) OR (#" & _
dteEndTime & "# BETWEEN [BookTime] AND [BookEndTime]) OR (#" & dteStartTime & _
"# < [BookTime] AND #" & dteEndTime & "# > [BookEndTime]))"

Is incorrect. And the Format() I applied was there for a reason.

 

[cyd44]

I actually cut a pasted the wrong code to you as I had a copy and was trying things out because on the debug screen the time shown for the variables was 09:0:0 and 10:30:0 whereas the Me.variable times show 09:00:00 and 10:30:00. I was simply wondering if this was the problem and took them out.

I have gone back to your original code and placed it into the test Db and have a 3464 Error on running saying data type mismatch occuring at the DCount Function. my Code used is as you gave me

Dim dteDate As Date
    Dim strRoom As String
    Dim dteStartTime As Date
    Dim dteEndTime As Date
    Dim BookRef As Long
    Dim StrSQL As String
    
    dteDate = Me!BookStartDate
    strRoom = Me!BookLocation
    dteStartTime = Me!BookTime
    dteEndTime = Me!BookEndTime
    BookRef = Me.BookID
    MsgBox ([BookID] & BookRef)
    StrSQL = "([BookStartDate] = " & Format(dteDate, "\#mm\/dd\/yyyy\#") & ") AND " & _
             "([BookLocation] = '" & strRoom & "') AND " & _
             "([BookTime] < #" & Format(dteEndTime, "H:m:s") & "#) AND " & _
             "([BookEndTime] > #" & Format(dteStartTime, "H:m:s") & "#) AND " & "([BookID] <> '" & BookRef & "')"
    DoCmd.RunCommand acCmdSaveRecord
    'If Me.Dirty Then Me.Dirty = False
    If Nz(DCount("*", "tblRoomsBooking", StrSQL), 0) <> 0 Then
        MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed")
        Me.Undo
        
'
        Exit Sub
    Else
        MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted")
        DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
        'GoTo Delete_Old_Appointment
    End If

I must apologise if I am comming acroos as thick, but I am very new to access and VBA and I always try to work things out by myself whilst waiting for answers. If I dont try things and rely upon others giving me the answers I will never progress I feel. Anyway, I have gone back to your original and have a short test Db to try this out and I am getting the above error on running it. Sorry if I am a pain but my logic says this should work but it doesnt.

 

[vbaInet]

No need to apologise. It's good to try things out and see if it works but at the same time you should tell me if you made changes and what changes you made.

So, remember what I mentioned in my other post about both BookTime and BookEndTime having the same format as their criteria? You need to Format() BookTime and BookEndtime on the left hand side, inside the quotes so it matches with it's criteria.

Also, do you know about Debug.Print?

 

[cyd44]

Found the Data error we had declared date as mm/dd/yyyy and it my date is dd/mm/yy/ Anyway, I changed this and the script runs but it still validates an invalid change.

I have 2 booking for 8/11/2011 in Room 1

1. First is from 11AM - 4PM
2. Second is from 4Pm - 7PM

When I change record 2 to start at 3PM the DCount says it is a Valid Change?

this is why I am so frustrated as the Logic appears sound but the result is not.

 

[cyd44]

I now think we are getting somewhere as I think it is to do with the Time Formating. My Time variables in the table show 11:00AM and 16:00PM but we have declared format as H:m:s.

What do I change the format to in order to reflect 11:00AM because I dont know the syntax for this?