Date/Time field, prevent duplicates - Runtime error 3075

[vickan240sx]

Hi,

I'm sorry, the error that I'm getting is a run-time error 3464.



I have the following code to prevent duplicate dates. If the date is a duplicate, it'll bring up a warning message and take me to the record that already exists.

Private Sub TodaysDate_BeforeUpdate(Cancel As Integer)

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.TodaysDate.Value
    stLinkCriteria = "[TodaysDate]= #" & "'" & SID & "'"

    'Check InsuranceNoticesLog table for duplicate TodaysDate
    If DCount("TodaysDate", "InsuranceNoticesLog", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "WARNING: " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to that record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Access Bar Code
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
    
End Sub

However, the data type in the table is set to date/time because I want the field in the form to show a calendar and prevent users from inputting an incorrect date format.

When I changed the data type to text, the code above works. However, when I change the data type to date/time, it no longer works. I know it has something to deal with using an # or something, but I couldn't look up any sample information online.

Please help.

Thank you very much.

[alansidman]

Dates usually need to be surrounded with # signs when referred to in a criteria in a query. You might want to try and add a # sign at the end of your stLinkCriteria.

BTW--you should not double post your threads in a forum. Once is sufficient to get a proper response. Send a report to the mods asking them to lock or delete the other thread.

[vickan240sx]

Figured it out!

SID = Me.TodaysDate.Value
    stLinkCriteria = "[TodaysDate]= #" & SID & "#"

[vickan240sx]

Dates usually need to be surrounded with # signs when referred to in a criteria in a query. You might want to try and add a # sign at the end of your stLinkCriteria.

BTW--you should not double post your threads in a forum. Once is sufficient to get a proper response. Send a report to the mods asking them to lock or delete the other thread.

Oh...I didn't realize that the thread was double posted. After I posted it, I realized that I entered a mistake into my subject and did an edit to my original post. Not sure how it double posted.

[pbaldy]

BTW--you should not double post your threads in a forum. Once is sufficient to get a proper response. Send a report to the mods asking them to lock or delete the other thread.

Duplicate deleted.

[vickan240sx]

Duplicate deleted.

My apologies. Thank you.

[vickan240sx]

How do I mark my thread as solved?

[vickan240sx]

A side question in regards to the same topic.

When I input data into the date field, then delete that data and try to continue on to another field, I get an error Run-time error '94', invalid use of Null. Is there anyway to get around this?

I feel that my staff my sometimes enter in data into the date field, but then delete it.

Thank you for your help.

[pbaldy]

My apologies. Thank you.

No apology needed; sounds like the forum did it to you.

[alansidman]

To mark your thread as solved, click on the thread tools at the top of the original posting.

Is the date field a required field?