Add/Edit Button Giving Datatype Error

simkessy

Registered User.
Local time
Today, 08:00
Joined
Jan 10, 2013
Messages
16
I have a main and subform. I have text boxes in the main form that will allow me to add/edit information into the subform. Add works fine but when I try to edit a record, the information of the selected record shows up in the textbox like I want to but when I try to update, I get datatype error. Also when I try to delete a record I also get a datatype error.

Im trying to copy the work one of my partners for a previous project did (CarDealership). Im hoping to get similar functionality into the KeyInventory DB.
Here's the file im working on: jumpshare. com/b/t7Lot8

This is the code for the add/update button:
Code:
Private Sub cmdAdd_Click()
If Me.keyID.Tag & "" = "" Then
    CurrentDb.Execute "INSERT INTO KEYS(KEY_ID, ROOM, DRAWER)" & _
        " VALUES(" & Me.keyID & ",'" & Me.roomID & "'," & Me.drawerID & ")"
          subKey.Form.Requery
Else
    CurrentDb.Execute "UPDATE KEYS " & _
    " SET KEY_ID=" & Me.keyID & _
    ", ROOM='" & Me.roomID & "'" & _
    ", DRAWER='" & Me.drawerID & "'" & _
    " WHERE KEY_ID=" & Me.keyID.Tag
End If

cmdReset_Click

subKey.Form.Requery
End Sub

This is the code for the Delete button:
Code:
Private Sub cmdDelete_Click()
If Not (Me.subKey.Form.Recordset.EOF And Me.subKey.Form.Recordset.BOF) Then
    If MsgBox("Confirm Deletion?", vbYesNo) = vbYes Then
        CurrentDb.Execute "DELETE FROM KEYS" & _
            " WHERE KEY_ID=" & Me.subKey.Form.Recordset.Fields("KEY_ID")
                Me.subKey.Form.Requery
    End If
End If
End Sub
 
Your add code treats Drawer as a numeric field, the update code as a text field.
 
Im really new to VBA. This project is my first time working with it actually. I am not sure how im making the distinction between text and numeric fields.

I just realized that the attribute for drawer was text, I just changed it to numeric. How do I make sure update treats it numeric.

EDIT: I don't see what you mean thought, there is no different between how room and drawer are treated. From what I can see.
 
In the update SQL, the value will be surrounded by ', which is appropriate for text. Numeric values shouldn't have them.
 
In the update SQL, the value will be surrounded by ', which is appropriate for text. Numeric values shouldn't have them.

My god, VBA is tricky. Ill try it right now and see if this works. Thanks for the replies.
 
In the update SQL, the value will be surrounded by ', which is appropriate for text. Numeric values shouldn't have them.

I removed the single quotes in the update portion of the Add button so it looks like this:

Code:
Else
    CurrentDb.Execute "UPDATE KEYS " & _
    " SET KEY_ID=" & Me.keyID & _
    ", ROOM='" & Me.roomID & "'" & _
    ", DRAWER=" & Me.drawerID & "" & _
    " WHERE KEY_ID=" & Me.keyID.Tag
End If

But I get the same error.

EDIT: Room is actually a number too, so Im going to remove the single quotes from room as well and see if that fixes it.
 
Last edited:
In the update SQL, the value will be surrounded by ', which is appropriate for text. Numeric values shouldn't have them.

Yea im not sure what's wrong. The drawer is definitely being treated as a number but im still getting data type errors when trying to update information
 
Can you post the db here, or a representative sample?
 
THere's a jump share file in the OP. I wasn't allowed to post links yet so i broke the link by removing http and adding spaces

EDIT: jumpshare. com/b/t7Lot8
 
Last edited:
Key_ID and Drawer are both text, so require the single quote delimiters.
 
Sorry since then I've changed the drawer to number. The only text attribute is Key_ID. This is giving my delete function problems:

