Continuous Form and subform - Linking with List Box help

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

Hi, I am trying to recreate the search for groups form here:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm . So far I have an
unbound form with a listbox on the left and a continuous subform on the
right. My two problems are: 1. How do I open the form so that the listbox
is filtered on a value from the previous form. 2. How do I bound my subform
on the right do the listbox?

Thanks in advance
 
I been so busy..and just not able to post a decent sample.

however, lets post some of the code I used

1. How do I open the form so that the listbox is filtered on a value from
the previous form.
Click to expand...

Well, in my case, I had a text box at the top, and user just typed in a few
characters and hit enter to fill the lisbox.

However, lets assume your case of wanting to load up the listbox when the
form loads with a passed filter. You don't mention what particular filter,
but lets just assume it is a particular "id", or some condition?. You can
either open the form up, and then set the list box.

Like:
dim strF as string
dim strSql as string

strF = "OurCoolSearchForm"
docmd.Open strF
strSql = "select * from tblForListbox where id = " & me!id
forms(strF)!TheListBoxOnTheLeftSide.RowSource = strSql

or, you could even use the forms filter

strSql = "select * from tblForListbox where " & me.filter
forms(strF)!TheListBoxOnTheLeftSide.RowSource = strSql

Another approach is to just pass the value needed, So,

dim strF as string
dim strSql as string


strF = "OurCoolSearchForm"
docmd.Open strF, , , , ,me!id

In the fomrs on-load event, you go

me.TheListBoxOnTheLeftSide.RowSource =
"select * from tblFrListBos where id = " & me.OpenArgs

(the above would be on one line)

2. How do I bound my subform
on the right do the listbox?
Click to expand...

Well, actually, could use two sub-forms, and not much code..but my exmaple
is a listbox
So, I use the after update event of the listbox (when you change it..the
after update event fires).

The code in hte after update event looks like

Dim subSql As String

If IsNull(Me.List51) = False Then
If Me.List51 <> "" Then
subSql = "SELECT GroupName, TripDate, Seats, Destination, Hotel"
subSql = subSql & " FROM OldGroupTrips WHERE [Gid] = " & Me.List51
subSql = subSql & " order by TripDate DESC"
Me.OldGroupTrips_subform.Form.RecordSource = subSql
Me.OldGroupTrips_subform.Visible = True
End If
End If

Now, I likely should have built a query and used hat above..but it don't
matter.

Note that you also need to fill the right side right after you load up the
list box. So, here the code that *really* should go in the on-load event

Me.List51.RowSource = "select * from tblForListBos where id = " &
me.OpenArgs
Me.List51.SetFocus

Me.List51.Selected(1) = True
Me.List51 = Me.List51.Column(0)

Call List51_AfterUpdate

That is nut shell as to how this works....
 
Thanks very much for posting - Extremely helpful!



Albert D.Kallal said:
I been so busy..and just not able to post a decent sample.

however, lets post some of the code I used

1. How do I open the form so that the listbox is filtered on a value from
the previous form.
Click to expand...

Well, in my case, I had a text box at the top, and user just typed in a
few characters and hit enter to fill the lisbox.

However, lets assume your case of wanting to load up the listbox when the
form loads with a passed filter. You don't mention what particular filter,
but lets just assume it is a particular "id", or some condition?. You can
either open the form up, and then set the list box.

Like:
dim strF as string
dim strSql as string

strF = "OurCoolSearchForm"
docmd.Open strF
strSql = "select * from tblForListbox where id = " & me!id
forms(strF)!TheListBoxOnTheLeftSide.RowSource = strSql

or, you could even use the forms filter

strSql = "select * from tblForListbox where " & me.filter
forms(strF)!TheListBoxOnTheLeftSide.RowSource = strSql

Another approach is to just pass the value needed, So,

dim strF as string
dim strSql as string


strF = "OurCoolSearchForm"
docmd.Open strF, , , , ,me!id

In the fomrs on-load event, you go

me.TheListBoxOnTheLeftSide.RowSource =
"select * from tblFrListBos where id = " & me.OpenArgs

(the above would be on one line)

2. How do I bound my subform
on the right do the listbox?
Click to expand...

Well, actually, could use two sub-forms, and not much code..but my exmaple
is a listbox
So, I use the after update event of the listbox (when you change it..the
after update event fires).

The code in hte after update event looks like

Dim subSql As String

If IsNull(Me.List51) = False Then
If Me.List51 <> "" Then
subSql = "SELECT GroupName, TripDate, Seats, Destination, Hotel"
subSql = subSql & " FROM OldGroupTrips WHERE [Gid] = " & Me.List51
subSql = subSql & " order by TripDate DESC"
Me.OldGroupTrips_subform.Form.RecordSource = subSql
Me.OldGroupTrips_subform.Visible = True
End If
End If

Now, I likely should have built a query and used hat above..but it don't
matter.

Note that you also need to fill the right side right after you load up the
list box. So, here the code that *really* should go in the on-load event

Me.List51.RowSource = "select * from tblForListBos where id = " &
me.OpenArgs
Me.List51.SetFocus

Me.List51.Selected(1) = True
Me.List51 = Me.List51.Column(0)

Call List51_AfterUpdate

That is nut shell as to how this works....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
Click to expand...
 
Back
Top