Insert statement does not work suddenly !

  • Thread starter edisonl via AccessMonster.com
  • Start date
E

edisonl via AccessMonster.com

Hi guys,

Help! My SQL statement suddenly fails me!

I had an Leave application running at the back activate by on_click(), so the
on_click button will activate different function depend on type of leave
choose by the user, Eg: Medical Leave will activate submitmedicalleave(),
Annual Leave will submit submitannualleave().

All of the submit () will have insert statement as indicate below to insert
into pendingleave_table awaiting for their superior approval. Before the
application goes live, it was tested all submit(0 works well, but suddenly it
doesn't seems to work anymore ( the insert statement can't go into
pendingleave_table !)

I had tried to do a compressed and repaired but it still remain the same for
the defective potion.

____________________________________________________________________________________
strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES('" & _
Forms!login_form!ApplicantUserID_Text & "','" & Forms!login_form!
LeaveType_ComboBox.Value & "','" & applytime & "','" & applydate & "','" & _
Forms!login_form!UserName_Text & "','" & FromDate_Text & "','" & ToDate_Text
& "','" & TotalDay_Text & "',0, '0:00', '00/00/00' ,'" & Remarks_Text & "');"

DoCmd.SetWarnings False
CurrentDb.Execute strsql
____________________________________________________________________________________


Is any one willing face with similar problem or got some hint to my issue ?

Regards & God Bless, Edison
 
A

Allen Browne

Suggestions:
a) Use dbFailOnError, so it will notify you if there is an error.

b) After the Execute, test RecordsAffected so you hear whether any records
matched you SQL string.

c) Don't turn off SetWarnings. (It's unnecessary with Execute.)

This kind of thing:

Dim db As DAO.Database
Dim lngCount As Long

strSql = "INSERT ...
Set db = CurrentDb()
db.Execute strSql, dbFailOnError
lngCount = db.RecordsAffected
MsgBox lngCount & " record(s) inserted."
Set Db = Nothing
 
E

edisonl via AccessMonster.com

Hi Mr Allen,

Thanks for replying..

I tried out your code but it says variable not found on>> dbFailOnError

Below is my code:

CurrentDb.Execute strsql, dbFailOnError

Regards & God Bless, Edison


Allen said:
Suggestions:
a) Use dbFailOnError, so it will notify you if there is an error.

b) After the Execute, test RecordsAffected so you hear whether any records
matched you SQL string.

c) Don't turn off SetWarnings. (It's unnecessary with Execute.)

This kind of thing:

Dim db As DAO.Database
Dim lngCount As Long

strSql = "INSERT ...
Set db = CurrentDb()
db.Execute strSql, dbFailOnError
lngCount = db.RecordsAffected
MsgBox lngCount & " record(s) inserted."
Set Db = Nothing
Hi guys,
Click to expand...
[quoted text clipped - 38 lines]
Regards & God Bless, Edison
Click to expand...
Click to expand...
 
P

PieterLinden via AccessMonster.com

The DAO library is not registered. Open your code module, then go to Tools,
References and find and check DAO 3.6, then recompile and re-run. Should
work now.
Hi Mr Allen,

Thanks for replying..

I tried out your code but it says variable not found on>> dbFailOnError

Below is my code:

CurrentDb.Execute strsql, dbFailOnError

Regards & God Bless, Edison
Suggestions:
a) Use dbFailOnError, so it will notify you if there is an error.
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
 
E

edisonl via AccessMonster.com

Hi Piet,

It gave an error saying type mismatch..

By the way, Can I do it in ADO ?

Regards & God Bless, Edison

The DAO library is not registered. Open your code module, then go to Tools,
References and find and check DAO 3.6, then recompile and re-run. Should
work now.
Hi Mr Allen,
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
Top