I can not figure out what the issue is with my code. I can add to my database just fine but cannot update the database. when debugging once it hits the sql statement it jumps to the error handler. Here is the code
Code:
Private Sub Testbutton_Click()'Declaring the necessary variables.
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim i As Integer
Dim x As Integer
'add error handling
On Error GoTo errHandler:
'get the path to the database
dbPath = Sheet2.Range("D23").Value
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
Set rs = New ADODB.Recordset 'assign memory to the recordset
'Create the SQL statement to retrieve the data from table.
'This where I am having trouble
rs.Open "SELECT * FROM GBLDB" & _
"WHERE ID = " & CLng(Me.txtRequest), ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdText
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"
Exit Sub
End If
With rs
.Fields("Furn Pull").Value = cbfurniturepull.Value
.Fields("Date Time").Value = Date & " " & Time
.Fields("Building").Value = txtbuilding.Text
.Fields("Title").Value = txttitle.Text
.Fields("Contact").Value = txtname.Text & ": " & txtreqphone.Text
.Fields("Location/SPID").Value = spidNumber
.Fields("Scope").Value = txtJobDescrbtion.Text
.Fields("CB Date").Value = cbydate
.Update
End With
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"
End Sub
