SQL Select problem

Sevn

Sevn

I trust ME!
Local time
Today, 13:32
Joined
Mar 13, 2008
Messages
97
I've been trying to get this to work, and I'm beginning to think it's impossible.

Can anyone tell me why I continue to get: Syntax Error (missing Operator) for this statement? I've checked all field/object names, and nothings mispelled.:confused:

Am I not suppose to put all these IIf statements? I'm know I could just create a query with all the formulas, and retrieve the results. Although; for job security purposes, I'd like to do all the calculations in the code.

Code: 
Set Rs = CurrentDb.OpenRecordset("SELECT sltQryItemDetail.PID, IIf([Forms]![frmDataColl]![cboFHC1]='D*' And [Forms]![frmDataColl]![txtFrtRtCWT1]=0,'Frt Rate',[NPrice]-[SCost]) AS Margin," & _
    "IIf([Forms]![frmDataColl]![cboFHC1]='D*' And [Forms]![frmDataColl]![txtFrtRtCWT1]=0,'Frt Rate',[Margin]/[NPrice]) AS MarginPct," & _
    "IIf([Forms]![frmDataColl]![txtPrice1]<>0,[Forms]![frmDataColl]![txtPrice1]*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]),0) AS GRevImp," & _
    "IIf([Forms]![frmDataColl]![txtPrice1]=0,0,[Forms]![frmDataColl]![txtPrice1]-([TotWhse]+[UFrt])) AS NPrice," & _
    "IIf([Forms]![frmDataColl]![txtFrtRtCWT1]=0,0,IIf([Forms]![frmDataColl]![cboFHC1]='D*',[UnitTnTm]*[Forms]![frmDataColl]![txtFrtRtCWT1],0)) AS UFrt," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='US' And [Country]='CA',[TotalCost]*[Forms]![frmDataColl]![txtXrate]," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='CA' And [Country]='US',[TotalCost]*(1/[Forms]![frmDataColl]![txtXrate]),[TotalCost]*1)) AS TCost," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='US' And [Country]='CA',[TotWhse]*[Forms]![frmDataColl]![txtXrate]," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='CA' And [Country]='US',[TotWhse]*(1/[Forms]![frmDataColl]![txtXrate]),[TotWhse]*1)) AS TWhse," & _
    "[TCost]-[TWhse] AS SCost, sltQryItemDetail.UnitTnTm, sltQryItemDetail.UnitShipWgt, [NPrice]*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]) AS TNSales," & _
    "[Margin]*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]) AS TGMargin, sltQryItemDetail.Country, sltQryItemDetail.TotWhse, sltQryItemDetail.TotalCost" & _
    "FROM sltQryItemDetail;")
 
For starters, you won't end up with a space between the last field and the word "FROM". You'll find it easier to debug to create and populate a string variable with your SQL string, and then use that in the OpenRecordset line. That lets you add:

Debug.Print VariableName

which lets you examine the finished SQL in the Immediate window.
 
I added a space before the FROM in the statement, and now I get a "Too few parameters". Do the formulas need to be at the end of the SQL?

Not sure I understand the debug.print thing you suggested. My code looks like this, and I don't get any out put to the immediate window you refer to.

Code: 
Option Compare Database

Public Sub RfshItemOne()

If "[Forms]![frmDataColl]![cboItemSKU1]" = Null Then
    Exit Sub
Else

Dim dbPath As String
Dim strSQL As String
Dim Rs As DAO.Recordset
Dim strBP As String, strSKU As String, strPID As String
Dim dblMrgn As Double, dblMrgnP As Double, dblGRI As Double
Dim dblNPrc As Double, dblUFrt As Double, dblTCost As Double
Dim dblTWhse As Double, dblSCost As Double, dblUTT As Double
Dim dblUSW As Double, dblTNS As Double, dblTGM As Double
Dim objDC As Object

dbPath = Application.CurrentProject.Path
strBP = [Forms]![frmDataColl]![txtBPNum1]
strSKU = [Forms]![frmDataColl]![cboItemSKU1]
strPID = "PID" & strBP & strSKU

