Need help on Insert

[pigkachu]

I am new in Microsoft Access and currently facing an error. I have no idea why I get this problem. Same code works well on another system.

When i input s898688j (example) in a IC number text box, it will give me run-time error '3061': Too few parameters. Expected 1

When i input 986343dd (example), i will give me run-time error'3075': Syntax error (missing operator) in query expression '986343dd'.

When i input 6986324 (example), the insert query is successful.

I think the error is from IC number. All the other field does not have any problem. I did not do any validation for my IC number yet as well

I have 2 tables

trainee table:

ID (autonumber),
full_name (text),
IC_num (text),
phone_no (text),
company_name (text)
course table:

course_id (autonumber),
IC_num (text),
course_name (text),
L3_survey (number),
L4_survey (number),
start_date (date/time),
end_date (date/time),
no_of_days (number)

Insert query in VBA:

For trainee:

CurrentDb.Execute "INSERT INTO trainee(IC_num, full_name, phone_no, company_name)" & _
" VALUES (" & Me.newIC & ",'" & Me.newName & "','" & _
Me.newPhone & "','" & Me.newCom & "')"

For course:

CurrentDb.Execute "INSERT INTO course(IC_num, course_name, L3_survey, L4_survey, start_date, end_date, no_of_days)" & _
" VALUES (" & Me.newIC & ",'" & Me.newCou & "','" & _
Me.newL3 & "','" & Me.newL4 & "','" & Me.newStrt & "','" & Me.newEnd & "','" & Me.newDay & "')"

I had tried a lot of ways but i have no idea why is it not working!!! :banghead:

 

[Galaxiom]

Render the SQL construction into a string variable and Debug.Print it before you try it with Execute.
It will be easier to spot the bad syntax.

However there are a few obvious problems.

Dates need to be formatted mm/dd/yyyy and delimited with #.

Me.newDay is being written to a numeric field so should not have string delimiters on the value.

 

[pigkachu]

Hi Galaxion,

Thanks for the reply! The debug.print help me a lot as i can see where is the mistake! My problem have been solve! Thank you very much!

 

[Solo712]

I am new in Microsoft Access and currently facing an error. I have no idea why I get this problem. Same code works well on another system.

When i input s898688j (example) in a IC number text box, it will give me run-time error '3061': Too few parameters. Expected 1

When i input 986343dd (example), i will give me run-time error'3075': Syntax error (missing operator) in query expression '986343dd'.

When i input 6986324 (example), the insert query is successful.

I think the error is from IC number. All the other field does not have any problem. I did not do any validation for my IC number yet as well

I have 2 tables

trainee table:

ID (autonumber),
full_name (text),
IC_num (text),
phone_no (text),
company_name (text)
course table:

course_id (autonumber),
IC_num (text),
course_name (text),
L3_survey (number),
L4_survey (number),
start_date (date/time),
end_date (date/time),
no_of_days (number)

Insert query in VBA:

For trainee:

CurrentDb.Execute "INSERT INTO trainee(IC_num, full_name, phone_no, company_name)" & _
" VALUES (" & Me.newIC & ",'" & Me.newName & "','" & _
Me.newPhone & "','" & Me.newCom & "')"

For course:

CurrentDb.Execute "INSERT INTO course(IC_num, course_name, L3_survey, L4_survey, start_date, end_date, no_of_days)" & _
" VALUES (" & Me.newIC & ",'" & Me.newCou & "','" & _
Me.newL3 & "','" & Me.newL4 & "','" & Me.newStrt & "','" & Me.newEnd & "','" & Me.newDay & "')"

I had tried a lot of ways but i have no idea why is it not working!!! :banghead:

You are inserting dates as strings. You need to change use pragmas (#) if you want to refer to date/time data type.


Best,
Jiri

 

[pigkachu]

Thanks Solo712.. It's already solved

 

[pigkachu]

Hi Solo712, thanks for the reply!

Somehow the insert is able to work even though the date is in string. But i guess i shall change it to date format to prevent future error. Thanks!