DoCmd.RunSQL Not Working...

[Sevn]

I have a form with various TextBoxes. The code should convert the form entries into variables, and use the variable values for the DoCmd.RunSQL statement.

I've tried typing in the variables with single quotes, and no quotes, but TextBox values don't seem to be passing to the SQL statement.

Am I typing the variables in correctly, or do I need to layout the code differently?

Please see section in Red.

Private Sub cmdDone_Click()

    Dim WAIT As Double
    Dim dblVT1 As Double, dblVT2A As Double, dblVT2B As Double, dblVT3 As Double
    Dim dblMT1 As Double, dblMT2A As Double, dblMT2B As Double, dblMT3A As Double, dblMT3B As Double, dblMT4 As Double
    Dim dblMTT1 As Double, dblMTT2A As Double, dblMTT2B As Double, dblMTT3A As Double, dblMTT3B As Double, dblMTT4 As Double

    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    
    WAIT = Timer
    
    dblVT1 = Me.txtVT1
    dblVT2A = Me.txtVT2A
    dblVT2B = Me.txtVT2B
    dblVT3 = Me.txtVT3
    dblMT1 = Me.txtMT1
    dblMT2A = Me.txtMT2A
    dblMT2B = Me.txtMT2B
    dblMT3A = Me.txtMT3A
    dblMT3B = Me.txtMT3B
    dblMT4 = Me.txtMT4
    
    dblMTT1 = Me.txtMTT1
    dblMTT2A = Me.txtMTT2A
    dblMTT2B = Me.txtMTT2B
    dblMTT3A = Me.txtMTT3A
    dblMTT3B = Me.txtMTT3B
    dblMTT4 = Me.txtMTT4

        If [Forms]![frm-FilterCPM]![cbCAD] <> 0 Then
            DoCmd.RunSQL "DELETE [tbl-CPM(USD)].* FROM [tbl-CPM(USD)];"
            DoCmd.RunSQL "INSERT INTO [tbl-CPM(USD)] ( ParentTieID, Customer, Market, Volume, GrossSales, GrossPerTon, GTNSales, GTNPerTon, NetSales, PricePerTon, COGS, COGSPerTon, MarginSales, MarginPerTon, MarginPct ) SELECT [4A) CalcPerTon].ParentTieID, [4A) CalcPerTon].Customer, [xref-Market].MarketName, [4A) CalcPerTon].VolCAD AS Volume, [4A) CalcPerTon].gsalescad AS GrossSales, [4A) CalcPerTon].gstoncad AS GrossPerTon, [4A) CalcPerTon].gtnscad AS GTNSales, [4A) CalcPerTon].gtntoncad AS GTNPerTon, [4A) CalcPerTon].nsalescad AS NetSales, [4A) CalcPerTon].nstoncad AS PricePerTon, [4A) CalcPerTon].cogscad AS COGS, [4A) CalcPerTon].cgtoncad AS COGSPerTon, [4A) CalcPerTon].margincad AS MarginSales, [4A) CalcPerTon].mgtoncad AS MarginPerTon, [4A) CalcPerTon].mgpctcad AS MarginPct FROM [4A) CalcPerTon] LEFT JOIN [xref-Market] ON [4A) CalcPerTon].Market = [xref-Market].MarketID;"
                If Me.cboMatType = 1 Then
                    DoCmd.RunSQL "UPDATE [tbl-CPM(USD)] SET [tbl-CPM(USD)].VolumeCat = IIf([Volume]>=5000 And [MarginPct]>0.6,'1A',IIf([Volume]>=5000 And [MarginPct]<0.6 And [MarginPct]>0.45,'1B',IIf([Volume]>=5000 And [MarginPct]>0.2 And [MarginPct]<0.45,'1C',IIf([Volume]>=5000 And [MarginPct]<0.2,'1D',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]>0.6,'2A',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]<0.6 And [MarginPct]>0.45,'2B',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]>0.2 And [MarginPct]<0.45,'2C',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]<0.2,'2D',IIf([Volume]<500 And [MarginPct]>0.6,'3A',IIf([Volume]<500 And [MarginPct]<0.6 And [MarginPct]>0.45,'3B',IIf([Volume]<500 And [MarginPct]>0.2 And [MarginPct]<0.45,'3C',IIf([Volume]<500 And [MarginPct]<0.2,'3D'))))))))))));"
                    DoCmd.OpenForm "frm-CPM(USD)", acNormal, , , acFormReadOnly, acHidden
                        While Timer < WAIT + 10
                            DoEvents  'do nothing
                        Wend
                    DoCmd.OpenReport "rpt-CPM(CA)", acViewPreview, , , acWindowNormal
                Else
                    DoCmd.RunSQL "UPDATE [tbl-CPM(USD)] SET [tbl-CPM(USD)].VolumeCat = IIf([Volume]>=5000 And [MarginPerTon]>60,'1A',IIf([Volume]>=5000 And [MarginPerTon]<60 And [MarginPerTon]>45,'1B',IIf([Volume]>=5000 And [MarginPerTon]>20 And [MarginPerTon]<45,'1C',IIf([Volume]>=5000 And [MarginPerTon]<20,'1D',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]>60,'2A',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]<60 And [MarginPerTon]>45,'2B',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]>20 And [MarginPerTon]<45,'2C',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]<20,'2D',IIf([Volume]<500 And [MarginPerTon]>60,'3A',IIf([Volume]<500 And [MarginPerTon]<60 And [MarginPerTon]>45,'3B',IIf([Volume]<500 And [MarginPerTon]>20 And [MarginPerTon]<45,'3C',IIf([Volume]<500 And [MarginPerTon]<20,'3D'))))))))))));"
                    DoCmd.OpenForm "frm-CPM(USD)", acNormal, , , acFormReadOnly, acHidden
                        While Timer < WAIT + 10
                            DoEvents  'do nothing
                        Wend
                    DoCmd.OpenReport "rpt-CPM(CA)-Alt", acViewPreview, , , acWindowNormal
                End If
        Else
            DoCmd.RunSQL "DELETE [tbl-CPM(USD)].* FROM [tbl-CPM(USD)];"
            DoCmd.RunSQL "INSERT INTO [tbl-CPM(USD)] ( ParentTieID, Customer, Market, Volume, GrossSales, GrossPerTon, GTNSales, GTNPerTon, NetSales, PricePerTon, COGS, COGSPerTon, MarginSales, MarginPerTon, MarginPct ) SELECT [4A) CalcPerTon].ParentTieID, [4A) CalcPerTon].Customer, [xref-Market].MarketName, [4A) CalcPerTon].VolUSD AS Volume, [4A) CalcPerTon].gsalesusd AS GrossSales, [4A) CalcPerTon].gstonusd AS GrossPerTon, [4A) CalcPerTon].gtnsusd AS GTNSales, [4A) CalcPerTon].gtntonusd AS GTNPerTon, [4A) CalcPerTon].nsalesusd AS NetSales, [4A) CalcPerTon].nstonusd AS PricePerTon, [4A) CalcPerTon].cogsusd AS COGS, [4A) CalcPerTon].cgtonusd AS COGSPerTon, [4A) CalcPerTon].marginusd AS MarginSales, [4A) CalcPerTon].mgtonusd AS MarginPerTon, [4A) CalcPerTon].mgpctusd AS MarginPct FROM [4A) CalcPerTon] LEFT JOIN [xref-Market] ON [4A) CalcPerTon].Market = [xref-Market].MarketID;"
                If Me.cboMatType = 1 Then
                    [COLOR="Red"][B]DoCmd.RunSQL "UPDATE [tbl-CPM(USD)] SET [tbl-CPM(USD)].VolumeCat = IIf([Volume]>= 'dblVT1' And [MarginPct]> 'dblMT1','1A',IIf([Volume]>= 'dblVT1' And [MarginPct]< 'dblMT2A' And [MarginPct]> 'dblMT2B','1B',IIf([Volume]>= 'dblVT1' And [MarginPct]> 'dblMT3A' And [MarginPct]< 'dblMT3B','1C',IIf([Volume]>= 'dblVT1' And [MarginPct]< 'dblMT4','1D'," & _
                        "IIf([Volume]< 'dblVT2A' And [Volume]>= 'dblVT2B' And [MarginPct]> 'dblMT1','2A',IIf([Volume]< 'dblVT2A' And [Volume]>= 'dblVT2B' And [MarginPct]< 'dblMT2A' And [MarginPct]> 'dblMT2B','2B',IIf([Volume]< 'dblVT2A' And [Volume]>= 'dblVT2B' And [MarginPct]> 'dblMT3A' And [MarginPct]< 'dblMT3B','2C',IIf([Volume]< 'dblVT2A' And [Volume]>= 'dblVT2B' And [MarginPct]< 'dblMT4','2D'," & _
                        "IIf([Volume]< 'dblVT3' And [MarginPct]> 'dblMT1','3A',IIf([Volume]< 'dblVT3' And [MarginPct]< 'dblMT2A' And [MarginPct]> 'dblMT2A','3B',IIf([Volume]< 'dblVT3' And [MarginPct]> 'dblMT3A' And [MarginPct]< 'dblMT3A','3C',IIf([Volume]< 'dblVT3' And [MarginPct]< 'dblMT4','3D'))))))))))));"[/B][/COLOR]