strSQL = "SELECT sltQryItemDetail.PID, IIf(([Forms]![frmDataColl]![cboFHC1])='D*' And ([Forms]![frmDataColl]![txtFrtRtCWT1])=0,'Frt Rate',([NPrice]-[SCost])) AS Margin," & _
    "IIf(([Forms]![frmDataColl]![cboFHC1])='D*' And ([Forms]![frmDataColl]![txtFrtRtCWT1])=0,'Frt Rate',([Margin]/[NPrice])) AS MarginPct," & _
    "IIf(([Forms]![frmDataColl]![txtPrice1])<>0,([Forms]![frmDataColl]![txtPrice1])*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]),0) AS GRevImp," & _
    "IIf([Forms]![frmDataColl]![txtPrice1]=0,0,[Forms]![frmDataColl]![txtPrice1]-([TotWhse]+[UFrt])) AS NPrice," & _
    "IIf([Forms]![frmDataColl]![txtFrtRtCWT1]=0,0,IIf([Forms]![frmDataColl]![cboFHC1]='D*',[UnitTnTm]*[Forms]![frmDataColl]![txtFrtRtCWT1],0)) AS UFrt," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='US' And [Country]='CA',[TotalCost]*[Forms]![frmDataColl]![txtXrate]," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='CA' And [Country]='US',[TotalCost]*(1/[Forms]![frmDataColl]![txtXrate]),[TotalCost]*1)) AS TCost," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='US' And [Country]='CA',[TotWhse]*[Forms]![frmDataColl]![txtXrate]," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='CA' And [Country]='US',[TotWhse]*(1/[Forms]![frmDataColl]![txtXrate]),[TotWhse]*1)) AS TWhse," & _
    "[TCost]-[TWhse] AS SCost, sltQryItemDetail.UnitTnTm, sltQryItemDetail.UnitShipWgt, [NPrice]*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]) AS TNSales," & _
    "[Margin]*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]) AS TGMargin, sltQryItemDetail.Country, sltQryItemDetail.TotWhse, sltQryItemDetail.TotalCost" & _
    " FROM sltQryItemDetail;"

Set Rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Debug.Print VariableName
'Set Rs = CurrentDb.OpenRecordset("SELECT sltQryItemDetail.PID, IIf(([Forms]![frmDataColl]![cboFHC1])='D*' And ([Forms]![frmDataColl]![txtFrtRtCWT1])=0,'Frt Rate',([NPrice]-[SCost])) AS Margin," & _
    "IIf(([Forms]![frmDataColl]![cboFHC1])='D*' And ([Forms]![frmDataColl]![txtFrtRtCWT1])=0,'Frt Rate',([Margin]/[NPrice])) AS MarginPct," & _
    "IIf(([Forms]![frmDataColl]![txtPrice1])<>0,([Forms]![frmDataColl]![txtPrice1])*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]),0) AS GRevImp," & _
    "IIf([Forms]![frmDataColl]![txtPrice1]=0,0,[Forms]![frmDataColl]![txtPrice1]-([TotWhse]+[UFrt])) AS NPrice," & _
    "IIf([Forms]![frmDataColl]![txtFrtRtCWT1]=0,0,IIf([Forms]![frmDataColl]![cboFHC1]='D*',[UnitTnTm]*[Forms]![frmDataColl]![txtFrtRtCWT1],0)) AS UFrt," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='US' And [Country]='CA',[TotalCost]*[Forms]![frmDataColl]![txtXrate]," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='CA' And [Country]='US',[TotalCost]*(1/[Forms]![frmDataColl]![txtXrate]),[TotalCost]*1)) AS TCost," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='US' And [Country]='CA',[TotWhse]*[Forms]![frmDataColl]![txtXrate]," & _
    "IIf([Forms]![frmDataColl]![txtCustCountry]='CA' And [Country]='US',[TotWhse]*(1/[Forms]![frmDataColl]![txtXrate]),[TotWhse]*1)) AS TWhse," & _
    "[TCost]-[TWhse] AS SCost, sltQryItemDetail.UnitTnTm, sltQryItemDetail.UnitShipWgt, [NPrice]*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]) AS TNSales," & _
    "[Margin]*([Forms]![frmDataColl]![txtAnnlTons1]*[UnitTnTm]) AS TGMargin, sltQryItemDetail.Country, sltQryItemDetail.TotWhse, sltQryItemDetail.TotalCost" & _
    " FROM sltQryItemDetail;")


'objDC = "[Forms]![frmDataColl]"
'dblMrgn = Rs("Margin")
'dblMrgnP = Rs("MarginPct")
'dblGRI = Rs("GRevImp")
'dblNPrc = Rs("NPrice")
'dblUFrt = Rs("UFrt")
'dblTCost = Rs("TCost")
'dblTWhse = Rs("TWhse")
'dblSCost = Rs("SCost")
'dblUTT = Rs("UnitTnTm")
'dblUSW = Rs("UnitShipWgt")
'dblTNS = Rs("TNSales")
'dblTGM = Rs("TGMargin")

'objDC.txtMargin1.SetFocus
'    objDC.txtMargin1.Locked = False
'    objDC.txtMargin1.Text = dblMrgn
'    objDC.txtMargin1.Locked = True
'    objDC.cboNewAcct.SetFocus

End If

End Sub
 
In your case it would be

Debug.Print strSQL

and it should be right above the OpenRecordset line, since the code will stop there if it has a problem. If sltQryItemDetail contains a parameter, you may be running into this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

Though you may also need to concatenate in your form references, like:

strSQL = "SELECT sltQryItemDetail.PID, IIf((" & [Forms]![frmDataColl]![cboFHC1] & ")='D*' And..."

If this is only returning one record, it may be easier to do the calculations in code rather than via the query. An overview of what you're trying to accomplish might lead to a better method.
 
Background:
My project is basically a margin calculator for the sales team.
  1. Reps make various selections, and input text into frmDataColl.
  2. The first part of the module should retrieve 2 frmDataColl values (txtBPNum1, cboItemSKU1), and create a unique ID for SQL.
  3. SQL statement should retrieve the unique record, and perform calculations based on form criteria & query results. This is why you see form & field references in the SQL.
  4. The SQL is based on a simple selection query (no calcs) that I've created in Access (real object, not code). The query has only 2 tables, linked 1 to many. The second table is only so I can bring in the Country for each Branch/Plant.

Status:
I've concatenated the form objects, and do seem to be getting closer. Although I'm still getting a "Too few Parameters" Error, I'm only missing 2 this time, instead of 6. Now that I'm thinking about it, the missing ones may be the TCost & TWhse fields. These fields must be calculated before most of the others can calculate.
I have been told it shouldn't matter, but I'm going to try putting all non calc fields at the front of the SQL, and put the calc fields in order as they are needed.​

Current Code:
Code: 
Option Compare Database

Public Sub RfshItemOne()

If "[Forms]![frmDataColl]![cboItemSKU1]" = Null Then
    Exit Sub
Else

Dim dbPath As String
Dim strSQL As String
Dim Rs As DAO.Recordset
Dim strBP As String, strSKU As String, strPID As String
Dim dblMrgn As Double, dblMrgnP As Double, dblGRI As Double
Dim dblNPrc As Double, dblUFrt As Double, dblTCost As Double
Dim dblTWhse As Double, dblSCost As Double, dblUTT As Double
Dim dblUSW As Double, dblTNS As Double, dblTGM As Double
Dim objDC As Object

dbPath = Application.CurrentProject.Path
strBP = [Forms]![frmDataColl]![txtBPNum1]
strSKU = [Forms]![frmDataColl]![cboItemSKU1]
strPID = "PID" & strBP & strSKU

strSQL = "SELECT sltQryItemDetail.PID, IIf((" & [Forms]![frmDataColl]![cboFHC1] & ")='D*' And (" & [Forms]![frmDataColl]![txtFrtRtCWT1] & ")=0,'Frt Rate',([NPrice]-[SCost])) AS Margin," & _
    "IIf((" & [Forms]![frmDataColl]![cboFHC1] & ")='D*' And (" & [Forms]![frmDataColl]![txtFrtRtCWT1] & ")=0,'Frt Rate',([Margin]/[NPrice])) AS MarginPct," & _
    "IIf((" & [Forms]![frmDataColl]![txtPrice1] & ")<>0,(" & [Forms]![frmDataColl]![txtPrice1] & ")*(" & [Forms]![frmDataColl]![txtAnnlTons1] & "*[UnitTnTm]),0) AS GRevImp," & _
    "IIf(" & [Forms]![frmDataColl]![txtPrice1] & "=0,0," & [Forms]![frmDataColl]![txtPrice1] & "-([TotWhse]+[UFrt])) AS NPrice," & _
    "IIf(" & [Forms]![frmDataColl]![txtFrtRtCWT1] & "=0,0,IIf(" & [Forms]![frmDataColl]![cboFHC1] & "='D*',[UnitTnTm]*" & [Forms]![frmDataColl]![txtFrtRtCWT1] & ",0)) AS UFrt," & _
    "IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='US' And [Country]='CA',[TotalCost]*" & [Forms]![frmDataColl]![txtXrate] & "," & _
    "IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='CA' And [Country]='US',[TotalCost]*(1/" & [Forms]![frmDataColl]![txtXrate] & "),[TotalCost]*1)) AS TCost," & _
    "IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='US' And [Country]='CA',[TotWhse]*" & [Forms]![frmDataColl]![txtXrate] & "," & _
    "IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='CA' And [Country]='US',[TotWhse]*(1/" & [Forms]![frmDataColl]![txtXrate] & "),[TotWhse]*1)) AS TWhse," & _
    "[TCost]-[TWhse] AS SCost, sltQryItemDetail.UnitTnTm, sltQryItemDetail.UnitShipWgt, [NPrice]*(" & [Forms]![frmDataColl]![txtAnnlTons1] & "*[UnitTnTm]) AS TNSales," & _
    "[Margin]*(" & [Forms]![frmDataColl]![txtAnnlTons1] & "*[UnitTnTm]) AS TGMargin, sltQryItemDetail.Country, sltQryItemDetail.TotWhse, sltQryItemDetail.TotalCost" & _
    " FROM sltQryItemDetail" & _
    " WHERE sltQryItemDetail.PID = '" & strPID & "';"

