ECEstudent
Registered User.
- Local time
- Today, 15:28
- Joined
- Jun 12, 2013
- Messages
- 153
Hi,
I'm trying to put values into a temporary table for the user to view the work behind a returned calculated value.
Here is my code:
How do I put all values under all 'u' 's into one table? Thank you in advance.
I'm trying to put values into a temporary table for the user to view the work behind a returned calculated value.
Here is my code:
Code:
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
Dim i As Integer
Dim n As Integer
Dim Arr() As String
Dim Arry() As String
Dim x As Variant
Dim y As Variant
Dim varCode As Variant
Dim varCod As Variant
Dim Count As Integer
Dim Count2 As Integer
Dim VldOrdrNbrDestination As Integer
Dim LResponse As Integer
Dim sum As Integer
txtPartNumber = InputBox("Enter Part Number:")
If Not txtPartNumber = "" Then
VldOrdrNbrDestination = 0
If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'") > 0 Then
MsgBox "Part Number Found!"
Count = DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'")
MsgBox Count
Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr= '" & txtPartNumber & "'")
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr")
i = i + 1
rst.MoveNext
Wend
x = Arr
Dim strSQL As String
Dim strTable As String
strTable = "tblTest2"
DoCmd.DeleteObject acTable, strTable
For Each varCode In x
sum = sum + 1
varCode = Replace(varCode, "-", "*")
varCode = Trim(varCode) 'trim extra spaces from varCode
MsgBox "'*" & varCode & "*'"
u = varCode
MsgBox "Look for structure"
If DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'") > 0 Then 'If what the user inputs exists in database, enter if statement
' Count2 = DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'")
' MsgBox "Number of Structures Found = " & Count2 & ""
' Dim strSQL As String
' Dim strTable As String
' strTable = "tblTest2"
' DoCmd.DeleteObject acTable, strTable 'Delete the table if it exists
Set rstt = CurrentDb.OpenRecordset( _
"Select OrderNumber, Item, RepId from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
While rstt.EOF = False
ReDim Preserve Arry(n)
Arry(n) = rstt.Fields("RepId")
n = n + 1
rstt.MoveNext
Wend
y = Arry
For Each varCod In y
If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] = '" & varCod & "'") > 0 Then
If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
VldOrdrNbrDestination = VldOrdrNbrDestination + 1
End If
End If
Next varCod
strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
If sum = 1 Then
CurrentDb.Execute strSQL
End If
Else
MsgBox "Structure Not Found"
End If
Next varCode
MsgBox "Number of orders: '" & VldOrdrNbrDestination & "'"
LResponse = MsgBox("Do you wish to view table?", vbYesNo, "")
If LResponse = vbYes Then
MsgBox "Chose to view table"
' strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
' CurrentDb.Execute strSQL
Else
MsgBox "Cancel table preview"
End If
Else
MsgBox "Part Number Not Found"
End If
Else
MsgBox "Enter Part Number"
End If
End SubHow do I put all values under all 'u' 's into one table? Thank you in advance.
Hope it helps!
