Still having trouble with DoCmd.RunSQL

[pledbetter]

Misc Screen Shots.docOnClick Event on Save & Exit Button on form frmFormerEmployerEntry:

Private Sub btnSaveAndExit_Click()
    'Make an entry into the Former Employers Table and exit the form
    DoCmd.RunSQL "insert into [tblFormerEmployers] ([numApplID],[txtEmployerName],[txtFmrEmployerPhone],[dteDateFrom],[dteDateTo],[curSalary],[txtSalaryUnit],[txtPosition],[txtLeavingReason]) values(" & numApplicantID & ", txtFldEmployerName,txtFldFmrEmplPhone, dteFldStartDate, dteFldEndDate, curFldOldSalary, cboPayUnits, txtFldPosition,txtFldReasonForLeaving);"
    
    
    With Me
        .txtFldEmployerName = ""
        .txtFldAddreess1 = ""
        .txtFldAddreess2 = ""
        .txtFldCity = ""
        .txtFldState = ""
        .txtFldZipCode = ""
        .txtFldCountry = ""
        .txtFldPostalCode = ""
        .txtFldFmrEmplPhone = ""
        .dteFldStartDate = ""
        .dteFldEndDate = ""
        .curFldOldSalary = 0
        .txtFldPosition = ""
        .txtFldReasonForLeaving = ""
    
    End With
    
    DoCmd.Close acForm, "frmFormerEmployerEntry"
    Forms![frmEmploymentApp].Requery


End Sub

This works perfectly. However, I have more than one Sub that uses all but the last two lines so I want to reuse that code instead of maintaining two copies.

In a Global module "Reused Code":

Sub AppendEmployer(frm As Form)
    
    'Make an entry into the Former Employers Table
    DoCmd.RunSQL "insert into [tblFormerEmployers] ([numApplID],[txtEmployerName],[txtFmrEmployerPhone],[dteDateFrom],[dteDateTo],[curSalary],[txtSalaryUnit],[txtPosition],[txtLeavingReason]) values(" & numApplicantID & ", frm.txtFldEmployerName, frm.txtFldFmrEmplPhone, frm.dteFldStartDate, frm.dteFldEndDate, frm.curFldOldSalary, frm.cboPayUnits, frm.txtFldPosition,frm.txtFldReasonForLeaving);"
    
    With frm
        .txtFldEmployerName = ""
        .txtFldAddreess1 = ""
        .txtFldAddreess2 = ""
        .txtFldCity = ""
        .txtFldState = ""
        .txtFldZipCode = ""
        .txtFldCountry = ""
        .txtFldPostalCode = ""
        .txtFldFmrEmplPhone = ""
        .dteFldStartDate = ""
        .dteFldEndDate = ""
        .curFldOldSalary = 0
        .txtFldPosition = ""
        .txtFldReasonForLeaving = ""
    
    End With


End Sub

Changed Original Sub to:

Private Sub btnSaveAndExit_Click()


    Call AppendEmployer(Me)


    DoCmd.Close acForm, "frmFormerEmployerEntry"
    Forms![frmEmploymentApp].Requery


End Sub

It crashes at DoCmd.RunSQL "insert into [tblFormerEmployers] ([numApplID],[txtEmployerName],[txtFmrEmployerPhone],[dteDateFrom],[dteDateTo],[curSalary],[txtSalaryUnit],[txtPosition],[txtLeavingReason]) values(" & numApplicantID & ", frm.txtFldEmployerName, frm.txtFldFmrEmplPhone, frm.dteFldStartDate, frm.dteFldEndDate, frm.curFldOldSalary, frm.cboPayUnits, frm.txtFldPosition,frm.txtFldReasonForLeaving);"


Not knowing what frm.txtFldEmployerName is, even though my Add Watch shows it ("El Pollo Loco") as plain as day. See attachment.

[Welshgasman]

No attachment?
Also what do you mean by 'it crashes'? What error do you get?

[pledbetter]

I attempted to attach a Word document with a screenshot of the actual message. It must not have happened. The exact response was a dialog box that prompts "Enter Parameter Value". It references "frmtxtFldEmployerName". I'll try again to attach that Word Doc. It is now attached.

[Welshgasman]

