I am working on a form on Access. It is supposed to insert data into a table, but I keep getting error 3464.
I am a little new to Visual Basic. This was previously working, but no longer. I am using SQL Server as a back-end. The connection works and it was inserting new rows. I have looked online but have not found a comprehensive explanation of this error.
Private Sub mSubmitButton_Click()
CurrentDb.Execute "INSERT INTO dbo_Reporting_Table([Date], Vendor, Ordered_By, Picked_Up_By, Reported_By) " & _
" VALUES('" & Me.mDate & "','" & Me.mVendor & "','" & Me.mOrdered_By & "','" & _
Me.mPicked_Up_By & "','" & Me.mReported_By & "')"
CurrentDb.Execute "INSERT INTO dbo_Items_Table([PO#], [Item], [Price], [Equipment], [Quantity]) " & _
" VALUES(" & Me.mPONumber & ",'" & Me.mItemInput0 & "'," & Me.mPriceInput0 & ",'" & _
Me.mEquipmentInput0 & "'," & Me.mQuantityInput0 & ")"
Me.mItemInput0 = ""
Me.mPriceInput0 = ""
Me.mEquipmentInput0 = ""
Me.mQuantityInput0 = ""
Me.mDate = ""
Me.mVendor = ""
Me.mOrdered_By = ""
Me.mPicked_Up_By = ""
Me.Requery
Exit Sub
Error_PopupMessage:
'Response = MsgBox(Message, vbOKOnly + vbInformation, "Form Entry Hint", "help", "1000")
Exit Sub
End Sub
Any advice on what I might be doing wrong would be greatly appreciated.
- Remove From My Forums
-
Question
-
I have two unbound fields, one a combo box for the current station number and a textbox for the new station number. I have two buttons, «Change GW Station» and «Delete GW Station.»
The Change GW Station is working however when I attempt to delete a record I get a «Error 3464 — Data type mismatch in criteria expression.» The data type of the data I am deleting (Station_Number) is text
I don’t understand especially since I am using the same field in the Change procedure and it works fine.
This is the delete event that is throwing the error
Private Sub cmdGWDelStation_Click() On Error GoTo errhandler Dim strSql As String Dim Db As dao.Database strSql = "DELETE * FROM Section16_5_Data WHERE Section16_5_Data.Sample_Number = '" & cboGWOldStationNu & "' " Set Db = CurrentDb() LResponse = MsgBox("Are you sure you want to delete Station Number '" & cboGWOldStationNu & "' " & _ " All sample data associated with station number '" & cboGWOldStationNu & "' will be also be delete.", vbYesNo) Select Case LResponse Case vbYes Db.Execute strSql MsgBox ("The station number and all associated sample data has been deleted") Case vbNo MsgBox ("This task has been cancelled") End Select updateField = True ExitHere: Set Db = Nothing Exit Sub errhandler: 'There is an error return false updateField = False With err MsgBox "Error " & .Number & vbCrLf & .Description, _ vbOKOnly Or vbCritical, "updateField" End With Resume ExitHere End Sub
jim neal
Answers
-
Hi,
you have mentioned Station_Number in your post but I can see Sample_Number in your SQL. Is this correct?
Vladimir Cvajniga
-
Marked as answer by
Tuesday, February 26, 2013 11:41 AM
-
Marked as answer by
Vladimir_prog 0 / 0 / 0 Регистрация: 14.07.2016 Сообщений: 2 |
||||
1 |
||||
14.07.2016, 23:33. Показов 4458. Ответов 2 Метки sql, запрос, ошибка, с (Все метки)
Всем доброго времени суток.
Буду очень благодарен за помощь, а то уже 2 дня с этим вожусь.
0 |
mobile 26792 / 14471 / 3192 Регистрация: 28.04.2012 Сообщений: 15,782 |
||||
15.07.2016, 00:27 |
2 |
|||
РешениеVladimir_prog, дата не текст, дата в Access поле типа Double — числовое поле с плавающей запятой. К тому же дату в ВБА надо задавать по американскому формату: mm/dd/yyyy. И указатель типа даты это решетки «#». Следовательно выражение для Where надо переписать так:
Обратный слэш указывает, что следующий символ не системный
1 |
0 / 0 / 0 Регистрация: 14.07.2016 Сообщений: 2 |
|
15.07.2016, 01:25 [ТС] |
3 |
mobile, большое спасибо за столь быстрый и рассписаный ответ !
0 |
I am working on a database for my work and i’m trying to insert and update values from tables with sql inside the vb editor
This is my code:
Option Compare Database
Private Sub Übernehmen_Click()
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim ArtikelNr As Integer
Dim Stück As Integer
Dim Lieferant As String
Dim Bestellnr As Integer
Dim EkPreis As String
Dim Mwst As String
Dim Einkaufsort As String
Dim GhIndex As String
Dim Datum As String
Dim Uhrzeit As String
Dim Lager As String
Dim Beschreibung As String
ArtikelNr = [Forms]![Einkauf]![ArtikelNr].Value
Stück = [Forms]![Einkauf]![Stück].Value
Lieferant = [Forms]![Einkauf]![Lieferant].Value
Bestellnr = [Forms]![Einkauf]![Bestellnr].Value
EkPreis = [Forms]![Einkauf]![EK-Preis].Value
Mwst = [Forms]![Einkauf]![Mwst-Satz].Value
Einkaufsort = [Forms]![Einkauf]![Einkaufsort].Value
GhIndex = [Forms]![Einkauf]![GH-Index].Value
Datum = [Forms]![Einkauf]![Datum].Value
Uhrzeit = [Forms]![Einkauf]![Uhrzeit].Value
Lager = [Forms]![Einkauf]![Lager].Value
strSQL1 = "INSERT INTO Einkäufe (ArtikelNr, Stück, Lieferant, Bestellnr, EKPreis, MwstSatz, Einkaufsort, GHIndex) VALUES (" & ArtikelNr & "," & Stück & ",'" & Lieferant & "','" & Bestellnr & "','" & EkPreis & "','" & Mwst & "','" & Einkaufsort & "','" & GhIndex & "');"
Beschreibung = DLast("EinkaufID", "Einkäufe")
strSQL2 = "INSERT INTO Transaktionen VALUES ('" & ArtikelNr & "','" & Datum & "','" & Lager & "','" & Stück & "','EinkaufID ' + '" & Beschreibung & "' ,'Einkauf',NULL,NULL,'" & Uhrzeit & "');"
strSQL3 = "UPDATE Lagerbestand SET Stück = Stück+" & Stück & " WHERE ArtikelNr = '" & ArtikelNr & "' AND Lager = '" & Lager & "';"
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
End Sub
After trying to press the button it first adds the two entries and stops at the third one just to throw an error saying «Runtime Error: 3464».
After I press debug it marks the line DoCmd.RunSQL strSQL3
.
I would appreciate any answer I get.
Many thanks in advance.
- Remove From My Forums
-
Question
-
I am trying to print a report I am recieving an emrro Run-tim error 3464: Data type mismatch in criteria expresson. How can I debug this error so that I can be able the report in Access?
-
Moved by
Thursday, July 12, 2012 1:38 AM
moving to a more appropriate forum (From:Excel IT Pro Discussions)
-
Moved by
Answers
-
Hi,
As your code, I found that your SQL is a select statement, and based on my try, I think the CurrentDb.Execute
cannot run a “Select” SQL statement. Since the “dbFailOnError” means
Rolls back updates if an error occurs. A “select” SQL statement will not update any object. I think we can use DoCmd.RunSQL strSQL instead.About this method, please refer to the following link:
http://msdn.microsoft.com/en-us/library/ff194626.aspx
And in your code “strSQL = rs![Col1]”. This statement will change the strSQL statement, it will return the result of the select statement. Then use this result of the select statement
will cause an error.
Jaynet Zhang
TechNet Community Support
-
Marked as answer by
Jaynet Zhang
Thursday, July 19, 2012 3:03 AM
-
Marked as answer by