'                    DoCmd.RunSQL "UPDATE [tbl-CPM(USD)] SET [tbl-CPM(USD)].VolumeCat = IIf([Volume]>=5000 And [MarginPct]>0.6,'1A',IIf([Volume]>=5000 And [MarginPct]<0.6 And [MarginPct]>0.45,'1B',IIf([Volume]>=5000 And [MarginPct]>0.2 And [MarginPct]<0.45,'1C',IIf([Volume]>=5000 And [MarginPct]<0.2,'1D',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]>0.6,'2A',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]<0.6 And [MarginPct]>0.45,'2B',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]>0.2 And [MarginPct]<0.45,'2C',IIf([Volume]<5000 And [Volume]>=500 And [MarginPct]<0.2,'2D',IIf([Volume]<500 And [MarginPct]>0.6,'3A',IIf([Volume]<500 And [MarginPct]<0.6 And [MarginPct]>0.45,'3B',IIf([Volume]<500 And [MarginPct]>0.2 And [MarginPct]<0.45,'3C',IIf([Volume]<500 And [MarginPct]<0.2,'3D'))))))))))));"
                    DoCmd.OpenForm "frm-cpm(usd)", acNormal, , , acFormReadOnly, acHidden
                        While Timer < WAIT + 10
                            DoEvents  'do nothing
                        Wend
                    DoCmd.OpenReport "rpt-cpm(usd)", acViewPreview, , , acWindowNormal
                Else
                    DoCmd.RunSQL "UPDATE [tbl-CPM(USD)] SET [tbl-CPM(USD)].VolumeCat = IIf([Volume]>=5000 And [MarginPerTon]>60,'1A',IIf([Volume]>=5000 And [MarginPerTon]<60 And [MarginPerTon]>45,'1B',IIf([Volume]>=5000 And [MarginPerTon]>20 And [MarginPerTon]<45,'1C',IIf([Volume]>=5000 And [MarginPerTon]<20,'1D',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]>60,'2A',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]<60 And [MarginPerTon]>45,'2B',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]>20 And [MarginPerTon]<45,'2C',IIf([Volume]<5000 And [Volume]>=500 And [MarginPerTon]<20,'2D',IIf([Volume]<500 And [MarginPerTon]>60,'3A',IIf([Volume]<500 And [MarginPerTon]<60 And [MarginPerTon]>45,'3B',IIf([Volume]<500 And [MarginPerTon]>20 And [MarginPerTon]<45,'3C',IIf([Volume]<500 And [MarginPerTon]<20,'3D'))))))))))));"
                    DoCmd.OpenForm "frm-cpm(usd)", acNormal, , , acFormReadOnly, acHidden
                        While Timer < WAIT + 10
                            DoEvents  'do nothing
                        Wend
                    DoCmd.OpenReport "rpt-cpm(usd)-Alt", acViewPreview, , , acWindowNormal
                End If
        End If

    DoCmd.Hourglass False
    DoCmd.SetWarnings True

End Sub

Sevn,

 

[pbaldy]

You need to concatenate the variables in:

"...IIf([Volume]>= " & dblVT1 & " And [MarginPct]> " & dblMT1 & "..."

 

[Sevn]

Thanks again PBaldy, I'll try that out.

 

[Sevn]

Works great.

Thanks a million.

 

[pbaldy]

No problemo, glad it worked for you.