Debug.Print strSQL
Set Rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'objDC = "[Forms]![frmDataColl]"
'dblMrgn = Rs("Margin")
'dblMrgnP = Rs("MarginPct")
'dblGRI = Rs("GRevImp")
'dblNPrc = Rs("NPrice")
'dblUFrt = Rs("UFrt")
'dblTCost = Rs("TCost")
'dblTWhse = Rs("TWhse")
'dblSCost = Rs("SCost")
'dblUTT = Rs("UnitTnTm")
'dblUSW = Rs("UnitShipWgt")
'dblTNS = Rs("TNSales")
'dblTGM = Rs("TGMargin")

'objDC.txtMargin1.SetFocus
'    objDC.txtMargin1.Locked = False
'    objDC.txtMargin1.Text = dblMrgn
'    objDC.txtMargin1.Locked = True
'    objDC.cboNewAcct.SetFocus

End If

End Sub

IW Error output:
Code: 
SELECT sltQryItemDetail.PID, IIf((DLT)='D*' And (1)=0,'Frt Rate',([NPrice]-[SCost])) AS Margin,IIf((DLT)='D*' And (1)=0,'Frt Rate',([Margin]/[NPrice])) AS MarginPct,IIf((3)<>0,(3)*(100*[UnitTnTm]),0) AS GRevImp,IIf(3=0,0,3-([TotWhse]+[UFrt])) AS NPrice,IIf(1=0,0,IIf(DLT='D*',[UnitTnTm]*1,0)) AS UFrt,IIf(US='US' And [Country]='CA',[TotalCost]*0.94,IIf(US='CA' And [Country]='US',[TotalCost]*(1/0.94),[TotalCost]*1)) AS TCost,IIf(US='US' And [Country]='CA',[TotWhse]*0.94,IIf(US='CA' And [Country]='US',[TotWhse]*(1/0.94),[TotWhse]*1)) AS TWhse,[TCost]-[TWhse] AS SCost, sltQryItemDetail.UnitTnTm, sltQryItemDetail.UnitShipWgt, [NPrice]*(100*[UnitTnTm]) AS TNSales,[Margin]*(100*[UnitTnTm]) AS TGMargin, sltQryItemDetail.Country, sltQryItemDetail.TotWhse, sltQryItemDetail.TotalCost FROM sltQryItemDetail WHERE sltQryItemDetail.PID = 'PID100032543';
 
Another helpful method is to copy/paste the SQL from the Immediate window into the SQL view of a new query, and try to run it. You'll often get a more descriptive error than when you try to open a recordset on it.

From the look of it, I'd probably just do the calculations in code rather than trying to do them in the query.
 
You will have to excuse my ignorance, but I'm still pretty new at this.

I thought I was doing the calculations in the code.

