Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

VBA SQL does not accept textbox parameter

Newbie
  
Join Date: Nov 2007
Posts: 19
#1: Jul 13 '09
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.  
Expert
  
Join Date: Jul 2008
Location: Maryland
Posts: 1,164
#2: Jul 13 '09

re: VBA SQL does not accept textbox parameter

Can you tell us where the value of strPartNum is set?
Newbie
  
Join Date: Nov 2007
Posts: 19
#3: Jul 13 '09

re: VBA SQL does not accept textbox parameter

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
Expert
  
Join Date: Jul 2008
Location: Maryland
Posts: 1,164
#4: Jul 13 '09

re: VBA SQL does not accept textbox parameter

Then the value of strPartNum is not set before you use it in line 14?
Newbie
  
Join Date: Nov 2007
Posts: 19
#5: Jul 13 '09

re: VBA SQL does not accept textbox parameter

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.
Expert
  
Join Date: Jul 2008
Location: Maryland
Posts: 1,164
#6: Jul 13 '09

re: VBA SQL does not accept textbox parameter

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.
Newbie
  
Join Date: Nov 2007
Posts: 19
#7: Jul 13 '09

re: VBA SQL does not accept textbox parameter

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

Tags
record sets, sql, vba

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,387 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved