Want form to allow entries if records aren't duplicates

[MeanCulture]

I created a query to show records already in the database. This way if someone tries to a record with similar entries, it would show an error. In doing so, when I test it out and try to enter a record that does not have like entries, it still gives me the error.

For example if I have a record showing Car 1 Reserved on 1/16/2014 at 3pm-5pm already in the system and I go to enter Car 2 Reserved on 1/17/2014 at 2pm - 4pm it gives me the same error. Here is the code I used.

Option Compare Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "qrydups", "[CHECK-OUT DATE/TIME]= #" & Me.DATE_RESERVED & "# And [CHECK-IN DATE/TIME]=#" & Me.CHECK_IN_DATE_TIME & "# And GV='" & Me.GV & "'") >= 1 Then
 MsgBox "Sorry, this GV is taken! Choose another start/end date and time!", , "Double Booking"
 Cancel = True
 End If
 End Sub

[ItsMe]

Are you saving the record before using your Dcount? you have >= 1

Also, may want to consider time ranges. Comparing check in with check in and check out with check out may not suffice in circumstances where the only difference is, say, the check in time. For instance, just because I want a car for a longer time period should not mean my request would trump a previous request.

[MeanCulture]

Are you saving the record before using your Dcount? you have >= 1

Also, may want to consider time ranges. Comparing check in with check in and check out with check out may not suffice in circumstances where the only difference is, say, the check in time. For instance, just because I want a car for a longer time period should not mean my request would trump a previous request.

The query that I made, shows the records that are currently in the database. When someone enters information into the form, it doesn't save if there is a record like it already in the system. Or at least that is what I am looking for the code to do.

How would I go about time ranges? That may be easier and less of a headache than what I am doing right now.

[ItsMe]

I don't know if time ranges are easier but it may be less of a headache in the long run. You may need to trasnslate your time to a Double data type and compare that. Maybe a combination of Double data type and the Between function. Probably need more than one comparison to look for overlapping start and or finish times.

[MeanCulture]

Ok, now back to Dcount, would I still use it to prevent duplicates? And how can I get it to allow records that aren't already in the system?

[ItsMe]

I don't see anything wrong with the string. Try this for testing.

Dim intCount As Integer
intCount = -500
intCount = DCount("*", "qrydups", "[CHECK-OUT DATE/TIME]= #" & Me.DATE_RESERVED & "# And [CHECK-IN DATE/TIME]=#" & Me.CHECK_IN_DATE_TIME & "# And GV='" & Me.GV & "'")
MsgBox intCount
If intCount >= 1 Then
 MsgBox "Sorry, this GV is taken! Choose another start/end date and time!", , "Double Booking"
 Cancel = True
End If

[MeanCulture]

Entered your code same result.

[ItsMe]

What is the msgbox saying. You are going to have to look at your string and understand the data. To me, the string looks good, it is working. Maybe the values in the table are not what you expect, like a time value vs date. Adjust your Dcount until the masgbox that displays the record count changes to help you narrow things down. You can approach it from the table side too. add duplicates and see if the number in hte msgbox jives.

[MeanCulture]

Ok I will play around with it.

[MeanCulture]

Ok I am completely lost. When I enter a brand new record that doesn't duplicate anything I already have in the database, the msg box that I created still shows. I only need it to show when the information entered already mataches whats in the database.

[ItsMe]

I suggest you pay attention to the count of records being returned.
MsgBox intCount

This msgbox will tell you the count of records being returned by your Dcount. You already know that '>= 1 Then' is being met.

If you pay attention to the count of records and are capable of manipulating the count by adjusting your Dcount statement or manipulating the data in your table or a combination of both, you will be closer to understanding what is needed.

[Missinglinq]

ItsMe is assuming that the GV Field is defined as Text, because of the syntax you used in your original code; is that correct? IF not, the syntax would be different.

Can you copy and post the code for the latest attempt you've made?

Linq ;0)>

[ItsMe]

ItsMe is assuming that the GV Field is defined as Text...

I am... if the Dcount return is always >=1 I imagine a text field and correct syntax. Something is not adding up. Hopefully this explains my pursuit of a literal count value and a determination in how to affect said value.

[Missinglinq]

I really think this is going to need the actual 'laying on of hands,' to figure out what's going on, because, as you said, something is not adding up!

Linq ;0)>

[MeanCulture]

Thank you for all your help. I haven't solved my issue, but I've found a better solution for this project. There are other issues after this one that I still have to figure out and it's taking more time than I have. Thank you again for your help.