Still having trouble with DoCmd.RunSQL

[pledbetter]

OK I will try the various suggestions and get back to you guys this evening.

[pledbetter]

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

What error?

Please see attachment. When I run my code as-is, I get my aforementioned error. When I change the concatenation to what was recommended, I now get an "Enter Parameter" prompt that lists the content of the control as a parameter! I have a text box whose full reference in my instance is frm.txtFldEmployerName. Its current contents is "Paul". It is now asking me for the value of a "Parameter" called "Paul". WTH, man?!

DoCmd-RunSQL Bugs.doc

[Welshgasman]

Please see attachment. When I run my code as-is, I get my aforementioned error. When I change the concatenation to what was recommended, I now get an "Enter Parameter" prompt that lists the content of the control as a parameter! I have a text box whose full reference in my instance is frm.txtFldEmployerName. Its current contents is "Paul". It is now asking me for the value of a "Parameter" called "Paul". WTH, man?!

DoCmd-RunSQL Bugs.doc

This is your code

When I change the “concatenation” as recommended: … values(" & numApplicantID & "," & frm.txtFldEmployerName & ",… <and so on>…

Put it all in a string variable, then you can debug.print it.
Paul would need to be 'Paul' as it is a string. Without those, then Access thinks it is a variable, which it could well be. That is why you get the prompt.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

Look up the syntax when you cannot get it to work.

https://learn.microsoft.com/en-us/of...oft-access-sql

[Minty]

It might be worth having a quick read here:
https://medium.com/@NoLongerSet/avoi...s-c036348dd732

[pledbetter]

This is your code

When I change the “concatenation” as recommended: … values(" & numApplicantID & "," & frm.txtFldEmployerName & ",… <and so on>…

Put it all in a string variable, then you can debug.print it.
Paul would need to be 'Paul' as it is a string. Without those, then Access thinks it is a variable, which it could well be. That is why you get the prompt.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

Look up the syntax when you cannot get it to work.

https://learn.microsoft.com/en-us/of...oft-access-sql

Again. My original syntax is correct. Running my original code in a OnClick event on a button, it works perfectly. I fact, I have TWO buttons that run almost the identical code; one does the append and exits, and the other appends and re-presents a form to collect more data to be uploaded. By virtue of simply transferring that exact, functioning code to its own module and passing the reference of its parent form to that module, the program crashes with those (to me), nonsensical errors. It has something to do with relocating the code to it's own module. As typical with Microsoft, somehow the rules change simply by doing so. I'm sure there is a super secret caveat involved with this. Like maybe I need use an Execute command or create an actual Append Query and run it or some such other nonsense. Again, the syntax is correct in the context of being in the OnClick event of a button. But I have two buttons what run 90% of the same code, so I just wanted to mode that common code to it's own module.

[pledbetter]

[QUOTE=Welshgasman;527986]This is your code

When I change the “concatenation” as recommended: … values(" & numApplicantID & "," & frm.txtFldEmployerName & ",… <and so on>…

Put it all in a string variable, then you can debug.print it.

Paul would need to be 'Paul' as it is a string. Without those, then Access thinks it is a variable, which it could well be. That is why you get the prompt.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

Look up the syntax when you cannot get it to work.

https://learn.microsoft.com/en-us/of...oft-access-sql


the string "Paul" is the actual contents of the text field "txtFldEmployerName" on the form "frm" that was passed to the module. Sorry, I'm not sure what you are trying to say here.

[Welshgasman]

the string "Paul" is the actual contents of the text field "txtFldEmployerName" on the form "frm" that was passed to the module. Sorry, I'm not sure what you are trying to say here.

Because your concatenation is flawed, you are presenting the contents of that control as a variable.
Access does not know that variable, it does not even know it does not even exist, which it does not, but it cannot find it, so it asks you for the value.

AGAIN!! put it all into a string variable and Debug.Print that until you get it correct.

Access is only doing what you are telling it to do/use, whether you regard that as intuitive or not.

[Micron]

I'm sure there is a super secret caveat involved with this

I would imagine that would be Scope (and no, not the mouthwash). If you're not aware of it, I suggest you check it out because the problem you describe could certainly be caused by it. It's been a few days since I read what I think is pertinent, and that was about not being able to refer to a control on a form that you've passed to code in a standard module without including its parent (in this case, frm). However you said you tried that but I don't see where you posted that attempt so that anyone could review it. Maybe do that? Or post a zipped db copy and let responders work with what you have. You could remove everything from the copy that is not needed to recreate the issue - just make sure the problem is presented in the copy as well.

[pledbetter]

It might be worth having a quick read here:
https://medium.com/@NoLongerSet/avoi...s-c036348dd732

Thanks for the link. I looked at it. It has merit. I will likely comply and start doing things that way. Not sure it will still work passing a form object to a module though. But I have a question. Why would one offer a tool, then offer another tool, suggest the first tool is bad, but don't bother to remove it? If DoCmd.RunSQL is so bad, why even have it? In what circumstances are one command preferred over another? In my case, I live with the errors and warnings in development, but when I submit my final product, I simply turn off those Action Query messages in Access itself so my end users are not bothered by them.

[pledbetter]

My concatenation is not flawed because it works in the context of being in the OnClick Event of a button on the form in question:

Private Sub btnSaveAndExit_Click()    'Make an entry into the Former Employers and Addresses Tables 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);"
    
    DoCmd.RunSQL "Insert into [tblAddresses] ([txtName], [txtAddress1], [txtAddress2], [txtCity], [txtState], [txtZip], [txtCountry], [txtPostCode]) values (txtFldEmployerName, txtFldAddreess1,txtFldAddreess2,txtFldCity,txtFldState,txtFldZipCode,txtFldCountry,txtFldPostalCode);"
    
    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

[Welshgasman]

I would imagine that would be Scope (and no, not the mouthwash). If you're not aware of it, I suggest you check it out because the problem you describe could certainly be caused by it. It's been a few days since I read what I think is pertinent, and that was about not being able to refer to a control on a form that you've passed to code in a standard module without including its parent (in this case, frm). However you said you tried that but I don't see where you posted that attempt so that anyone could review it. Maybe do that? Or post a zipped db copy and let responders work with what you have. You could remove everything from the copy that is not needed to recreate the issue - just make sure the problem is presented in the copy as well.

What is strange, is that the form properties are available in the sub when passed as a form object as you would expect, yet Access is not recognising them, which is what the O/P is reporting.

[Welshgasman]

My concatenation is not flawed because it works in the context of being in the OnClick Event of a button on the form in question:

Private Sub btnSaveAndExit_Click()    'Make an entry into the Former Employers and Addresses Tables 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);"


    
    DoCmd.RunSQL "Insert into [tblAddresses] ([txtName], [txtAddress1], [txtAddress2], [txtCity], [txtState], [txtZip], [txtCountry], [txtPostCode]) values (txtFldEmployerName, txtFldAddreess1,txtFldAddreess2,txtFldCity,txtFldState,txtFldZipCode,txtFldCountry,txtFldPostalCode);"
    
    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

It is flawed, as what you use in the form is NOTHING like what you are trying to use in the sub
In the sub, you need to concatenate all the form controls as values, and do it correctly, which you are NOT doing.

[Micron]

the form properties are available in the sub when passed as a form object as you would expect, yet Access is not recognising them

I'd say that's because the value of a control is not a form property - it is a property of the control. I have nothing to test with, which is why I suggested posting a db copy, but I think June7 mentioned this possibility first, somewhere around post 6. Or am I not understanding what you're saying?

[Minty]

Thanks for the link. I looked at it. It has merit. I will likely comply and start doing things that way. Not sure it will still work passing a form object to a module though. But I have a question. Why would one offer a tool, then offer another tool, suggest the first tool is bad, but don't bother to remove it? If DoCmd.RunSQL is so bad, why even have it? In what circumstances are one command preferred over another? In my case, I live with the errors and warnings in development, but when I submit my final product, I simply turn off those Action Query messages in Access itself so my end users are not bothered by them.

There are always multiple ways to skin a cat in Access, some are more suitable than others depending on the task.

Personally, I always use CurrentDb.Execute , I dislike turning warnings on and off, and prefer my code to simply error whilst developing so I can see where it's gone wrong.
I also always put the concatenation into a variable, as even thought I've been doing this for years I still get it wrong, or don't pass the value I'm expecting.
It also allows me to format it in a very readable fashion, so if I need to alter it later it's easy to see where and how.

E.g. (SQLDate is a function that formats the date correctly as #2024-09-30# and again makes life easier to read.)

    Dim strSQL as String

    'insert the new record
    strSQL = "INSERT INTO tb_AllocationLists (AllocationList_Author, AllocationList_ModifiedBy, AllocationList_DateModified,AllocationList_DateCreated, AllocationList_Name) "
    strSQL = strSQL & "VALUES (" & iUser & ", " & iUser & ", " & SQLDate(Now()) & ", " & SQLDate(Now()) & ", '" & strListName & "')"

    Debug.Print strSQL


    Set db = CurrentDb
    db.Execute strSQL, dbFailOnError

[Welshgasman]

Yes, we need a DB to work with. I am not updating my tables just to work this out.
Either upload a DB with instructions on how to recreate, or I will just drop out.

Only needs to have whaever is needed to recreate the issue.

@Micron I probably used the incorrect word. The control values are present in the Locals window.