Buttons and listbox to search in a form

[sebastico]

Hello

In a Form I have a list box to display NameID and Name fields. I also have
buttons for each letter form A to Z. The idea is If I click a button the
listbox displays all names starting with such letter.

Also I have a button to show all the names and another to clean the listbox
names.

When I open the Form it shows all names, so I don't know if the button All
works.

When I click the Clean button nothing happens.

When I click the A button it shows Compile Error: Sub or Function not
defined. Also code Left (Name, 1) = 'A'; " is red in colour.

This is my code

Option Compare Database
Option Explicit

'Here CmdA will display in Listbox all names starting with A
Private Sub CmdA_Click()
lsbNames.RowSource = "SELECT NameID, Name FROM TNames"
WHERE
Left(Nomb, 1) = 'A';"
End Sub

'Clean names in listbox
Private Sub CmdClean_Click()
Dim l As Long
For l = 0 To lsbNames.ListCount - 1
lsbNames.Selected(l) = False
Next
End Sub

'To show all Names
Private Sub CmdAll_Click()
lsbNames.RowSource = "SELECT NameID, Name FROM TNames;"
End Sub

I don't know What I, doing wrong

Could you tell me how to fix my code? I'm learning how to program in vba by
myself and with the forum, so I would really appreciate your help.

Many thanks

 

[Jeanette Cunningham]

1.
Private Sub CmdA_Click()
Dim strSQL As String
strSQL = "SELECT NameID, " _
& "Name FROM TNames " _
& "WHERE Name Like "A*" " _
& "ORDER BY TNames.Name"
Debug.Print strSQL
Me.lsbNames.RowSource= strSQL
End Sub


Note: if you really do have a field called Name in TNames, change it to
LastName or something similar.
Avoid using 'Name' because Access uses 'Name' internally for something else
and can get confused it you use 'Name' in your tables.

Use the debug.print line to copy and paste the query string from the
immediate window into a new query to check that the query is correctly
formed and returns the results you want.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

2.
'Clean names in listbox
Private Sub CmdClean_Click()
Dim l As Long
For l = 0 To lsbNames.ListCount - 1
lsbNames.Selected(l) = False
Next
End Sub

To clear the listbox, just tell the row source to be empty like this-->
Me.lsbNames = Null
Me.lsbNames.RowSource = ""


3.
Private Sub CmdAll_Click()
Dim strSQL As String

strSQL = "SELECT TNames.NameID, " _
& "TNames.Name FROM TNames " _
& "ORDER BY TNames.Name"
Debug.Print strSQL

Me.lsbNames.RowSource = strSQL
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[sebastico]

Hi Jeannete

As you suggested I changed the word Name and two codes work, steps 2 and 3
are Ok.

Step 1 when I click the A Button it displays Compile error: expected end of
statement
This lines are in red colour
strSQL = "SELECT AuID, " _
& "AutNomb FROM 2bTAut " _
& "WHERE AutNomb Like "A*" " _
& "ORDER BY 2bTAut.AutNomb"

and with"A*" " is pointed in blue.

Unfortunately I don't understand your lines to check what is wrong:
"Use the debug.print line to copy and paste the query string from the
immediate window into a new query to check that the query is correctly
formed and returns the results you want. " Could you help me?

Thank you very much indeed

 

[John W. Vinson]

Step 1 when I click the A Button it displays Compile error: expected end of
statement
This lines are in red colour
strSQL = "SELECT AuID, " _
& "AutNomb FROM 2bTAut " _
& "WHERE AutNomb Like "A*" " _
& "ORDER BY 2bTAut.AutNomb"

and with"A*" " is pointed in blue.

The quotemark before the A is being seen as the end of the quoted string. To
include a doublequote inside a string delimited by doublequotes, you must
double the doublequote (how's that for doubletalk!); or you can use a '
delimiter instead. Try

strSQL = "SELECT AuID, " _
& "AutNomb FROM 2bTAut " _
& "WHERE AutNomb Like 'A*' " _
& "ORDER BY 2bTAut.AutNomb"

or the following (harder to read because of all the quotes):

strSQL = "SELECT AuID, " _
& "AutNomb FROM 2bTAut " _
& "WHERE AutNomb Like ""A*"" " _
& "ORDER BY 2bTAut.AutNomb"

 

[sebastico]

John.

Thank you very much.
Now the my button works

 

[sebastico]

Thanks Jeannete
With your help and John all my buttons are working.