Unanswered: Set control on a form to display multi-column recordset output?
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.