Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: Data Type Mismatch Error

    Hello,

    I have a form that is used to update a table in my database. I have written some code so that repeating values are not inserted for a part number.

    Private Sub cmd_check_existing()

    On Error GoTo Err_Command39_Click

    Dim dbsProducts As Database
    Dim rstAttributes As Recordset

    Set dbsImpellers = CurrentDb()
    Set rstAttributes = dbsImpellers.OpenRecordset("AttributesTbl", dbOpenDynaset)

    If MsgBox("Do You Want To Save This Record?", vbQuestion + vbYesNo, "Save Record?") = vbNo Then
    Me.Undo
    MsgBox "Product has not been added to database", vbInformation, "Save Cancelled"

    Else

    If IsNull(DCount("Assembly", "AttributesTbl", "Assembly =" & Me.tbo_assembly)) Then
    MsgBox "Product has been successfully added", vbInformation, "Save Successful"

    Else
    MsgBox "This assembly part number already exists in the database."
    Me.Undo

    End If

    End If

    Exit_Command39_Click:
    Exit Sub

    Err_Command39_Click:
    MsgBox Err.Description
    Resume Exit_Command39_Click

    End Sub

    There are two different syntaxes available for the "Assembly" field: either a 9 digit number with no spaces in between(i.e. 100018596) OR a 9 digit part number separated by dashes (859-982-364).

    Currently, assembly is set up as text data type in the database. When I open the table in design view, it allows me to add either of the syntaxes above without any issue. However, when I try using the form to do it and enter the 9 digit number without spaces, it gives me a data type mismatch error on the DCount line. I realize that this is because the 9 digit number is being read as an integer. Is there any way to avoid this and use this form to add this number regardless of which syntax is being used?

    Thanks

  2. #2
    Join Date
    Apr 2011
    Posts
    34
    Because Assembly is text, it must be enclosed in single quotes:
    Code:
    If IsNull(DCount("Assembly", "AttributesTbl", "Assembly ='" & Me.tbo_assembly & "'")) Then

  3. #3
    Join Date
    Nov 2011
    Posts
    57
    Thank you so much!

    Can I ask why the single quotation marks make a difference? I'm completely new to access and VBA, so I've mostly been searching the internet to get the code I need and slightly adjusting it to fit my needs, but I'm interested in actually trying to learn and understand it.

  4. #4
    Join Date
    Apr 2011
    Posts
    34
    Whenever you deal with text, it has to be enclosed in quotation marks. For numbers, you don't. Sometime you want numbers to be interpreted as text, like in your case. Without quotation marks, 859-982-364 is read as a mathematical operation 859 minus 982 etc...

  5. #5
    Join Date
    Nov 2011
    Posts
    57
    Thanks for the reply.

    I'm actually experiencing another problem now... anytime I enter any number (regardless of syntax), it is telling me that it arleady exists in the database. However, I've gone back and looked at the table and it does not... the problem just started when I added the single quotation marks. Any idea what the source could be?

  6. #6
    Join Date
    Apr 2011
    Posts
    34
    You should consider making that field a primary key with No duplicates allowed. This way, data entry is made at the database design level rather than at the form level. If your form changes or you do manual entries directly in the table, you will be guarantied not to have duplictes.

    Obviously
    Code:
    ElseIf IsNull(DCount("Assembly", "AttributesTbl", "Assembly =" & Me.tbo_assembly)) Then
    return something not null, therefore the If statement goes to the next Else and throws the message.

    Maybe run the output of the Dcout in the debug window (Alt+G).
    And in your code, insert
    Code:
    Debug.Print DCount("Assembly", "AttributesTbl", "Assembly ='" & Me.tbo_assembly & "'")
    Before the if statement.

  7. #7
    Join Date
    Nov 2011
    Posts
    57
    I did as you suggested... the DCount value is 0, so then it should be adding it to the database instead of going to the "else" part of the code and telling me it already exists...

    Any other suggestions?

  8. #8
    Join Date
    Nov 2011
    Posts
    57
    I think I fixed it... I changed it to

    ElseIf DCount("Assembly", "AttributesTbl", "Assembly =" & Me.tbo_assembly) = 0 Then

    This seemed to do it. I'm not really sure how this is any different than isnull, but I'm happy it works!

  9. #9
    Join Date
    Apr 2011
    Posts
    34
    But isnull(0)=False, that is the problem.
    Eliminates the Isnull statement and do
    Code:
    If DCount("Assembly", "AttributesTbl", "Assembly ='" & Me.tbo_assembly & "'")=0 Then

  10. #10
    Join Date
    Nov 2011
    Posts
    57
    That makes sense. Thanks so much for your help, I really appreciate it!

Posting Permissions

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