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.

[Lorirobn]

Thank you - you are absolutely right - I do need to have my listbox be
Multi-Select! However, I want the selected records from the list box
moved to the subform first, and for each line, the user needs to add
additional data. Is this ok?

I had a look at the mvps.org web site (thanks for that), and couldn't
find the answer to my original question. (did a search on listboxes and
looked through the results). To reiterate, for each line of data moved
from the listbox to the subform, I need to get the max sequence number
for a given field combination on the table. I was not sure how to do
the SQL, or where to put it within my form's VBA. I could also get the
max seq no from the subform, if I knew how to look through all the rows
on there...

Thank you,
Lori

*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

I'm still somewhat baffled as to why you'd use a listbox here, but it's
your application, not mine.

Here's the code for looping through selected items in a listbox:
http://www.mvps.org/access/forms/frm0007.htm

Getting the next sequence number, use DMAX() then use DMAX()+1 to get
your next one.

If you have a listbox, it has an ItemsSelected property

[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.