Grabbing Table item from Form

[darkstar257]

Each page on the main form stores information on an order in the sub-form table. So that the main form is tied to the table Item_Line by a Relationship. However, when I try to pull values from VB code, it doesn't give me all the information from this relationship. What's going on?

I created a button in the main form to grab values from the table, but it only pulls up values from the mouse cursor selected line of the table when the button is clicked. This is simply using the Forms! call out:

Forms![Item_Line_sub]![ID]

So then I tried to pull it up by using a Recordset and it gives me the first table item only:

Dim db As Database
Dim Lrs As DAO.Recordset
Dim LGST As String

Set db = CurrentDb()
Set Lrs = db.OpenRecordset("Item_Line")

If Lrs.EOF = False Then
        LGST = Lrs("Description")
    Else
        LGST = "Not found"
    End If

    Lrs.Close
    Set Lrs = Nothing

    MsgBox (LGST)

End Sub

And when i use a Do While loop it gets stuck in an infinite loop outputting an random item value from somewhere else on the table.

Dim db As Database
Dim Lrs As DAO.Recordset
Dim LGST As String

Set db = CurrentDb()
Set Lrs = db.OpenRecordset("Item_Line")
    Do while Not rst.EOF
        MsgBox (ID)
        End If
        rst.MoveNext
    Loop....
...

 

[pbaldy]

Your recordset is Lrs, not rst.

 

[darkstar257]

Thanks for pointing that out. However, now that I finally get it to be able to grab values from the table in the sub form, i'm trying to match the Form number to the Subform Table ID number... and the FindFirst function is giving me this error:
"Operation is not supported for this type of object"

Set db = CurrentDb()
Set rst = db.OpenRecordset("Item_Line")

strCriteria = "FormID = " & rst![ID]
num = 0

Do While Not rst.EOF
[B]    rst.FindFirst strCriteria[/B]
    MsgBox ("Match Found at ID: " & rst![ID] & " Description: " & rst![Description])
    If Not rst.NoMatch Then
    MsgBox ("No Match Found")
    num = num + 1
    End If
    Loop
MsgBox ("Loop Count: " & num)

Can I just use an If statement to match the ID numbers? instead of using FindFirst like this?

    If FormID = rst![ID] Then
    MsgBox ("Match Found at ID: " & rst![ID] & " Description: " & rst![Description])
    Exit Do
    Else
    rst.MoveNext
    num = num + 1
    MsgBox ("Current Table ID: " & rst![ID])
    End If

 

[pbaldy]

I think your problem is that you haven't specified a recordset type, and the default is a table type. I don't think FindFirst works with a table type recordset. You'd want to specify dbOpenDynaset.

I can't remember the last time I used FindFirst. There are typically much more efficient ways to accomplish the goal. If all you're after is a count, you can use DCount or open a recordset on an SQL statement:

"SELECT Count(*) AS HowMany FROM TableName WHERE FormID = 123"

 

[darkstar257]

The count just lets me know that the loop is actually "working" going through checking all the entries.

What i'm after is being able to grab all the data from a specific line item entry in the subform table for that specific main form order entry. And i found out that the line items ID of the table is the same as the ID of each main form. So if I match ID of both main form and sub form table then I should have a winner.

I read about dbOpenDynaset types today too, but I didn't know what is the difference between the types.

So I tried:

Set db = CurrentDb()
Set rst = db.OpenRecordset("Item_Line", dbOpenDynaset)
MsgBox ("Current Line Item ID: " & rst![ID] & " Description: " & rst![Description])
[B]rstID = rst![ID][/B]

this actually gave me a "Invalid Use of Null" error because rst![ID] is now an null value for some reason.

I'm trying to figure out how to use Seek method instead of FindFirst because that's what Table type recordsets suppose to use. This is what I have so far with the Seek method, but once both Form and table ID are matched, it just freezes with the repeating Match Found message box. It doesn't continue to search for repeating ID matches as I know there are repeating IDs.

Set db = CurrentDb()
Set rst = db.OpenRecordset("Item_Line", dbOpenTable) 'dbOpenTable

'Seek Method
With rst
.Index = "ID"

Do While Not rst.EOF
If FormID = "" Then Exit Do
.Seek "=", Val(FormID)
MsgBox ("Match Found at: " & rst![ID] & "   Description: " & rst![Description] & "Loop: " & num)
.MoveNext
If .NoMatch Then
MsgBox ("Match NOT Found")
End If
Loop
num = num + 1
MsgBox ("Loop Count: " & num)
.Close
End With

 

[pbaldy]

What i'm after is being able to grab all the data from a specific line item entry in the subform table for that specific main form order entry.

And do what with them? You're still doing it the inefficient way. Open your recordset on an SQL statement that limits it to the necessary items. You're opening it on the whole table and then sifting through it. Though there may seem to be no difference, your way requires the whole table be brought over the network. The other way will only bring the required records. On a large table, you'll see a big performance difference.

 

[darkstar257]

And do what with them? You're still doing it the inefficient way. Open your recordset on an SQL statement that limits it to the necessary items. You're opening it on the whole table and then sifting through it. Though there may seem to be no difference, your way requires the whole table be brought over the network. The other way will only bring the required records. On a large table, you'll see a big performance difference.

You're absolutely right, it's extremely inefficient since I have to go through the entire table. Luckily my table is only around 1200 entries so the search and matching is instantaneous. I actually am very unfamiliar with Visual Basic and Access so i don't know how to open a recordset on an SQL statement or what an SQL statement even is

however this is the only way I know how to do it as i'm no programmer

I perfected the search algorithm and combined it to work with my automated email using CDO. My CDO email code puts all the table values I successfully found from the recordset into a string array and sends it through SMTP.

As you can see below i ended up using a simple If equals statement rather than SEEK or FIND FIRST.

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Item_Line", dbOpenTable) 'dbOpenTable
    blDimensioned = False
    FormID = Forms![Packing Slip]!ID
    
    Do While Not rst.EOF
        If FormID = rst![ID] And Len(rst![Description]) <> 0 Then
            If blDimensioned = True Then 'Has array been dimensioned?
                ReDim Preserve strOrderList(0 To UBound(strOrderList) + 1) As String
            Else
                ReDim strOrderList(0 To 0) As String
                blDimensioned = True
            End If
        'Add item to list
        strOrderList(UBound(strOrderList)) = "Qty Shipped: " & rst![Qty Shipped] & " " & rst![Unit of Measure] & "   " & rst![Description] & vbCr & _
        "Ship Date: " & rst![ShipDate] & "   " & "Tracking No.: " & rst![TrackingNo] & vbCr
        rst.MoveNext
        Else
        rst.MoveNext
        End If
        num = num + 1
    Loop
   
    'Displays individual items in string array
    For arrayPosition = LBound(strOrderList) To UBound(strOrderList)
        strBuf = strBuf & strOrderList(arrayPosition) & vbCr

    Next arrayPosition
    'MsgBox (strBuf)
    db.Close

The Output:

 Initial Shipment Information:
  Qty Shipped: 50 CASE   Candy Canes
  Ship Date: 3/3/2011   Tracking No.: abcd654564654
   
  Qty Shipped: 4 CASE   Lolipops
  Ship Date: 3/3/2011   Tracking No.: 5454d324sd
   
  Qty Shipped: 2 ROLLS   Ribbons
  Ship Date: 3/3/2011   Tracking No.: sd654s654654s35

 

[pbaldy]

At the end of the day, you have to do what you feel is best. Glad you have a working solution.