Do you mean that I should:
  1. create variables for each frmDataColl object
  2. set variables to frmDataColl values
  3. Do the calcs with variables, instead of ([Forms]![frmDataColl]![ObjectName] inside the SQL
 
After taking a closer look at the IW output, it looks like the SQL is translating the form objects to their values. Therefore; causing the IIF statements to look wrong. I've illustrated an example below.

Original SQL segment:
"IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='US' And [Country]='CA',[TotalCost]*" & [Forms]![frmDataColl]![txtXrate] & ", " & _
"IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='CA' And [Country]='US',[TotalCost]*(1/" & [Forms]![frmDataColl]![txtXrate] & "),[TotalCost]*1)) AS TCost

IW output:
IIf(US='US' And [Country]='CA',[TotalCost]*0.94, IIf(US='CA' And [Country]='US',[TotalCost]*(1/0.94),[TotalCost]*1)) AS TCost

Is this what it's suppose to look like?

I've cut my SQL down to the following:
Code: 
strSQL = "SELECT sltQryItemDetail.PID, sltQryItemDetail.UnitTnTm, sltQryItemDetail.UnitShipWgt, sltQryItemDetail.Country, sltQryItemDetail.TotWhse, sltQryItemDetail.TotalCost, " & _
    "IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='US' And [Country]='CA',[TotalCost]*" & [Forms]![frmDataColl]![txtXrate] & ", " & _
    "IIf(" & [Forms]![frmDataColl]![txtCustCountry] & "='CA' And [Country]='US',[TotalCost]*(1/" & [Forms]![frmDataColl]![txtXrate] & "),[TotalCost]*1)) AS TCost" & _
    " FROM sltQryItemDetail " & _
    " WHERE sltQryItemDetail.PID = '" & strPID & "';"

I'm positive that the error is coming from the TCost & TWhse calcs. With the SQL above, I get "Too Few Parameters", but only missing 1. If I remove the TCost portion, and only SQL the non-calc fields, I get no errors.
 
Last edited:
F'n DUH.
:o

I haven't figured out my problem yet, but I just realized what you were trying to tell me by "doing the calculations in the code".

You're telling me to query only the "real" fields (not calc) in my SQL statement, and then do all my calculations with variables.

Therefore; the steps would be:
  1. Have SQL pull my single record based on unique ID in WHERE statement
  2. Have VBA do all the calculations into variables
  3. Apply variable values to the text boxes on the form

I'll post back with what I come up with.
 
LOL! Yes, that's what I meant. Post back if you get stuck.
 
Ok, here's what I put together. I'm getting an error at the line in red, stating Object Varable or With Block Variable not set.

I'm sure there are better ways to write this, but I'm still learning.

Code: 
Option Compare Database

Public Sub RfshItemOne()

    If "[Forms]![frmDataColl]![cboItemSKU1]" = Null Then
        Exit Sub
    Else

Dim dbPath As String
Dim strSQL As String
Dim Rs As DAO.Recordset
Dim objDC As Object

'Variables from frmDataColl
Dim strBP As String, strSKU As String, strPID As String
Dim strCustCntry As String, strFHC As String
Dim dblFRCWT As Double, dblXrate As Double, dblPrice As Double
Dim dblAnnlTons As Double

'Variables from Query
Dim strBPCntry As String
Dim dblUTT As Double, dblUSW As Double, dblTotWhse As Double
Dim TotCost As Double

'Variables calculated during execution
Dim dblMrgn As Double, dblMrgnP As Double, dblGRI As Double
Dim dblNPrc As Double, dblUFrt As Double, dblTCost As Double
Dim dblTWhse As Double, dblSCost As Double, dblTNS As Double
Dim dblTGM As Double

dbPath = Application.CurrentProject.Path
strBP = [Forms]![frmDataColl]![txtBPNum1]
strSKU = [Forms]![frmDataColl]![cboItemSKU1]
strPID = "PID" & strBP & strSKU

strSQL = "SELECT * FROM sltQryItemDetail WHERE sltQryItemDetail.PID = '" & strPID & "';"
    Debug.Print strSQL
    Set Rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Set frmDataColl Variables
strCustCntry = [Forms]![frmDataColl]![txtCustCountry]
dblXrate = [Forms]![frmDataColl]![txtXrate]
dblFRCWT = [Forms]![frmDataColl]![txtFrtRtCWT1]
strFHC = [Forms]![frmDataColl]![cboFHC1]
strPrice = [Forms]![frmDataColl]![txtPrice1]
dblAnnlTons = [Forms]![frmDataColl]![txtAnnlTons1]

'Set Query Variables
dblUTT = Rs("UnitTnTm")
dblUSW = Rs("UnitShipWgt")
strBPCntry = Rs("Country")
dblTotWhse = Rs("TotWhse")
dblToTCost = Rs("TotalCost")

'Set calculated Variables
If strCustCntry = "US" And strBPCntry = "CA" Then
        dblTCost = dblToTCost * dblXrate And dblTWhse = dblTotWhse * dblXrate
    ElseIf strCustCntry = "CA" And strBPCntry = "US" Then
            dblTCost = dblToTCost * (1 / dblXrate) And dblTWhse = dblTotWhse * (1 / dblXrate)
        Else: dblTCost = dblToTCost * 1 And dblTWhse = dblTotWhse * 1
            End If

dblSCost = TCost - TWhse

If dblFRCWT = 0 Then
        dblUFrt = 0
    ElseIf strFHC = "D*" Then
            dblUFrt = dblUTT * dblFRCWT
        Else: dblUFrt = 0
            End If

If dblPrice = 0 Then
        dblNPrc = 0
    Else: dblNPrc = dblTotWhse + dblUFrt
            End If

dblTNSales = dblNPrc * dblAnnlTons * dblUTT

If strFHC = "D*" And dblFRCWT = 0 Then
        MsgBox "Please enter Freight Rate", vbCritical, "Missing Freight rate"
            objDC = "[Forms]![frmDataColl]"
                With objDC
                    .txtFrtRtCWT1.SetFocus
                End With
    Else: dblMrgn = dblNPrc - dblSCost
'            dblMgrnP = dblMrgn / dblNPrc
            End If

dblTGM = dblMrgn * dblAnnlTons * dblUTT

If dblPrice = 0 Then
        dblGRI = 0
    Else: dblGRI = dblPrice * dblAnnlTons * dblUTT
        End If

'Update Form Fields
[COLOR="Red"][B]objDC = "[Forms]![frmDataColl]"[/B][/COLOR]
    With objDC
        .txtMargin1.SetFocus
        .txtMargin1.Locked = False
        .txtMargin1.Text = dblMrgn
        .txtMargin1.Locked = True
        .txtMarginPct1.SetFocus
        .txtMarginPct1.Locked = False
        .txtMarginPct1.Text = dblMrgnP
        .txtMarginPct1.Locked = True
        .txtGrossRevImp1.SetFocus
        .txtGrossRevImp1.Locked = False
        .txtGrossRevImp1.Text = dblGRI
        .txtGrossRevImp1.Locked = True
        .txtNetPrice1.SetFocus
        .txtNetPrice1.Locked = False
        .txtNetPrice1.Text = dblNPrc
        .txtNetPrice1.Locked = True
        .cboNewAcct.SetFocus
    End With

End If

End Sub
 
Nowhere are you instantiating objDC. You need to instantiate it first by using a

Set objDC = something here


so, it might work to use

Set objDC = "[Forms]![frmDataColl]"
 
Actually, it would be:

Set objDC = [Forms]![frmDataColl]

WithOUT the quotes.
 
My savior is here!

Why not just

With Forms![frmDataColl]

I don't see the point in using the object.
 
Thanks guys.

The code runs without errors, and updates the fields, but they are all zeros.

Here's what I'm working with now, if you have any ideas.

Code: 
Option Compare Database

Public Sub RfshItemOne()

    If "[Forms]![frmDataColl]![cboItemSKU1]" = Null Then
        Exit Sub
    Else

Dim dbPath As String
Dim strSQL As String
Dim Rs As DAO.Recordset

'Variables from frmDataColl
Dim strBP As String, strSKU As String, strPID As String
Dim strCustCntry As String, strFHC As String
Dim dblFRCWT As Double, dblXrate As Double, dblPrice As Double
Dim dblAnnlTons As Double

'Variables from Query
Dim strBPCntry As String
Dim dblUTT As Double, dblUSW As Double, dblTotWhse As Double
Dim TotCost As Double

'Variables calculated during execution
Dim dblMrgn As Double, dblMrgnP As Double, dblGRI As Double
Dim dblNPrc As Double, dblUFrt As Double, dblTCost As Double
Dim dblTWhse As Double, dblSCost As Double, dblTNS As Double
Dim dblTGM As Double

dbPath = Application.CurrentProject.Path
strBP = [Forms]![frmDataColl]![txtBPNum1]
strSKU = [Forms]![frmDataColl]![cboItemSKU1]
strPID = "PID" & strBP & strSKU

strSQL = "SELECT * FROM sltQryItemDetail WHERE sltQryItemDetail.PID = '" & strPID & "';"
    Debug.Print strSQL
    Set Rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Set frmDataColl Variables
strCustCntry = [Forms]![frmDataColl]![txtCustCountry]
dblXrate = [Forms]![frmDataColl]![txtXrate]
dblFRCWT = [Forms]![frmDataColl]![txtFrtRtCWT1]
strFHC = [Forms]![frmDataColl]![cboFHC1]
strPrice = [Forms]![frmDataColl]![txtPrice1]
dblAnnlTons = [Forms]![frmDataColl]![txtAnnlTons1]

'Set Query Variables
dblUTT = Rs("UnitTnTm")
dblUSW = Rs("UnitShipWgt")
strBPCntry = Rs("Country")
dblTotWhse = Rs("TotWhse")
dblToTCost = Rs("TotalCost")

'Set calculated Variables
If strCustCntry = "US" And strBPCntry = "CA" Then
        dblTCost = dblToTCost * dblXrate And dblTWhse = dblTotWhse * dblXrate
    ElseIf strCustCntry = "CA" And strBPCntry = "US" Then
            dblTCost = dblToTCost * (1 / dblXrate) And dblTWhse = dblTotWhse * (1 / dblXrate)
        Else: dblTCost = dblToTCost * 1 And dblTWhse = dblTotWhse * 1
            End If

dblSCost = TCost - TWhse

If dblFRCWT = 0 Then
        dblUFrt = 0
    ElseIf strFHC = "D*" Then
            dblUFrt = dblUTT * dblFRCWT
        Else: dblUFrt = 0
            End If

If dblPrice = 0 Then
        dblNPrc = 0
    Else: dblNPrc = dblTotWhse + dblUFrt
            End If

dblTNSales = dblNPrc * dblAnnlTons * dblUTT

If strFHC = "D*" And dblFRCWT = 0 Then
        MsgBox "Please enter Freight Rate", vbCritical, "Missing Freight rate"
            With [Forms]![frmDataColl]
                .txtFrtRtCWT1.SetFocus
            End With
    Else: dblMrgn = dblNPrc - dblSCost
'            dblMgrnP = dblMrgn / dblNPrc
            End If

dblTGM = dblMrgn * dblAnnlTons * dblUTT

If dblPrice = 0 Then
        dblGRI = 0
    Else: dblGRI = dblPrice * dblAnnlTons * dblUTT
        End If

'Update Form Fields
With [Forms]![frmDataColl]
    .txtMargin1.SetFocus
    .txtMargin1.Locked = False
    .txtMargin1.Text = dblMrgn
    .txtMargin1.Locked = True
    .txtMarginPct1.SetFocus
    .txtMarginPct1.Locked = False
    .txtMarginPct1.Text = dblMrgnP
    .txtMarginPct1.Locked = True
    .txtGrossRevImp1.SetFocus
    .txtGrossRevImp1.Locked = False
    .txtGrossRevImp1.Text = dblGRI
    .txtGrossRevImp1.Locked = True
    .txtNetPrice1.SetFocus
    .txtNetPrice1.Locked = False
    .txtNetPrice1.Text = dblNPrc
    .txtNetPrice1.Locked = True
    .cboNewAcct.SetFocus
End With

End If

End Sub
 
For starters, you don't need to unlock the field to put a value in it via code. Second, you don't have to set focus to a control to put a value in it, you just use the default .Value property instead of the .Text property. So, instead of 4 lines each, you just need one:

.txtMargin1.Value = dblMrgn

Have you set a breakpoint and stepped through the code so you can see how the calculations are going, and checked the values as code executes?

By the way, personally I would not mix the one line and block formats of the If/Then statements. Not saying it's causing a problem, but to me it confuses the code.
 
I want the fields locked by default, so they must be unlocked before update.

I will remove the Set Focus lines, if not needed.

As for the If/Then statements, I assume you mean they should look like this:
Code: 
If strCustCntry = "US" And strBPCntry = "CA" Then
        dblTCost = dblToTCost * dblXrate
        dblTWhse = dblTotWhse * dblXrate

I'm walking through the calculations, and one of them isn't grabbing a value, and therefore causing the succeeding variables to be 0. I will investigate this, and post back with results.
 
What I'm saying is that you can still set the value without unlocking the textbox. The user can't, but code can. You only need one line to set each. This set the value of a textbox, which was locked and did not have focus:

Me.txtLast = 777

The one that caught my eye was:

Code: 
If dblPrice = 0 Then
        dblGRI = 0
    Else: dblGRI = dblPrice * dblAnnlTons * dblUTT
        End If

I would do this (I also indent differently, which is again a matter of preference):

Code: 
If dblPrice = 0 Then
  dblGRI = 0
Else
  dblGRI = dblPrice * dblAnnlTons * dblUTT
End If
 
YAHOOOOOOOOOOOOOOOOOOOOO!

It worked!

Thanks for all your help.

One more question...
There will be 25 lines of products on the completed form, to be able to process several products all at once.

My goal was to get one piece of working code, and call the procedure when any of the user fields are changed. I know I can just call the module from the AfterUpdate procedure for each, but how can I tell it to only update the line item being changed?

For example; if a SalesRep has 10 items listed, and changes the price for item #5. I would like the code to know that #5 is being changed, and update it accordingly. There are several variables that are dependent on line item details (SKU, Price, Freight, etc.). These variables would need to be determined, based on which user field is changed. If user is changes txtPrice5, then all variables for item #5 would need to be reprocessed, and values updated.

Let me know, if I'm not clear, or if this should be a new thread.

Working Code...
Code: 
Option Compare Database

Public Sub RfshItemOne()

    If "[Forms]![frmDataColl]![cboItemSKU1]" = Null Then
        Exit Sub
    Else

Dim dbPath As String
Dim strSQL As String
Dim Rs As DAO.Recordset

'Variables from frmDataColl
Dim strBP As String, strSKU As String, strPID As String
Dim strCustCntry As String, strFHC As String
Dim dblFRCWT As Double, dblXrate As Double, dblPrice As Double
Dim dblAnnlTons As Double

'Variables from Query
Dim strBPCntry As String
Dim dblUTT As Double, dblUSW As Double, dblTotWhse As Double
Dim TotCost As Double

'Variables calculated during execution
Dim dblMrgn As Double, dblMrgnP As Double, dblGRI As Double
Dim dblNPrc As Double, dblUFrt As Double, dblTCost As Double
Dim dblTWhse As Double, dblSCost As Double, dblTNS As Double
Dim dblTGM As Double

dbPath = Application.CurrentProject.Path
strBP = [Forms]![frmDataColl]![txtBPNum1]
strSKU = [Forms]![frmDataColl]![cboItemSKU1]
strPID = "PID" & strBP & strSKU

strSQL = "SELECT * FROM sltQryItemDetail WHERE sltQryItemDetail.PID = '" & strPID & "';"
    Debug.Print strSQL
    Set Rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Set frmDataColl Variables
strCustCntry = [Forms]![frmDataColl]![txtCustCountry]
dblXrate = [Forms]![frmDataColl]![txtXrate]
dblFRCWT = [Forms]![frmDataColl]![txtFrtRtCWT1]
strFHC = [Forms]![frmDataColl]![cboFHC1]
dblPrice = [Forms]![frmDataColl]![txtPrice1]
dblAnnlTons = [Forms]![frmDataColl]![txtAnnlTons1]

'Set Query Variables
dblUTT = Rs("UnitTnTm")
dblUSW = Rs("UnitShipWgt")
strBPCntry = Rs("Country")
dblTotWhse = Rs("TotWhse")
dblToTCost = Rs("TotalCost")

'Set calculated Variables
If strCustCntry = "US" And strBPCntry = "CA" Then
        dblTCost = dblToTCost * dblXrate
        dblTWhse = dblTotWhse * dblXrate
    ElseIf strCustCntry = "CA" And strBPCntry = "US" Then
        dblTCost = dblToTCost * (1 / dblXrate)
        dblTWhse = dblTotWhse * (1 / dblXrate)
    Else
        dblTCost = dblToTCost * 1
        dblTWhse = dblTotWhse * 1
End If
'------------------------->
dblSCost = dblTCost - dblTWhse
'------------------------->
If dblFRCWT = 0 Then
        dblUFrt = 0
    ElseIf strFHC Like "D*" Then
        dblUFrt = dblUTT * dblFRCWT
    Else
        dblUFrt = 0
End If
'------------------------->
If dblPrice = 0 Then
        dblNPrc = 0
    Else
        dblNPrc = dblTotWhse + dblUFrt
End If
'------------------------->
dblTNSales = dblNPrc * dblAnnlTons * dblUTT
'------------------------->
If strFHC Like "D*" And dblFRCWT = 0 Then
        MsgBox "Please enter Freight Rate", vbCritical, "Missing Freight rate"
            With [Forms]![frmDataColl]
                .txtFrtRtCWT1.SetFocus
            End With
    Else
        dblMrgn = dblNPrc - dblSCost
        dblMrgnP = dblMrgn / dblNPrc
End If
'------------------------->
dblTGM = dblMrgn * dblAnnlTons * dblUTT
'------------------------->
If dblPrice = 0 Then
        dblGRI = 0
    Else
        dblGRI = dblPrice * dblAnnlTons * dblUTT
End If
'------------------------->

'Update Form Fields
With [Forms]![frmDataColl]
    .txtMargin1.Value = dblMrgn
    .txtMarginPct1.Value = dblMrgnP
    .txtGrossRevImp1.Value = dblGRI
    .txtNetPrice1.Value = dblNPrc
    .cboNewAcct.SetFocus
End With

End If

End Sub
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom