VBA SQL does not accept textbox parameter

[dbrother]

Hello all,
I'm requesting assistance with this spot of code. I have a form that I want to calculate the standard cost for our inventory items based on what item is entered into the form's textbox. In the SQL code, I can "hard code" a part number and the SQL query works fine, but when entering the strPartNum variable, it gives me no records returned in my message box @ line #62. Line 14 is where the part number variable is introduced. I've tried with and without the "%" wildcard to no avail. I've also written a pass-through query (data resides on corporate server) and it works as well with a hard-coded part number.

Microsoft Access ver 2003 SP3
Windown XP Pro Sp2

Any assistance appreciated.

Here's the code:

Public Sub cmdGetData()

strSQL1 = "SELECT ALL APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ITEM_TYPE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1, "
strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_STATUS_CODE, "
strSQL1 = strSQL1 & "BOM.CST_ITEM_COSTS.ATTRIBUTE1, BOM.CST_ITEM_COSTS.ITEM_COST, "
strSQL1 = strSQL1 & "BOM.CST_COST_TYPES.COST_TYPE, BOM.CST_ITEM_COSTS.BASED_ON_ROLLUP_FLAG, "
strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PLANNER_CODE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UOM_CODE, "
strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INSPECTION_REQUIRED_FLAG, "
strSQL1 = strSQL1 & "INVPROD.IV_ITEM_ROLLING_12_SUM.ITEM_USAGE_QUANTITY "
strSQL1 = strSQL1 & "FROM APPS_ORAFND.MTL_SYSTEM_ITEMS_VL, "
strSQL1 = strSQL1 & "INVPROD.IV_ITEM_ROLLING_12_SUM , BOM.CST_ITEM_COSTS, BOM.CST_COST_TYPES "
strSQL1 = strSQL1 & "WHERE (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=5609 "
strSQL1 = strSQL1 & "AND BOM.CST_COST_TYPES.COST_TYPE='CURRENT' "
strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 LIKE '" & strPartNum & "')) "
strSQL1 = strSQL1 & "AND ((APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=BOM.CST_ITEM_COSTS.INVENTORY_ITEM_ID(+)) "
strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=BOM.CST_ITEM_COSTS.ORGANIZATION_ID(+)) "
strSQL1 = strSQL1 & "AND (BOM.CST_ITEM_COSTS.COST_TYPE_ID=BOM.CST_COST_TYPES.COST_TYPE_ID(+)) "
strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=INVPROD.IV_ITEM_ROLLING_12_SUM.ORGANIZATION_ID(+))"
strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=INVPROD.IV_ITEM_ROLLING_12_SUM.INVENTORY_ITEM_ID(+))) "
strSQL1 = strSQL1 & "GROUP BY APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ITEM_TYPE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1, "
strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_STATUS_CODE, "
strSQL1 = strSQL1 & "BOM.CST_ITEM_COSTS.ATTRIBUTE1, BOM.CST_ITEM_COSTS.ITEM_COST, "
strSQL1 = strSQL1 & "BOM.CST_COST_TYPES.COST_TYPE, BOM.CST_ITEM_COSTS.BASED_ON_ROLLUP_FLAG, "
strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PLANNER_CODE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UOM_CODE, "
strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INSPECTION_REQUIRED_FLAG, "
strSQL1 = strSQL1 & "INVPROD.IV_ITEM_ROLLING_12_SUM.ITEM_USAGE_QUANTITY "
strSQL1 = strSQL1 & "ORDER BY APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 ASC "

If Not IsNull(txtQty.Value) And IsNull(txtPart.Value) Then
        MsgBox "Please ensure that you enter a Part #.", vbOKOnly, "DATA REQUIRED"
        cmdRESET
        Exit Sub
        
        Else
        If Not IsNull(txtPart.Value) And IsNull(txtQty.Value) Then
        MsgBox "Please ensure that you enter a valid quantity.", vbOKOnly, "DATA REQUIRED"
        cmdRESET
        Exit Sub
        
        Else
        If IsNull(txtQty.Value) And IsNull(txtPart.Value) Then
        MsgBox "Please ensure that you enter a Part # and a valid quantity.", vbOKOnly, "DATA REQUIRED"
        cmdRESET
        Exit Sub
        
        Else
        Me.txtPart.Value = UCase(Me.txtPart.Value)
        Me.Repaint
        strPartNum = Me.txtPart.Value
        strPartNum = strPartNum & "%"
        Set rst1 = con1.Execute(strSQL1)
              
    End If
   End If
  End If
  
    
  'RstCount = rst1.RecordCount
  RstCount = 0
  
  If rst1.RecordCount = 0 Then
    MsgBox "NO DATA FOUND. CHECK PART # VALUE!", vbOKOnly, "RECORD COUNT"
    cmdRESET
    Exit Sub
  
  Else
  rst1.MoveFirst
    Do Until rst1.EOF
        RstCount = RstCount + 1
        rst1.MoveNext
    Loop
    MsgBox "There were " & RstCount & " records found.", vbOKOnly, "RECORD COUNT"
  End If


rst1.MoveFirst
strItemType = rst1.Fields(0).Value
strPartNum = rst1.Fields(1).Value
strDesc = rst1.Fields(2).Value
strStatus = rst1.Fields(3).Value
intItemCost = rst1.Fields(5).Value


intTotalCost = intItemCost * txtQty.Value

Me.lblPart.Caption = strPartNum
Me.lblDesc.Caption = strDesc
Me.lblType.Caption = strItemType
Me.lblStat.Caption = strStatus
Me.lblItCt.Caption = intItemCost
Me.txtTlCt.Value = intTotalCost

rst1.Close
End Sub

[ChipR]

Can you tell us where the value of strPartNum is set?

[dbrother]

Lines 29-50 check 2 textboxes (txtPartNum and txtQty) for null and not null values and gives the appropriate error message box if necessary. The last else (lines 49 and 50, which means all inputs are valid) sets the value for strPartNum and then adds the % wildcard character.

Thanks for the prompt response.
dbrother

[ChipR]

Then the value of strPartNum is not set before you use it in line 14?

[dbrother]

Nope, it's called in line 51 when says to execute the strSQL1 statement. You have to set the SQL string to something before you call it else you receive an error stating so. It's like declaring a variable before you use it.

[ChipR]

Sorry, I thought it would be clear. At line 14, the value (nothing) of strPartNum is inserted in the string. Later changing strPartNum will not change the value of strSQL1. Just move lines 3-27 after line 50, before line 51.

[dbrother]

Thanks! That solved it.

I thought that I'd used the sql string like that before, where it was altered later and then the variable replaced itself, but it doesn't seem so....

Thanks again.