Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002

    Unanswered: Set control on a form to display multi-column recordset output?

    Hi folks,

    Im not a pro programmer by any means and am mostly self taught. Currently Im developing a form for a multi-user environment. Basically, it collects a bunch of product information about a particular item and displays it all in various boxes (controls) on the form. It's working, but Im getting SQL server record locking (SQL server 7).

    My design was to build several queries that all use a drop down box value as their criteria (the drop down being the part number you select). Then I added several list box controls that use those queries as rowsources. When the part number in the drop down box changes, the controls all Requery. As I said, this works most of the time but record locking is happening.

    What I'd really like to do is open a recordset, populate the list box, and close the recordset to release the table locks. Is there a way to do that? Right now I have the following code which is not working (I forced a part number value for testing purposes). "jack" is the list box control I am trying to populate for the test process.:

    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Dim part As String
    part = "AT184447"

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySTOCK1SnapshotInventory")
    qdf.Parameters(0) = part
    Set rst = qdf.OpenRecordset
    Me!jack = rst

    I know how to do this on a single field control box (ie me!jack = rst!Column(1), but I cant seem to figure this out for multiple column rst's. In the case of this inventory rst, there are 4 columns I want to display in the list box: warehouse, on hand, on sales order, available balance.

    Any help would be *greatly* appreciated.

    My IT network admin mentioned stored procedures but those are beyond my ability. Im hoping to figure this out with VBA code. Our SQL programmer left the company a few weeks ago.

  2. #2
    Join Date
    Oct 2002
    NSW Australia
    you can refer to the fields in your recordset by their location left to right eg.

    a = rs.Fields(2)
    b = rs.Fields(0)
    c = rs.Fields(4)
    d = rs.Fields(3)
    e = rs.Fields(1)

  3. #3
    Join Date
    Aug 2002
    Thanks I'll try that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts