Full Version: docmd.runSQL works by currentDB.execute doesn't..
UtterAccess Forums > Microsoft® Access > Access Forms
dannyseager
I don't understand what I've done wrong here....
If I use this line of code
docmd.runsql "INSERT INTO tbl_company ( ComTypeID, ComCompanyName ) SELECT [Forms]![Frm_CompanyWizard]![typeID], [Forms]![Frm_CompanyWizard]![CompanyName];"
it works and appends the fields...
but if I use
currentDB.execute "INSERT INTO tbl_company ( ComTypeID, ComCompanyName ) SELECT [Forms]![Frm_CompanyWizard]![typeID], [Forms]![Frm_CompanyWizard]![CompanyName];"
it fails with a error message saying 2 too few parameters.... This is the 2nd time I've had this sort of problem and got it working before (with a suggestion from someone here) but I don't understand why it's not working...
I've tried checking for null's
currentDB.execute "INSERT INTO tbl_company ( ComTypeID, ComCompanyName ) SELECT Nz([Forms]![Frm_CompanyWizard]![typeID],1), Nz([Forms]![Frm_CompanyWizard]![CompanyName],1);"
and even added this before it to see if the fields are null
msgbox [Forms]![Frm_CompanyWizard]![CompanyName]
and it returns the value...
I'm a bit stumped...
xteam
currentDB.execute "INSERT INTO tbl_company ( ComTypeID, ComCompanyName ) SELECT " & [Forms]![Frm_CompanyWizard]![typeID] & ", '" & [Forms]![Frm_CompanyWizard]![CompanyName] & "';"
considered ComTypeID numeric, ComCompanyName string.
Chaga
Or,
urrentDB.execute "INSERT INTO tbl_company (ComTypeID, ComCompanyName ) VALUES ([Forms]![Frm_CompanyWizard]![typeID] , '" & [Forms]![Frm_CompanyWizard]![CompanyName] & "')"
dannyseager
Thanks guy's...
only tried xteams but I assume charles's works as well...
I see what you've done (i.e. make the field a text control) but why will the runsql work and the currentdb.execute not...? shrug.gif
Chaga
Hi Danny,
Odid some research on the web and what I came up to was the following.
The major difference between docmd.runSQL and currentDB.execute, is that the Docmd is just a way of letting Access do what the user should do (thus having all the warning messages) while the CurrentDB.Execute statement runs the SQL directly (is much faster, and has an error handler), the downside is that it has to be an "error free" statement.
Docmd will fix minor errors like it does in the query designer whenever entering criterias (ie adds quotes and octothorpes depending on field type) while CurrentDB.Execute won't.
Adrian, in his SQL statement fixed that issue by adding all required quotes and that's why his solution worked for you.
Hope it was clear (and that I am right frown.gif).
Cheers
dannyseager
Many thanks charles for taking the time.
Chaga
No problem wink.gif, was also curious.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.