From ListBox to Subform -- , how to add SQL statement?

[lorirobn]

Hi All,

I have a listbox on a main form. When user double-clicks an item in
the list box, I move the selected values to a subform at the bottom of
the form. List box is not multiselect, and subform is continuous form.
This is a work-in-progress, but seems to be working ok.

On my subform I have a sequence number field. When user selects from
the listbox, and the values are moved to the subform, I need to
calculate the sequence number for this new record based on values in
its table. (The table is one of the tables in the record source query
for the subform). I have set up an SQL string to do this, but not sure
how to use it. My question is: how can I use SQL to reference a table
within my List Box double-click event logic?

My List Box double-click event is:
Dim ctl As Control
Dim itm As Variant
Set ctl = Me![lstFurnListItems]
Me.fsubFurniture.SetFocus
DoCmd.GoToRecord , , acNewRec

For Each itm In ctl.ItemsSelected
If Not IsNull(itm) Then
Me!fsubFurniture!RoomID = txtRoomID.Value
Me!fsubFurniture!ItemID = ctl.Column(0, itm)
End If
Next

I want to have SQL something like:
Dim strSql As String
strSql = "SELECT tblRoomItems.SeqNo " _
& " FROM tblRoomItems " _
& " WHERE (((tblRoomItems.RoomID) = '" & txtRoomID.Value & "')"
_
& " AND ((tblRoomItems.ItemID) = " & ctl.Column(0, itm) & "));"

I tried doing something like the following, but it didn't work:
Dim recset As Recordset
Set recset = CurrentDb.OpenRecordset(strSql)

Me!fsubFurniture!SeqNo = tblRoomItems.SeqNo + 1

Appreciate any suggestions!
Thanks,
Lori

 

[pietlinden]

I'm baffled as to why anyone would want to you what you are proposing.
Why not just put a combobox in the subform? If you're going to use a
Multi-select listbox, that's one thing, because then you can select a
big chunk of records, click a button, and add them to a subform's
rowsource.

But to answer your question, you use the ItemsSelected collection of
the listbox. IF you look up "listbox" at www.mvps.org/access, there's
code that does what you want.

 

[pietlinden]

Nope, you can't grab the next sequence number like you can in Oracle...
(drat!)
You can do something like this...
Say you have a table

CREATE TABLE MyTable(
MyIDKey LONG PRIMARY KEY,
MyText TEXT(50),
....)

You can increment the MyIDKey field in the form's BeforeInsert event,
and set it to something like this:

Me.MyIDKey = DMax("[MyPK]", "MyTable")+1

Hope this helps.

 

[lorirobn]

Hi,
The DMAX you originally suggested works fine for my sequence number.

The reason I'm using a listbox is because I am querying a table for
inventory items and may potentially have many items listed. I want the
user to be able to view them, scroll through them, and then select the
item(s) he wants to 'assign' to a room. I just think a list box is
easier viewing for this sort of thing. Does that make sense?

I liked your suggestion to have it a multi-select list box. However,
when I changed it to multi-select, only the last item selectedfrom the
listbox gets added to my subform. I am starting to think what I want
cannot be done with a multi-select: user selects chunk of records,
they all get moved to subform but are incomplete (no INSERT yet), user
enters required information for each record on subform, and THEN record
gets added to table. Can a chunk of records be added to subform
without being saved one at a time? I can't save until user enters data,
because record would be incomplete.

Thanks for your help.