Help with SQL in VBA statement

[foxy]

Hi,

I have the following statement to write the contents of text boxes on a form into 3 tables:

Private Sub cmdSubmit_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblPerson (forename, surname, organisation_org_ID, address_1, address_2, address_3, postcode, tel_tel, tel_mob, tel_switch, email, job_title, emp_no, rao, sg) VALUES (txtForename, txtSurname, 1, txtAdd1, txtAdd2, txtAdd3, txtPostcode, txtTel, txtMob, txtSwitch, txtEmail, txtJob_Title, txtEmp_No, txtRAO, txtSG)"
DoCmd.RunSQL "INSERT INTO tblUser (person_ID, [ContactPoint UserID], [Pre-requisite training?], [Date Completed], [Access level requested], Shielder) VALUES (11, txtCPUserID, chkTraining, txtTraining_Date, txtAccess_Level, chkShielder)"
DoCmd.RunSQL "INSERT INTO tblShieldingLeads (person_ID, email_secure, password, line_mgr_name, line_mgr_tel, line_mgr_tel_mob) VALUES (11, txtSecure_Email, txtTel_Password, txtLine_Mgr, txtLine_Mgr_Tel, txtLine_Mgr_Mob)"
MsgBox "Record Saved", vbOKOnly
DoCmd.Close acForm, "frmAddNewPerson"
End Sub

When I run it, it only writes to the 'tblPerson' table in the first insert statement and not the other tables.

Any ideas?

Cheers

Foxy

 

[Dennisk]

take you warnings off and single step through each line to find the line in error.

Any routine that that executes SQL statements should use .Execute method, this allows you to use a DBFailOnError Constant which would be used with an error traping routine in the sub.

 

[foxy]

Thanks for that.

I have removed the line to set warnings to false, but still no warnings are appearing when I run the code. It just seems to ignore the second 2 SQL statements.

???

 

[JANR]

If you use Setwarning off you also MUST include SetWarnings TRUE at the end of your sub to enable access messages.

JR

 

[DCrake]

Try remming out the ones that work and see if it stills errors

 

[foxy]

I have solved one issue, it was erroring on foreign keys, but I now have another problem!

I am trying to populate the foreign key with a sub select in the insert statement:

DoCmd.RunSQL "INSERT INTO tblUser ([System UserID],
                                 person_ID,
                                 [Pre-requisite training?],
                                 [Date Completed],
                                 [Access level requested],
                                 Shielder) 
VALUES (txtSysUserID,
            [B](SELECT MAX(per_ID) FROM tblPerson)[/B],
            chkTraining,
            txtTraining_Date,
            txtAccess_Level,
            chkShielder)"

But I get the error:

"Query input must contain at leaast one table or query"

Are subselects possible in a VBA statement? If so, what am I doing wrong?

Cheers

Foxy