Sandra Smith
asked on
Pass a parameter in VBA based on external variable value
I have a docmd in VBA that is an append query, but I need to check an external datapoint and depending on that datapoint, the append query should alter the text that is appended to the table. Am I back to not getting the quotes correct or should the strType be in another format?
If Forms!frmImportMenuFSC.cbo Forecast = "Budget" Then
strType = "BUDFSCHSN"
ElseIf Forms!frmImportMenuFSC.cbo Forecast = "R1" Then
strType = "R1FSCHSN"
ElseIf Forms!frmImportMenuFSC.cbo Forecast = "R2" Then
strType = "R2FSCHSN"
End If
strAppendData = "INSERT INTO tblFSCDetail ( SAP, WorksheetID, ForecastYear, Jan, " & _
"Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec] ) " & _
"SELECT tblTempRetail.SAP," & " ' strType ' " & " AS WorksheetID, " & _
"FORMS!frmImportMenuFSC.tx tForecastY ear AS ForecastYear, " & _
"tblTempRetail.Jan, tblTempRetail.Feb, tblTempRetail.Mar, " & _
"tblTempRetail.Apr , tblTempRetail.May, tblTempRetail.Jun, " & _
"tblTempRetail.Jul, tblTempRetail.Aug, tblTempRetail.Sep, " & _
"tblTempRetail.Oct, tblTempRetail.Nov, tblTempRetail.Dec " & _
"FROM tblTempRetail "
If Forms!frmImportMenuFSC.cbo
strType = "BUDFSCHSN"
ElseIf Forms!frmImportMenuFSC.cbo
strType = "R1FSCHSN"
ElseIf Forms!frmImportMenuFSC.cbo
strType = "R2FSCHSN"
End If
strAppendData = "INSERT INTO tblFSCDetail ( SAP, WorksheetID, ForecastYear, Jan, " & _
"Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec] ) " & _
"SELECT tblTempRetail.SAP," & " ' strType ' " & " AS WorksheetID, " & _
"FORMS!frmImportMenuFSC.tx
"tblTempRetail.Jan, tblTempRetail.Feb, tblTempRetail.Mar, " & _
"tblTempRetail.Apr , tblTempRetail.May, tblTempRetail.Jun, " & _
"tblTempRetail.Jul, tblTempRetail.Aug, tblTempRetail.Sep, " & _
"tblTempRetail.Oct, tblTempRetail.Nov, tblTempRetail.Dec " & _
"FROM tblTempRetail "
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this
SELECT tblTempRetail.SAP," & strType & " AS WorksheetID, " &
SELECT tblTempRetail.SAP," & strType & " AS WorksheetID, " &
ASKER
UniqueData, I do not know what you did because I thought I had tried a version of what wrote, but it did not work I just copied your and it works perfectly. I think I am getting really tired, I had a nap a few days ago..... Thank you both.
nevermind my post, thought you are passing a name of field.....
>& " ' strType ' " &
should be & "'" & strType & "'" &