I cannot see why TBH.
I would put a few debug.prints in to see values, or set breakpoint and use the immediate window.
I would put the sql into a string variable and debug.print that.

The only way I can see to get around this is to concatenate the values as you do for the first value?

[pledbetter]

I cannot see why TBH.
I would put a few debug.prints in to see values, or set breakpoint and use the immediate window.
I would put the sql into a string variable and debug.print that.

The only way I can see to get around this is to concatenate the values as you do for the first value?

Yeah no sweat, appreciate your time. I'll poke at this during the weekend.

[pbaldy]

You have to concatenate each variable into the string separately. More on delimiters here:

https://www.baldyweb.com/BuildSQL.htm

[pledbetter]

Like I said earlier, the "concatenation" was accepted as part of an OnClick Event on a form. But simply by virtue of moving the exact same concatenated string to its own module, it is somehow now in error, THIS is what I mean by nonintuitive. This is off topic but in another project I was stymied by a change in a query that was not performing a expected. After hours of research I figured out that though the Design View showed the correctly changed query, the SQL View showed the unchanged query. I had to physically modify the SQL View before the query would work! Any more it seems I have to take Microsoft errors with a grain of salt. Is it REALLY a syntax or logic error or is it an undocumented Microsoft "feature"? It makes coding more stressful when you have to question your own tool.

But thanks for the link. I will pore over it this weekend.

[pbaldy]

Do you have an example you can post of this working?

insert into [tblFormerEmployers] ([numApplID],[txtEmployerName],[txtFmrEmployerPhone],[dteDateFrom],[dteDateTo],[curSalary],[txtSalaryUnit],[txtPosition],[txtLeavingReason]) values(" & numApplicantID & ", txtFldEmployerName,txtFldFmrEmplPhone, dteFldStartDate, dteFldEndDate, curFldOldSalary, cboPayUnits, txtFldPosition,txtFldReasonForLeaving);"

I can't imagine that concatenation working. In my experience each value must be concatenated separately.

[pledbetter]

My very first post, where I said it was working perfectly.

[pbaldy]

I understand, I was wondering if you could attach a db here showing it.

[June7]

VBA that does not concatenate reference to form controls to build SQL works when using DoCmd.RunSQL not CurrentDb.Execute. VBA finds the controls when it constructs the SQL and pulls their values.

Will not work in a general module unless you use full form reference with each control.

DoCmd.RunSQL "INSERT INTO Tablename(FieldName) VALUES(Forms!Form1.Text1)"

Otherwise, concatenate each frm.control reference to use the frm object variable.

DoCmd.RunSQL "INSERT INTO Tablename(FieldName) VALUES(" & frm.controlname & ")"

[pledbetter]

Sorry, can't easily. Data is pulled from linked Actian Zen MySQL tables.

[pledbetter]

VBA that does not concatenate reference to form controls to build SQL works when using DoCmd.RunSQL not CurrentDb.Execute. VBA finds the controls when it constructs the SQL and pulls their values.

Will not work in a general module unless you use full form reference with each control.

DoCmd.RunSQL "INSERT INTO Tablename(FieldName) VALUES(Forms!Form1.Text1)"

Otherwise, concatenate each frm.control reference to use the frm object variable.

DoCmd.RunSQL "INSERT INTO Tablename(FieldName) VALUES(" & frm.controlname & ")"

Hi,

Why is your syntax different from mine? Why no square brackets? I tried it that way; it still crashed, but with a different error.

For multiple values, what to do with the commas? Maybe like this? DoCmd.RunSQL "INSERT INTO Tablename(FieldName1, Fieldname2) VALUES(" & frm.controlname & "," & frmcontrolname2 & ")"

[June7]

Square brackets are only needed when names have spaces or special characters (other than underscore) or are reserved words (like Date).

What error?

I have not tested using a passed form object variable. I presume concatenation will work with properly structured code.

Yes, commas within quotes.

If field is text type, use apostrophe delimiters, if date/time use # delimiters. I prefer CurrentDb.Execute, like:

CurrentDb.Execute "INSERT INTO Tablename(FieldName1, FieldName2) VALUES('" & frm.controlname & "', #" & frm.controlname2 & "#)"

[pbaldy]

Good catch June7, I hadn't considered that RunSQL could resolve the form references. I wouldn't do it that way, but interesting none the less.