By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,056 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,056 IT Pros & Developers. It's quick & easy.

VBA SQL does not accept textbox parameter

P: 19
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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub cmdGetData()
  2.  
  3. strSQL1 = "SELECT ALL APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ITEM_TYPE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1, "
  4. strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_STATUS_CODE, "
  5. strSQL1 = strSQL1 & "BOM.CST_ITEM_COSTS.ATTRIBUTE1, BOM.CST_ITEM_COSTS.ITEM_COST, "
  6. strSQL1 = strSQL1 & "BOM.CST_COST_TYPES.COST_TYPE, BOM.CST_ITEM_COSTS.BASED_ON_ROLLUP_FLAG, "
  7. strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PLANNER_CODE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UOM_CODE, "
  8. strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INSPECTION_REQUIRED_FLAG, "
  9. strSQL1 = strSQL1 & "INVPROD.IV_ITEM_ROLLING_12_SUM.ITEM_USAGE_QUANTITY "
  10. strSQL1 = strSQL1 & "FROM APPS_ORAFND.MTL_SYSTEM_ITEMS_VL, "
  11. strSQL1 = strSQL1 & "INVPROD.IV_ITEM_ROLLING_12_SUM , BOM.CST_ITEM_COSTS, BOM.CST_COST_TYPES "
  12. strSQL1 = strSQL1 & "WHERE (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=5609 "
  13. strSQL1 = strSQL1 & "AND BOM.CST_COST_TYPES.COST_TYPE='CURRENT' "
  14. strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 LIKE '" & strPartNum & "')) "
  15. strSQL1 = strSQL1 & "AND ((APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=BOM.CST_ITEM_COSTS.INVENTORY_ITEM_ID(+)) "
  16. strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=BOM.CST_ITEM_COSTS.ORGANIZATION_ID(+)) "
  17. strSQL1 = strSQL1 & "AND (BOM.CST_ITEM_COSTS.COST_TYPE_ID=BOM.CST_COST_TYPES.COST_TYPE_ID(+)) "
  18. strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ORGANIZATION_ID=INVPROD.IV_ITEM_ROLLING_12_SUM.ORGANIZATION_ID(+))"
  19. strSQL1 = strSQL1 & "AND (APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID=INVPROD.IV_ITEM_ROLLING_12_SUM.INVENTORY_ITEM_ID(+))) "
  20. strSQL1 = strSQL1 & "GROUP BY APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.ITEM_TYPE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1, "
  21. strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.DESCRIPTION, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INVENTORY_ITEM_STATUS_CODE, "
  22. strSQL1 = strSQL1 & "BOM.CST_ITEM_COSTS.ATTRIBUTE1, BOM.CST_ITEM_COSTS.ITEM_COST, "
  23. strSQL1 = strSQL1 & "BOM.CST_COST_TYPES.COST_TYPE, BOM.CST_ITEM_COSTS.BASED_ON_ROLLUP_FLAG, "
  24. strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PLANNER_CODE, APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.PRIMARY_UOM_CODE, "
  25. strSQL1 = strSQL1 & "APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.INSPECTION_REQUIRED_FLAG, "
  26. strSQL1 = strSQL1 & "INVPROD.IV_ITEM_ROLLING_12_SUM.ITEM_USAGE_QUANTITY "
  27. strSQL1 = strSQL1 & "ORDER BY APPS_ORAFND.MTL_SYSTEM_ITEMS_VL.SEGMENT1 ASC "
  28.  
  29. If Not IsNull(txtQty.Value) And IsNull(txtPart.Value) Then
  30.         MsgBox "Please ensure that you enter a Part #.", vbOKOnly, "DATA REQUIRED"
  31.         cmdRESET
  32.         Exit Sub
  33.  
  34.         Else
  35.         If Not IsNull(txtPart.Value) And IsNull(txtQty.Value) Then
  36.         MsgBox "Please ensure that you enter a valid quantity.", vbOKOnly, "DATA REQUIRED"
  37.         cmdRESET
  38.         Exit Sub
  39.  
  40.         Else
  41.         If IsNull(txtQty.Value) And IsNull(txtPart.Value) Then
  42.         MsgBox "Please ensure that you enter a Part # and a valid quantity.", vbOKOnly, "DATA REQUIRED"
  43.         cmdRESET
  44.         Exit Sub
  45.  
  46.         Else
  47.         Me.txtPart.Value = UCase(Me.txtPart.Value)
  48.         Me.Repaint
  49.         strPartNum = Me.txtPart.Value
  50.         strPartNum = strPartNum & "%"
  51.         Set rst1 = con1.Execute(strSQL1)
  52.  
  53.     End If
  54.    End If
  55.   End If
  56.  
  57.  
  58.   'RstCount = rst1.RecordCount
  59.   RstCount = 0
  60.  
  61.   If rst1.RecordCount = 0 Then
  62.     MsgBox "NO DATA FOUND. CHECK PART # VALUE!", vbOKOnly, "RECORD COUNT"
  63.     cmdRESET
  64.     Exit Sub
  65.  
  66.   Else
  67.   rst1.MoveFirst
  68.     Do Until rst1.EOF
  69.         RstCount = RstCount + 1
  70.         rst1.MoveNext
  71.     Loop
  72.     MsgBox "There were " & RstCount & " records found.", vbOKOnly, "RECORD COUNT"
  73.   End If
  74.  
  75.  
  76. rst1.MoveFirst
  77. strItemType = rst1.Fields(0).Value
  78. strPartNum = rst1.Fields(1).Value
  79. strDesc = rst1.Fields(2).Value
  80. strStatus = rst1.Fields(3).Value
  81. intItemCost = rst1.Fields(5).Value
  82.  
  83.  
  84. intTotalCost = intItemCost * txtQty.Value
  85.  
  86. Me.lblPart.Caption = strPartNum
  87. Me.lblDesc.Caption = strDesc
  88. Me.lblType.Caption = strItemType
  89. Me.lblStat.Caption = strStatus
  90. Me.lblItCt.Caption = intItemCost
  91. Me.txtTlCt.Value = intTotalCost
  92.  
  93. rst1.Close
  94. End Sub
  95.  
  96.  
Jul 13 '09 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,287
Can you tell us where the value of strPartNum is set?
Jul 13 '09 #2

P: 19
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
Jul 13 '09 #3

Expert 100+
P: 1,287
Then the value of strPartNum is not set before you use it in line 14?
Jul 13 '09 #4

P: 19
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.
Jul 13 '09 #5

Expert 100+
P: 1,287
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.
Jul 13 '09 #6

P: 19
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.
Jul 13 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.