Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    36

    Unanswered: Data type missmatch

    Im building a database that uses the date of the record as the primary key, the table is set not to allow duplicates. I originally built in a warning messaage that would display if a date was selected that was already present, this would then return you to the form and blank the date. This worked fine but now I would like the script to open the original record on that date, the problem is im getting a data type mismatch.

    Heres my code

    LTotal = DCount("Datebox", "HaemDailyRota1", "Date1 = Datebox.value")
    If LTotal > 0 Then
    DoCmd.GoToRecord , , "Date1 = #" & Me.[Datebox] & "#"
    'Me.Undo
    'Message box warning of duplication
    MsgBox "Daily Rota " _
    & SID & " has already been entered." _
    & vbCr & vbCr & "Please edit the current rota.", _
    vbInformation, "Duplicate Information"

    So this script worked fine untill I added the DoCmd.go to record bit, now this generates a data type mismatch, any ideas?

    Thanks

  2. #2
    Join Date
    Sep 2011
    Posts
    36
    I have stopped the data type mismatch by altering the code as shown below however now I get the error message 2105, cannot go to the specified record


    Private Sub Datebox_AfterUpdate()

    Dim SID As Date
    Dim stLinkCriteria As String



    SID = Me.Datebox.Value
    stLinkCriteria = "[Datebox]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber
    LTotal = DCount("Datebox", "HaemDailyRota1", "Date1 = Datebox.value")
    If LTotal > 0 Then


    Me.Undo
    'Message box warning of duplication
    MsgBox "Daily Rota " _
    & SID & " has already been entered." _
    & vbCr & vbCr & "Please edit the current rota.", _
    vbInformation, "Duplicate Information"
    DoCmd.GoToRecord , , acNewRec
    Me.Dirty = False
    DoCmd.GoToRecord , , , [SID]
    Else


    End If


    End Sub



    Anyone any ideas how to solve this? I have checked in debug and SID has the correct value for a saved record.

    Thanks

  3. #3
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Moving to another record based on a criteria goes like this:
    Code:
    Dim rst as DAO.RecordSet
    Dim strCriteria As String
    
    strCriteria = ...
    Set rst = Me.RecordSetClone
    rst.FindFirst strCriteria
    If rst.NoMatch = False Then
        Me.Bookmark = rst.Bookmark
    Else
        ' No matching record
    End If
    rst.Close
    Set rst = Nothing
    Have a nice day!

  4. #4
    Join Date
    Sep 2011
    Posts
    36
    Hi thanks for the responce

    Are there any other options? I have a 64bit versionof office and from what I gather I cannot run the 360.dll file required for the DAO 3.6 object library. I've tried adding it in the referances but it fails to load.

    Thanks

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Try registering Microsoft Office 14.0 Access Database Engine object (if it's not already done). It's the the standard DAO reference for access 2010, and NOT dao 3.6.
    Have a nice day!

  6. #6
    Join Date
    Sep 2011
    Posts
    36
    Thanks that worked

    but now I get invalid argument on "Rst.FindFirst strCriteria" the strCriteria shows the correct date of the record I want to load when I highlite in debug mode.

    Any ideas?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If it's a Date/Time data type, it must be in the proper format: #mm/dd/yyyy#

    Also when re-reading your code, this is incorrect:
    Code:
    LTotal = DCount("Datebox", "HaemDailyRota1", "Date1 = Datebox.value")
    I suppose that for the criteria (third argument of the DCount() function) you want to use the value in Datebox, while you placed the reference to it inside the quotes. It should be:
    Code:
    LTotal = DCount("Datebox", "HaemDailyRota1", "Date1 = " & Datebox.Value)
    And if, as I can easily guess, Date1 is defined as a Date/Time, this would be better:
    Code:
    LTotal = DCount("Datebox", "HaemDailyRota1", "Date1 = #" & Format(Datebox.Value, "mm/dd/yyyy") & "#")
    Have a nice day!

  8. #8
    Join Date
    Sep 2011
    Posts
    36
    Ok now I have


    Private Sub Datebox_AfterUpdate()

    Dim SID As String
    Dim Rst As DAO.Recordset
    Dim stCritera As Date

    Set Rst = Me.RecordsetClone

    strCriteria = "#" & [Datebox] & "#"

    'Check table for duplicate Date
    LTotal = DCount("Datebox", "HaemDailyRota1", "Date1 = #" & Format(Datebox.Value, "mm/dd/yyyy") & "#")
    If LTotal > 0 Then

    'Message box warning of duplication
    MsgBox "Daily Rota " _
    & Date1 & " has already been entered." _
    & vbCr & vbCr & "Please edit the current rota.", _
    vbInformation, "Duplicate Information"
    'Me.Undo

    Rst.FindFirst (strCriteria)
    If Rst.NoMatch = False Then
    Me.Bookmark = Rst.Bookmark
    Else
    'No matching record
    End If
    Else
    Rst.Close
    Set Rst = Nothing
    End If


    End Sub

    Still it fails on the "Rst.FindFirst (strCriteria)" line, with run time error 3001, invalid argument, In debug mode the strCriteria shows value "strCriteria = 21/09/2011"

  9. #9
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    It should be "strCriteria = 09/21/2011" as Access can only use the American date format (Month/Day/Year) internally:
    Code:
    strCriteria = "#" & Format(Datebox.Value, "mm/dd/yyyy") & "#"
    Have a nice day!

  10. #10
    Join Date
    Sep 2011
    Posts
    36
    Ok thanks for that, now I have it so it moves to the line Me.Bookmark = Rst.Bookmark and now it gives error 3022, and a long message about the changes not being allowed as they will create duplicate record in index of tabe!

  11. #11
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Your code seems confusing to me. It looks like you were trying to do twice the same thing (check for the existence of a record with a matching date) using two different methods simultaneously (DCount() and FindFirst).

    One remark first: As mentionned in one of my previous posts, strCriteria must be declared as a String, not as a Date.
    From MSDN (http://msdn.microsoft.com/en-us/libr...ice.12).aspx):
    Recordset.FindFirst Method
    (Office 2007 - Access Developer Reference)

    Locates the first record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).

    Syntax: expression.FindFirst(Criteria)

    expression: A variable that represents a Recordset object.

    Criteria: Required (String) A String used to locate the record. It is like the WHERE clause in an SQL statement, but without the word WHERE.
    Also, I don't see how the code you posted could trigger an error because of duplicate key values as there is no instruction in it that tries to modify the data.

    Anyway, here's a rewritten version of the function, as far as I can understand what you try to achieve:
    Code:
    Private Sub Datebox_AfterUpdate()
    
        Dim Rst As DAO.Recordset
        Dim stCritera As String
        
        Set Rst = Me.RecordsetClone
        strCriteria = "Date1 = #" & Format(Me.Datebox.Value, "mm/dd/yyyy") & "#"
        Rst.FindFirst strCriteria
        
        If Rst.NoMatch = False Then    ' --> Duplicate found:
                                       ' ==> at least one record found with [HaemDailyRota1].[Date1] = Me.Datebox.Value.
        
            'Message box warning of duplication.
            '
            MsgBox "Daily Rota " & Date1 & " has already been entered." & vbCr & vbCr & "Please edit the current rota.", _
                    vbInformation, "Duplicate Information"
            
            ' Move the current record to the one with [Date1] = Me.Datebox.Value.
            '
            Me.Bookmark = Rst.Bookmark
            '
            ' * TO DO: Do something when matching record exists.
            '   -----
            
        Else                            ' --> No duplicate found:
                                        ' ==> No record found with [HaemDailyRota1].[Date1] = Me.Datebox.Value.
            '
            ' * TO DO: Do something when no matching record.
            '   -----
        End If
        Rst.Close
        Set Rst = Nothing
    
    End Sub
    Have a nice day!

  12. #12
    Join Date
    Sep 2011
    Posts
    36
    Hi Thanks for your efforts to help me with this, I have just tried the edited code you provided and it works all the way down to Me.Bookmark = Rst.Bookmark, and again tells me I cannot continue as it will create a duplicate record, is the form attempting to save, before moving to the book mark? Do I need to put something in to stop it trying save the duplicate record?

  13. #13
    Join Date
    Sep 2011
    Posts
    36
    Yes thats it, I just added me.undo before that line and it now works Thanks for your help, much appreciated.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You should first Undo the changes in Datebox (if it's bound to Date1) if you want to move to another record. The error occurs because you're using a bound form with the textbox Datebox bound to Date1. When you move from one record to another (that's what Me.Bookmark = Rst.Bookmark does) Access first try to save the changed data in the current record.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •