Filtering List Box Alphabetically (1 Viewer)

[shery1995]

Hi All

I m trying to filter the records "Alphabetically" based on Tab Selection/Change. However it does not work at all, can someone look at attached file and suggest a solution? Your help will be much appreciated.

 

[Gasman]

This is your code

Private Sub TabCtl0_Change()
    Me.RecordSource = "SELECT ClientID, FirstName_1, MiddleName_1, LastName_1, Mob FROM tblClient " _
         & " WHERE FirstName_1 LIKE '" & Chr(97 + Me!TabCtl0.Value) & "*' " _
         & " ORDER BY FirstName_1 ASC;"
End Sub

CHR(97) is an a but you have no ending bracket.?
Why not use the Caption of each page?, then it would be just one piece of code?

 

[Gasman]

I have had a quick peek further.

You are setting the form recordsource and not the listbox rowsource?
You could still use the caption of each page.?

I've not played too much with Tabs or pages, but managed this

Private Sub Form_Load()
    Me!TabCtl0.Pages(0).SetFocus
    Call Page1_Click
End Sub

Private Sub Page1_Click()
Dim strSQL As String

strSQL = "SELECT ClientID, FirstName_1, MiddleName_1, LastName_1, Mob FROM tblClient "
strSQL = strSQL & " WHERE FirstName_1 LIKE '" & Me.ActiveControl.Parent.Caption & "*'"
strSQL = strSQL & " ORDER BY FirstName_1 ASC;"
Me.lstClient.RowSource = strSQL
End Sub

Hopefully you can expand on that or an expert can chip in.?

 

[theDBguy]

Hi. I agree, you were filtering the form, instead of the listbox. Take a look now.

 

[shery1995]

Hi. I agree, you were filtering the form, instead of the listbox. Take a look now.

Thank you so much for your help.

 

[shery1995]

I have had a quick peek further.

You are setting the form recordsource and not the listbox rowsource?
You could still use the caption of each page.?

I've not played too much with Tabs or pages, but managed this

Private Sub Form_Load()
    Me!TabCtl0.Pages(0).SetFocus
    Call Page1_Click
End Sub

Private Sub Page1_Click()
Dim strSQL As String

strSQL = "SELECT ClientID, FirstName_1, MiddleName_1, LastName_1, Mob FROM tblClient "
strSQL = strSQL & " WHERE FirstName_1 LIKE '" & Me.ActiveControl.Parent.Caption & "*'"
strSQL = strSQL & " ORDER BY FirstName_1 ASC;"
Me.lstClient.RowSource = strSQL
End Sub

Hopefully you can expand on that or an expert can chip in.?

Many thanks for your help.

 

[Gasman]

Many thanks for your help.

I now see what you were going for with the CHR().
My apologies for leading you astray.

 

[theDBguy]

Thank you so much for your help.

Hi. You're welcome. @Gasman and I were happy to assist. Good luck with your project.

 

[shery1995]

Hi. You're welcome. @Gasman and I were happy to assist. Good luck with your project.

I am thankful to both of you and @Gasman for your help. Although I can use the list box form you have amended/corrected for me, however for the sake of my own knowledge and learning I made changes in my original form based on your recommendations and correction. Unfortunately, I am not getting the required result. I am not sure where I am doing wrong? I am attaching the file for your review and would be grateful to you If you can spot where I am doing wrong and let me know so that I can avoid this mistake in future. Many thanks for your precious time

 

[Gasman]

I''l leave it to theDBguy, it's his file.

 

[theDBguy]

I''l leave it to theDBguy, it's his file.

Appreciate that, but it's not necessary. I won't mind if you want to give it a try too.

 

[theDBguy]

I am thankful to both of you and @Gasman for your help. Although I can use the list box form you have amended/corrected for me, however for the sake of my own knowledge and learning I made changes in my original form based on your recommendations and correction. Unfortunately, I am not getting the required result. I am not sure where I am doing wrong? I am attaching the file for your review and would be grateful to you If you can spot where I am doing wrong and let me know so that I can avoid this mistake in future. Many thanks for your precious time

Hi. The problem is because you attached your listbox to the tab control. This was something I forgot to mention earlier. I had to detach your listbox from the tab control and simply attach it to the form itself.

 

[arnelgp]

you first put the listbox Outside the TabControl.
then Drag it inside the Tabcontrol to make an illusion
that it is being hosted by the tabcontrol.

 

[shery1995]

Hi. The problem is because you attached your listbox to the tab control. This was something I forgot to mention earlier. I had to detach your listbox from the tab control and simply attach it to the form itself.

Thank you so much. I did what you told and it is working fine now, once again thank you for your help I learned a lot with your help.

 

[shery1995]

you first put the listbox Outside the TabControl.
then Drag it inside the Tabcontrol to make an illusion
that it is being hosted by the tabcontrol.

I did exactly what you said and it is working fine now. Many thanks

 

[theDBguy]

Thank you so much. I did what you told and it is working fine now, once again thank you for your help I learned a lot with your help.

Congratulations!