ListBox Column as Subform Recordsource

[jcw5107]

I have a form with a Listbox & subform on it.
When scrolling thru the records on the main form, the listbox requeries to show right list with the main forms current record. On any given record the list box can have from 1 to 4 items shown.
I need to have the subform requery based on a single column in the listbox. Can this be done without the user having to select the items in the listbox? I want the subform to show ALL related records from the listbox, wether its 1 item or 4...

Any suggestions or examples...???
Thanks in advance...!!!
jcw5107

 

[Crowley16]

don't think this can be done easily since you can't access the column of the listbox dynamically, i.e. can't use it in a select statement.

so what you'd have to do is to have some code to actually build up the recordsource of subform.

probably the easiest way would be to have a function to build some dynamic sql to put into the recordsource of your subform.

the function would look at the contents of the listbox, and probably build up a where statement which can be appended to the recordsource of your subform, and you can put this function in the same event where your listbox gets refreshed.

--------------------
Procrastinate Now!

 

[TheAceMan1]

How are ya jcw5107 . . .

If you don't make a selection in the listbox how are you to know what to do! . . . The answer is [blue]No![/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[jcw5107]

Hey now...!!

Alrighty then... I guess I have to go to plan "L"...!!!
I think I found a work-around to simulate what it is I want to do....
On the main form - on the OnCurrent Event, a bit of code runs to select all items in the listbox, and then the subform requeries based on the selection... Gets me what I want, but not sure if this is how I want the d/base to work.

Thanks for all of your help..!! Both of ya..!!!
jcw5107

 

[TheEntertainer]

I'm doing something similiar but I am making selection, but it doesn't seem to register the selection. This is the code is below the if statement (If Me.List39.Selected(intCounter) = True) never seems to be true and therefore the strCriteria is not constructed correctly.

For intCounter = 1 To Me.List39.ListCount Step 1  
If Me.List39.Selected(intCounter) = True Then
strCriteria = "[seizure_id] = " & Me.List39.Column(3, intCounter)
strCriteria = strCriteria & " and [egg_id] = " & Me.List39.Column(2, intCounter)
MsgBox strCriteria & " Within Loop"
End If
Next intCounter

MsgBox Me.List39.Selected(1) & " Selected Item"
MsgBox strCritieria & " Outside Loop"

If strCriteria <> vbNullString Then
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
End If

strSQL = "SELECT * FROM seizure" & _
IIf(strCriteria = vbNullString, ";", " WHERE " & strCriteria & ";")

MsgBox strSQL

It a multiple column listbox with values like this:

id egg_date seizure_id
1 23/10/2007 2

I'm trying to pick up the id and the seizure_id field and use as the record source of some other textboxs.

Any help would be appreciated.

Cheers

 

[TheEntertainer]

Think I've sorted it.

Just needed to change the multiselect property of the listbox to simple or extended.

Cheers