Code:
     CurrentDb.Execute "DELETE FROM KEYS" & _
    " WHERE KEY_ID=" & Me.subKey.Form.Recordset.Fields("KEY_ID")

The way it's written it is looking for a number. Im not sure how to make it look for text using the single quotes. What I try is giving me syntax errors are runtime.

to address the add/update this is what im trying.
Code:
If Me.keyID.Tag & "" = "" Then
    CurrentDb.Execute "INSERT INTO KEYS(KEY_ID, ROOM, DRAWER)" & _
        " VALUES('" & Me.keyID & "'," & Me.roomID & "," & Me.drawerID & ")"
          subKey.Form.Requery
Else
    CurrentDb.Execute "UPDATE KEYS " & _
    " SET KEY_ID=''" & Me.keyID & _
    ", ROOM=" & Me.roomID & _
    ", DRAWER=" & Me.drawerID & _
    " WHERE KEY_ID=" & Me.keyID.Tag
End If

I added the single quotes in the insert statement but in the set statement I don't know how to set text. This is, I think, the same issue I am facing with the delete function.
 
Try

CurrentDb.Execute "UPDATE KEYS " & _
" SET KEY_ID='" & Me.keyID & _
"', ROOM=" & Me.roomID & _
", DRAWER=" & Me.drawerID & _
" WHERE KEY_ID=" & Me.keyID.Tag

You might find this helpful, as you can see what the finished SQL looks like:

http://www.baldyweb.com/ImmediateWindow.htm
 
Try

CurrentDb.Execute "UPDATE KEYS " & _
" SET KEY_ID='" & Me.keyID & _
"', ROOM=" & Me.roomID & _
", DRAWER=" & Me.drawerID & _
" WHERE KEY_ID=" & Me.keyID.Tag

You might find this helpful, as you can see what the finished SQL looks like:

I used the debug.print stm for my delete code:
Code:
  If MsgBox("Confirm Deletion?", vbYesNo) = vbYes Then
        Dim strSql As String
        strSql = "DELETE FROM KEYS" & _
            " WHERE KEY_ID=" & Me.subKey.Form.Recordset.Fields("KEY_ID")
        Debug.Print strSql ' <- prints to Immediate window
        CurrentDb.Execute strSql, dbFailOnError

    End If

The result in Immediate was this: DELETE FROM KEYS WHERE KEY_ID=1
Considering that this attribute is a text I need the single quotes around the selected record ID.
I've tried:

" WHERE KEY_ID= ' " (what you suggested for SET KEY_ID in the add code). I get this: DELETE FROM KEYS WHERE KEY_ID='1
" WHERE KEY_ID= '' "
" WHERE KEY_ID= '*' "

None of these work. I have no clue how to get it to put single quotes around the selected value.
 
Try

" WHERE KEY_ID='" & Me.subKey.Form.Recordset.Fields("KEY_ID") & "'"
 
Try

" WHERE KEY_ID='" & Me.subKey.Form.Recordset.Fields("KEY_ID") & "'"

I just put this in and it worked.
Code:
"DELETE FROM KEYS" & _
            " WHERE KEY_ID=" & _
    Chr(39) & Me.subKey.Form.Recordset.Fields("KEY_ID") & Chr(39)

It's basically what you said but someone at Stackoverflow said using ascii code would be safer (no idea why though)

Both methods work though so that's fantastic. Now I just need to figure out the add/update and I should be good. I believe the problem is the same though - ensuring the single bracket is in the correct spot for it to be recognized as text.

The solution to the update problem:
Code:
    CurrentDb.Execute "UPDATE KEYS " & _
    " SET KEY_ID=" & Me.keyID & _
    ", ROOM=" & Me.roomID & _
    ", DRAWER=" & Me.drawerID & _
    " WHERE KEY_ID=" & Chr(39) & keyID.Tag & Chr(39)
    Debug.Print KEY_ID
 
Last edited:

Users who are viewing this thread

Back
Top Bottom