EXECUTE problem

[Aymun]

hey,is this code right?

Option Compare Database
Dim DB As DAO.Database
Dim SQL1 As String

Private Sub Form_Load()
Set DB = CurrentDb
End Sub

Private Sub Command1_Click()
SQL1 = "insert into table1 (no,name) values (1,'aymun')"
DB.Execute (SQL1)
End Sub

whenever i run this code,i get this error:
run time error '91':
object variable or with block variable not set.

can any one help please?

[pranoy]

Dim DB As DAO.Database

should be Dim DB As new DAO.Database

[Aymun]

i fixed the definition statement as you wrote, but i still get a message telling me:
invalid use of new keyword.

[FunkyDexter]

I'm not sure that 'new' is your problem. Your setting DB to currentDB in your form_load event so you don't need to create an instance in your variable declarations.

I think this: DB.Execute (SQL1) is your problem. Putting the parameter in brackets means your calling a function and are expecting a result back. Try: DB.Execute SQL1 and you should be right.

[Aymun]

i removed the brackets,but i still get the same problem.

[FunkyDexter]

The only other thing I can see which might cause it is if DB is 'Nothing' when you make the DB.Execute call. This would happen if:-
1.CurrentDB is 'Nothing' when the form_load event fires (so DB gets set to 'Nothing' in turn)
OR
2.Your managing to fire the Command1_click method before the form_load method has run (possibly by calling the event directly from your code rather than by actually pressing the button).

I'd suggest putting a breakoint on the Set DB = CurrentDb and running the code again to see what happens. You'll then be able to check that the form_load event is actually firing and, if it is, that CurrentDb isn't 'nothing' at that point.

Beyind that I'm afraid I'm stumped.

[Ecniv]

Shouldn't affect things, but variables in a module not in a sub usually have public or private..

EG:

private db as dao.database


With DAO you need to set it, and although you appear to be doing this in the load, well as Dexter put, it might not be being set. So, set the db before you use it and set all variables that are then not required back to nothing (in this case the db).

Try the private thing above first, then perhaps a breakpoint as the form opens so you can se it being set. then another breakpoint before the sql statement is being executed.

Also your Sql statement has a reserved word (name) in it. To be safe, put square brackets around all table names and all field names. this mean it will always know it is a table/field.

[Aymun]

actually,in the pause mode, when i put the cursor over DB in ("set DB =currentDB")
a tool tip text appear's telling me ("DB='NOTHING'), so,because of that,i wrote ("set DB =CurrentDB") in the event that fires the insert statement,and it worked,but now i have another question, what if i want to enter the values in textboxes instead of entering them directly, the insert statement would be like that:
"insert into table1 ([no],[name]) values (text1.text,text2.text)"
is this code has anything wrong?

[FunkyDexter]

"insert into table1 ([no],[name]) values (text1.text,text2.text)"
Because your building up a string which you'll then pass to the execute statement you actually need to add the values from the text boxes into the string before you pass it, so:-

SQL1 = "insert into table1 ([no],[name]) values (" & text1.text & ",'" & text2.text & "')"

so if your textboxes contained 1 and Aymun SQL1 will end up being the same as the string you used in your original example. Then pass SQL1 to the execute statment exactly as you were before.