Prev: Change Field BackColours When A Tickbox Is True
Next: Calculating comboboxes
From: shmoussa on 29 Mar 2010 13:59
Hi,

I have a listbox. When I select an item in the listbox, I would like
subform1 to sort column1 with the selection from listbox on top.

For example, when I select Pennsylvania from the listbox- it will
update subform1 to, while still retaining all of the records, but all
records with "Pennsylvania" listed in column1 on top.

Is this possible? Thanks
From: Marshall Barton on 29 Mar 2010 15:32
shmoussa wrote:
>I have a listbox. When I select an item in the listbox, I would like
>subform1 to sort column1 with the selection from listbox on top.
>
>For example, when I select Pennsylvania from the listbox- it will
>update subform1 to, while still retaining all of the records, but all
>records with "Pennsylvania" listed in column1 on top.
>

That's not a sort problem. Presumably the records are
already sorted by state so all you need to do is navigate to
the first record with the selected state:

With Me.subform1.Form.Recordset
.FindFirst "statefield = '" & Me.thelistbox & "' "
If .Nomatch Then Beep
End With

--
Marsh
MVP [MS Access]
From: shmoussa on 29 Mar 2010 15:43
On Mar 29, 3:32 pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
> shmoussa wrote:
> >I have a listbox. When I select an item in the listbox, I would like
> >subform1 to sort column1 with the selection from listbox on top.
>
> >For example, when I select Pennsylvania from the listbox- it will
> >update subform1 to, while still retaining all of the records, but all
> >records with "Pennsylvania" listed in column1 on top.
>
> That's not a sort problem.  Presumably the records are
> already sorted by state so all you need to do is navigate to
> the first record with the selected state:
>
> With Me.subform1.Form.Recordset
>         .FindFirst "statefield = '" & Me.thelistbox & "' "
>         If .Nomatch Then Beep
> End With
>
> --
> Marsh
> MVP [MS Access]


Thanks for the reply. Can you tell me where to put this? Does it go in
the SQL for the query that generates the subform? Or in VBA for which
event?

Thanks again
From: Marshall Barton on 29 Mar 2010 16:41
shmoussa wrote:

>On Mar 29, 3:32�pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
>> shmoussa wrote:
>> >I have a listbox. When I select an item in the listbox, I would like
>> >subform1 to sort column1 with the selection from listbox on top.
>>
>> >For example, when I select Pennsylvania from the listbox- it will
>> >update subform1 to, while still retaining all of the records, but all
>> >records with "Pennsylvania" listed in column1 on top.
>>
>> That's not a sort problem. �Presumably the records are
>> already sorted by state so all you need to do is navigate to
>> the first record with the selected state:
>>
>> With Me.subform1.Form.Recordset
>> � � � � .FindFirst "statefield = '" & Me.thelistbox & "' "
>> � � � � If .Nomatch Then Beep
>> End With
>>
>
>Thanks for the reply. Can you tell me where to put this? Does it go in
>the SQL for the query that generates the subform? Or in VBA for which
>event?


VBA code never, ever goes in an SQL statement. VBA code can
ONLY go in a module, In your case I suggest that you put it
in a button's click event (the button is for users to say
the list box selection is made and they are ready to do the
search). If you want to do the search immediately when a
row is selected in the list box, then you can put the code
in the list box's AfterUpdate event procedure.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Change Field BackColours When A Tickbox Is True
Next: Calculating comboboxes