Still having trouble with DoCmd.RunSQL

[Micron]

I don't use the locals window so I can't say how it works but I wouldn't bank on that being an indicator of everything. That's because I just read an M$ article on it and wonder if "subvariables" is the reason for that.



OP indicated that table data is being pulled from linked tables or some other type of remote source, so in order to post a db copy, one or more make table queries would be needed to capture a data sample as native tables instead of linked tables. Then the linked tables in the db copy removed, then the native tables renamed to what the linked table names were. That might not be something OP is willing to do.

[Welshgasman]

Well I can get it to work with a Select query?
Again, it is using concatenation.

TestQuery Me

Sub TestQuery(frm As Access.Form)


Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim strSQL As String


Set db = CurrentDb()






strSQL = "SELECT * from TestTransactions WHERE Category LIKE '" & Left(frm.Category, 6) & "*'"
Set qdf = db.CreateQueryDef("TestQuery", strSQL)
DoCmd.OpenQuery qdf.Name
Set db = Nothing


End Sub

[June7]

You are not concatenating as instructed in post 14. You are not using delimiters which is why you get popup prompt. Without apostrophe delimiters to define text string Access tries to read Paul as a fieldname and doesn't find one. If your text had space or special characters the code would not even produce a popup but error.

[pledbetter]

OK here is the solution:

Sub AppendEmployer(frm As Form)Dim strSQLEmp As String
Dim strSQLAddr As String




    strSQLEmpl = "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 & """" & ");"
    strSQLAddr = "Insert into [tblAddresses]([txtName], [txtAddress1], [txtAddress2], [txtCity], [txtState], [txtZip], [txtCountry],[txtPostCode]) values(""" & frm.txtFldEmployerName & """" & "," & """" & frm.txtFldAddreess1 & """" & "," & """" & frm.txtFldAddreess2 & """" & "," & """" & frm.txtFldCity & """" & "," & """" & frm.txtFldState & """" & "," & """" & frm.txtFldZipCode & """" & "," & """" & frm.txtFldCountry & """" & "," & """" & frm.txtFldPostalCode & """" & ");"
    
    'Make an entry into the Former Employers and Addresses Tables and clear the form for more input
    
    DoCmd.RunSQL strSQLEmpl
    DoCmd.RunSQL strSQLAddr
    
    With frm
        .txtFldEmployerName = ""
        .txtFldAddreess1 = ""
        .txtFldAddreess2 = ""
        .txtFldCity = ""
        .txtFldState = ""
        .txtFldZipCode = ""
        .txtFldCountry = ""
        .txtFldPostalCode = ""
        .txtFldFmrEmplPhone = ""
        .dteFldStartDate = "1/1/1900"
        .dteFldEndDate = "12/31/1900"
        .curFldOldSalary = 0
        .cboPayUnits = "Hour"
        .txtFldPosition = ""
        .txtFldReasonForLeaving = ""
    
    End With


End Sub

Isn't that the most non-intuitive, confusing, "quotation mark Hell" solution you've ever seen? Note how everything requires FOUR quotation marks to satisfy Microsoft's SQL engine, yet in another stunning example of inconsistency, THREE were required to start of the string for my second append query. Why three? Who cares. This works. Now on to error trapping...

[Welshgasman]

Well, if you had used single quotes as previously mentioned, it would have been a lot easier.

Only time that will not work, is if you have a single quotation mark wthin the data, like O'Malley

[pledbetter]

Can you show me an example?

[Welshgasman]

Bit hard without the controls, but have a try with

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

and debug.print that and correct as needed.
This also assumes that you are using USA dates, going by your location.

[June7]

Alternative is to open recordset and use recordset AddNew method to create record. Then don't need delimiters and apostrophe in data is not an issue. https://learn.microsoft.com/en-us/of...new-method-dao

When only adding records, open empty recordset: SELECT * FROM table WHERE 0=1

[Minty]

Another alternative is to use a QueryDef and parameters, which removes any need for data typing from form values.

There is very clever coded example here : https://www.access-programmers.co.uk...-query.297170/

[Welshgasman]

Also here is another
https://www.access-programmers.co.uk.../#post-1477897