Combining two SQL strings.

[PuddinPie]

Hello,

What I am trying to do is to have two SQL strings run at the same time so that the information all inputs into one row. Here's what I have.

' These lines start the SQL string to copy all the data into the table.
strSQL = "INSERT INTO tblAllRequestData (FormType,RequestersName,RequestersPhoneNumber," & _
"RequestersLLID,RequestersEmail,EMPFirstName,EMPTeam,EMPLastName,EMPNewTerminalNum," & _
"EMPLLID,EMPNewDeskNum,EMPExpectedStartDate,EMPRole,EMPNewCostCenter,EMPTelephoney," & _
"EMPCompType,EMPSoftSysAccessProfile,EMPDashboardProfile,EMPAdditionalSoftSysAccess," & _
"EMPSpecialAccessFurniture)" & _
"VALUES ([Forms].[frmNewEMPInternal].[txtFormType],[Forms].[frmNewEMPInternal].[txtReqName]," & _
"[Forms].[frmNewEMPInternal].[txtReqPhone],[Forms].[frmNewEMPInternal].[txtReqLLID]," & _
"[Forms].[frmNewEMPInternal].[txtReqEmail],[Forms].[frmNewEMPInternal].[txtEMPFirstName]," & _
"[Forms].[frmNewEMPInternal].[txtEMPTeam],[Forms].[frmNewEMPInternal].[txtEMPLastName]," & _
"[Forms].[frmNewEMPInternal].[txtEMPTerminalNum],[Forms].[frmNewEMPInternal].[txtEMPLLID]," & _
"[Forms].[frmNewEMPInternal].[txtEMPDeskNum],[Forms].[frmNewEMPInternal].[txtEMPStartDate]," & _
"[Forms].[frmNewEMPInternal].[txtEMPRole],[Forms].[frmNewEMPInternal].[txtEMPCostCenter]," & _
"[Forms].[frmNewEMPInternal].[frmTelephony],[Forms].[frmNewEMPInternal].[FrmComputer]," & _
"[Forms].[frmNewEMPInternal].[cboAccessProfile],[Forms].[frmNewEMPInternal].[cboDashboardProfile]," & _
"[Forms].[frmNewEMPInternal].[txtAdditionalAccess],[Forms].[frmNewEMPInternal].[txtSpecialAccess])"

If Me!txtOldNumber.Visible = True Then
strSQLOldNum = "INSERT INTO tblAllRequestData (EMPTelephoneyOldNum) VALUES ([Forms].[frmNewEMPInternal].[txtOldNumber])"
Else
End If

strTotalSQL = "strSQL" & "strSQLOldNum"

DoCmd.RunSQL strTotalSQL

MsgBox "Your request has been submitted"
DoCmd.Close

I have two strings. One conditional and one static. I would like to have them both run at the same time but it errors out. As you can see above I've tryed to combine both strings into one string but that's not working either.

Please Help.

 

[the_net_2.0]

first of all, you can't run two statements at once. execution is progressive. secondly, you can NEVER include control or object references inside of a string without some sort of concatenation effort. That's basic syntax.

for example, this part of your code:

"EMPSpecialAccessFurniture)" & _
"VALUES ([Forms].[frmNewEMPInternal].[txtFormType].......

should look like this:

"EMPSpecialAccessFurniture)" & _
[COLOR="Red"]" V[/COLOR]ALUES ('" & [Forms]![frmNewEMPInternal]![txtFormType] & "', '" & NEXT REF & "', '" & etc, etc..

try that first. those are your first steps.

also notice the red syntax. The basic parsing and spacing of the statement is in error as well.