ECEstudent
Registered User.
- Local time
- Today, 09:20
- Joined
- Jun 12, 2013
- Messages
- 153
I really need you guys' help on this...I have no idea what the problem is with my code. I have 3 tables, 2 of them are related the other one is not because there's modification involved and bla bla. just ignore table 1. But what is supposed to happen is that user inputs a part number, that part number is searched for in table 1, the associated parents are kept in an array, and then the values within that array are searched in the query (where table 2 and table 3 are involved) and then what is supposed to happen is that the results of the search are supposed to be returned to the user in a temporary table called tmpProductQuery. The table pops up like it's supposed to but there are absolutely no values included in that table even though I know there are at least 200 values that are supposed to be there. Plllllllleaaaase help. This DB is making me go insane 

Code:
Option Compare Database
Option Explicit
Sub PartNumberSearch()
Dim txtPartNumber As Variant
Dim rst As Recordset 'declaration of variable
Dim Arr() As String 'declaration of variable
Dim i As Integer 'declaration of variable
Dim x As Variant 'declaration of variable
Dim varCode As Variant 'declaration of variable
Dim u As Variant
Dim dbs As Database
Dim qdf As QueryDef
Dim strSql As String
txtPartNumber = InputBox("Enter Part Number:")
If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] Like '*" & txtPartNumber & "*'") > 0 Then
MsgBox "Part Number Found"
Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
While rst.EOF = False 'go through while loop as long as end of record has not been reached
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr") 'Insert parent fields into array
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr
For Each varCode In x
'varCode = Replace(varCode, "-", "*")
u = varCode
MsgBox varCode
Set dbs = CurrentDb()
strSql = "Select * from CalculateTotal where ((Structure) Like '*" & u & "*');"
' Set rst = CurrentDb.OpenRecordset( _
' "Select * from CalculateTotal where ((Structure) Like '*" & u & "*')", dbOpenDynaset, dbSeeChanges) 'search associated fields with user input
Set rst = dbs.OpenRecordset(strSql, dbOpenSnapshot)
With dbs
Set qdf = .CreateQueryDef("tmpProductQuery", strSql)
DoCmd.OpenQuery "tmpProductQuery"
.QueryDefs.Delete "tmpProductQuery"
End With
dbs.Close
qdf.Close
Next varCode
Else
MsgBox "Part Number Does Not Exist"
End If
End Sub