Invalid SQL statement

[knowledge76]

I am getting runtime error in my code "delete, select, update or insert expected." I am not able to locate the problem. Friends tell me please where is the problem. when I use select.update insert or delete then I get an error syntax error in from cluase.

Private Sub OK_Click()
On Error GoTo ok_Click

  If IsNull(Me!Dpt) 
(Me!Reputation) Then
    MsgBox "Fill all the fields other than" & Chr(10) & Chr(13) & "clerk!"
    Exit Sub
  End If

  Dim conn1 As ADODB.Connection, rs As ADODB.Recordset
  Set conn1 = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  
rs.Open _
      "Customers", conn1, adOpenDynamic, adLockOptimistic
    rs.AddNew
 rs("Dpt") = Me!Dpt
  rs("salary") = Me!salary
 rs.Close      ' Tabelle schließen.
  DoCmd.Close
  Forms![Main_Form].Form!Uform1.Requery

Exit_ok_Click:
    Exit Sub

ok_Click:
    MsgBox Error$
    Resume Exit_ok_Click
    
End Sub

 

[filo65]

it is not missing an rs.Update before rs.close?

have you tried to comment your error handles?

filo65

 

[Mile-O]

Comment out the On Error line and you'll find out where the error is.

 

[knowledge76]

I have missed the rs.update and rs.close lines in the code and i removed the error handlers but the same error I am getting. The DAO version of this code is working fine but I have converted to ADO and it is not working.

 

[Mile-O]

rsRates.Open "Customers", conn1, adOpenStatic, adLockPessimistic

 

[knowledge76]

Here is the glimpse of my DAO code. When I use to open recordset without using select statemtn i get message select expected and when i use select then i get an error Syntax error in from clause and I am not able to figuzre out the error-

Private Sub OK_Click()


  Dim DB1 As DATABASE, rs As Recordset
  Set DB1 = DBEngine.Workspaces(0).Databases(0)
  Set Tabelle1 = DB1.OpenRecordset("Customers", DB_OPEN_DYNASET)
 rs.AddNew
 rs("A") = Me!A
  rs("Br") = Me!B

  rs.Update    
  rs.Close      

  DoCmd.Close
  Forms![Departments].Form!Uform1.Requery
    
End Sub

 

[Mile-O]

Have you tried the two things I've suggested?

 

[knowledge76]

yes I have tried them but the error remains the same.

 

[Mile-O]

Well, since you tried the first suggestion I made you can tell me at what line the code freezes at.

Also:

If IsNull(Me!Dpt)
(Me!Reputation) Then

What is that supposed to be doing? It doesn't make any sense.

 

[knowledge76]

if the Dpt field or reputation field are empty then a message box appears which stated that this field cannot be left bempty.

 

[Mile-O]

if the Dpt field or reputation field are empty then a message box appears which stated that this field cannot be left bempty.

Then the code should be:

If IsNull(Me.Dpt) Or IsNull(Me.Reputation) Then

 

[knowledge76]

yes I have made the corrction but i cant remove the error.

 

[Mile-O]

Is it possible to post your database or an example recreating your error?

 

[knowledge76]

my project is too big and the limit here is just 2 MB. I zip my project but even then it is 20 times bigger than the allowed amount.

 

[Mile-O]

That's why I suggested an example recreating it.

Just a new database with the relevant table, query, form, and modules.

 

[Rich]

Set rs = db.OpenRecordset ("Customers")

rs.AddNew
rs! A = Me!A
rs! Br = Me!B


rs.Update
rs.MoveNext

rs.Close
Set rs = Nothing
db.Close