run time error 1

[sanjna000]

Hi there,

I am using INSERT INTO query in order to add records to the access db using VBA. My code is as follows:

rivate Sub cmd_OK_Click()
Dim wrkJet As DAO.Workspace
Dim db As DAO.Database
Dim dbname As String
Dim rs As DAO.Recordset
Dim strSQL As String
Dim Q As DAO.QueryDef

dbname = "C:\Documents and Settings\sanju\Desktop\Project\Commcare.mdb"
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase(dbname, , True, "Microsoft.Jet.OLEDB.3.51")
strSQL = "INSERT INTO MasterAccountDetails VALUES (txt_AccName.text, txt_Desc.text, CB_Period.Value, txt_Template.text, txt_Midstarttime.text, txt_Midendtime.text );"
Set Q = db.CreateQueryDef("")
Q.Sql = strSQL
Q.Execute
Q.Close
db.Close
wrkJet.Close

But unfortunately my code generates a run time error stating "Too few parameters.Expected 6.". Presumbly this error msg generates when i don't pass all the required field values. but i have passed all of them. and i cannot understand what is wrong with the statement.
Does anyone know how to solve this prob? Due to some strange reason i can run only SELECT query via VBA. My UPDATE and INSERT query does not seem to be working.

Thanks a lot for u r help in advance.
Sanjna...

 

[PHV]

strSQL = "INSERT INTO MasterAccountDetails VALUES ('" & txt_AccName & "','" & txt_Desc & "','" & CB_Period & "','" & txt_Template & "','" & txt_Midstarttime & "','" & txt_Midendtime & "')"
For fields defined as Numeric, get rid of the single quotes surrounding the corresponding value.
For fields defined as DateTime, replace the corresponding single quotes by hash (#).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanjna000]

Cheers PH...You are a Genius !!! It works well. Do u have any idea of not working my UPDATE query.

 

[sanjna000]

Hey PH,
I was able to solve the prob in my UPDATE query. Thanks for u r help. You are a star for solving my INSERT query.