Returning values into a query or a table

[ECEstudent]

Hi, I'm not sure if this is possible but what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered.
I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.

1) Part Number is input by user
2) Search that Part Number into Table1
3) Take returned Parent Values associated with Part Number and store in Array
4) Modify Array values
5) Search Array values using a For Each loop into the query

------------------So far this is where I am at. What I need after this point and I am stuck on is:

6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query.
7) Also return the total rows in a specific column (Order Numbers)


Does anyone have any idea how to do that? I would appreciate any help!

 

[ECEstudent]

My code looks like this so far:

txtPartNumber = InputBox("Enter Part Number:")

If Not IsNull(txtPartNumber) Then
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

'delete after testing
MsgBox varCode

Dim Count As Integer

Count = DCount("Structure", "CalculateTotal", "[Structure] Like '*" & u & "*'") 'If what the user inputs exists in database, enter if statement
' MsgBox Count

strSQL = "SELECT COUNT(OrderNumber) AS TotalOrders FROM CalculateTotal"

Set rst = CurrentDb.OpenRecordset( _
"Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*')AND (((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#)) ", dbOpenDynaset, dbSeeChanges) 'search associated fields with user input

Dim varC As Variant
Dim E As Variant

i = 0
Dim Arrf() As String 'declaration of variable
While rst.EOF = False 'go through while loop as long as end of record has not been reached
ReDim Preserve Arrf(i)
Arrf(i) = rst.Fields("OrderNumber") 'Insert Item Numbers into array
i = i + 1
rst.MoveNext
Wend 'end of while loop
E = Arrf
Dim varCod As Variant
Dim t As Integer

t = 0 'set variable i equal to zero
While rst.EOF = False 'go through while loop as long as end of record has not been reached
ReDim Preserve Arry(i)
Arry(i) = rst.Fields("Item") 'Insert serial card IDs into array
t = t + 1
rst.MoveNext
Wend 'end of while loop
p = Arry

'intResult = DCount("OrderNumber", "CalculateTotal", "")

rst.Close
'db.Close
Next varCode



Else

MsgBox "Part Number Not Found"
End If
Else
MsgBox "Please Enter Part Number"

End If
totalOrderNumber = i + 1
totalItemNumber = t + 1
MsgBox totalOrderNumber
MsgBox totalItemNumber
End Sub

 

[rzw0wr]

My suggestion would be to fix the table problem.

I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.

Dale

 

[ECEstudent]

There is no table problem for me to fix.

 

[rzw0wr]

IF you have 3 tables and you have 1 that can not be related to the others, You have a table structure problem.

I only suggested that you find that problem and fix it. Then you will not need all the code to try to cover it up.

Dale

 

[ECEstudent]

No really. It's not causing a problem. Let's ignore table 1. I am now looking for a value in the query and I want the query to return all of the columns for that value as well as calculate the rows for 2 of the columns in that query. Does that make more sense? Thank you for your responses.

 

[rzw0wr]

Do you have Option Explicit at the top of you code window?

If not put it the and click Debug/Compile.
I don't see this code compiling.

It should give you the errors.

Dale

 

[ECEstudent]

yupp I do. It compiles just fine over on my DB but it keeps saying no associated values exist in the table when I know there are at least 20...

 

[rzw0wr]

Please post all of the code then.
Note: Go to advanced and use the Code Wrap.
This makes code easier to read.


Dale

 

[ECEstudent]

Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim Arr() As String 'declaration of variable
Dim i As Integer 'declaration of variable
Dim x As Variant 'declaration of variable
Dim rst As Recordset 'declaration of variable
Dim u As Variant
Dim varCode As Variant 'declaration of variable
Dim Arry() As String 'declaration of variable
Dim p As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
Set db = CurrentDb
Dim totalOrderNumber As Integer
Dim totalItemNumber As Integer
txtPartNumber = InputBox("Enter Part Number:")
If Not IsNull(txtPartNumber) Then
    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
            'delete after testing
            'MsgBox varCode
            Dim Count As Integer
            Count = DCount("Structure", "CalculateTotal", "[Structure] Like '*" & u & "*'")  'If what the user inputs exists in database, enter if statement
           ' MsgBox Count
            strSQL = "SELECT COUNT(OrderNumber) AS TotalOrders FROM CalculateTotal"
            Set rst = CurrentDb.OpenRecordset( _
            "Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*')AND (((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#)) ", dbOpenDynaset, dbSeeChanges) 'search associated fields with user input
            Dim varC As Variant
            Dim E As Variant
            i = 0
            Dim Arrf() As String 'declaration of variable
            While rst.EOF = False 'go through while loop as long as end of record has not been reached
                ReDim Preserve Arrf(i)
                Arrf(i) = rst.Fields("OrderNumber") 'Insert Item Numbers into array
                i = i + 1
                rst.MoveNext
            Wend 'end of while loop
            E = Arrf
            Dim varCod As Variant
            Dim t As Integer
            t = 0 'set variable i equal to zero
            While rst.EOF = False 'go through while loop as long as end of record has not been reached
                ReDim Preserve Arry(i)
                Arry(i) = rst.Fields("Item") 'Insert serial card IDs into array
                t = t + 1
                rst.MoveNext
            Wend 'end of while loop
            p = Arry
'intResult = DCount("OrderNumber", "CalculateTotal", "")
            rst.Close
'db.Close
        Next varCode
    Else
        MsgBox "Part Number Not Found"
    End If
Else
MsgBox "Please Enter Part Number"
End If
totalOrderNumber = i + 1
totalItemNumber = t + 1
MsgBox totalOrderNumber
MsgBox totalItemNumber
End Sub

 

[ECEstudent]

Oh. Please ignore the comments. Some of them are incorrect due to how often I kept changing my code...