Insert with select

[krzysiekk]

Hi

I wrote code:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO scheme_job_it ([job_no],[status]) " _
& "SELECT 'R'+Right([next_job],5), [Forms]![it_form]![Text133] from scheme_job_no"

when I execute I'm not receiving errors and nothing been inserted into table, when I change code to

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO scheme_job_it ([job_no]) " _
& "SELECT 'R'+Right([next_job],5) from scheme_job_no"

Working fine.
Please advise

 

[MarkK]

Use 'VALUES' instead of SELECT, since you don't seem to be acutally drawing the data from a table...

CurrentDb.Execute _
  "INSERT INTO scheme_job_it " & _
    "( [job_no], [status] ) " & _
  "VALUES " & _
    "( 'R" & Right([next_job], 5) & "', '" & [Forms]![it_form]![Text133] & "' )"

...but for clarity, reliability and simplicity I would probably create a temp querydef and supply it with parameters, like...

dim qdf as dao.querydef
dim dbs as dao.database

set dbs = currentdb
set qdf = dbs.createquerydef("", _
  "INSERT INTO scheme_job_it " & _
    "( [job_no], [status] ) " & _
  "VALUES " & _
    "( p0, p1 )"
with qdf
  .parameters(0) = "R" & right(next_job, 5)
  .parameters(1) = Forms!it_form.text133
  .execute dbfailonerror
end with
set qdf = nothing

Cheers,
Mark