Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

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.cboForecast = "Budget" Then
    strType = "BUDFSCHSN"
ElseIf Forms!frmImportMenuFSC.cboForecast = "R1" Then
    strType = "R1FSCHSN"
ElseIf Forms!frmImportMenuFSC.cboForecast = "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.txtForecastYear 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 "
ASKER CERTIFIED SOLUTION
Avatar of UniqueData
UniqueData
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Imoutwest
Imoutwest

For strType as a string:
>& " ' strType ' " &
should be & "'" & strType & "'" & 

Avatar of Rey Obrero (Capricorn1)
try this

SELECT tblTempRetail.SAP," & strType & " AS WorksheetID, " &
Avatar of Sandra Smith

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.....