"like" in function in query criteria (1 Viewer)

John Sh

Member
Local time
Today, 16:33
Joined
Feb 8, 2021
Messages
408
I have a select query that returns a list of names to a combobox. I need to limit the list to the first letter entered.
Within the query it would be "like 'F' & '*'". without the outer quotes. This works but is obviously not very practical.
I have a function in the criteria to do this but when I enter >myFuntion access inserts the table and field as
![field], before the function call resulting in a type mismatch. How can I dynamically change the criteria?
For some reason the "table" inside [] will not appear in the text!!
My function code below.
Code:
Function getKey()
getKey = "'like' & sKey & '*'"
End Function

sKey is a public string variable.
Should the function, which is in a module, be Public?

The combobox code below

Code:
Private Sub CboGetName_Keypress(KeyAscii As Integer)
sKey = UCase(Chr(KeyAscii))
Call getKey()
Me.CboGetName.Dropdown
End Sub

Is the "call" necessary with no parameters passed and does it make any difference?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:33
Joined
May 7, 2009
Messages
19,169
getKey() is a function, so you assign the return value from the function to
a variable:

dim m as string
m = getKey()


also you missed a quote on getkey:

Function getKey()
getKey = "like '" & sKey & "*'"
End Function
 

John Sh

Member
Local time
Today, 16:33
Joined
Feb 8, 2021
Messages
408
getKey() is a function, so you assign the return value from the function to
a variable:
dim m as string
m = getKey()


also you missed a quote on getkey:

Function getKey()
getKey = "like '" & sKey & "*'"
End Function
Thanks for spotting the missed quote but this doesn't answer my question. It did, however, fix the type mismatch error.
I have tried various combinations of the function's return value and ways of entering the criteria in the query.,
At best I get a dropdown list with all the names listed. At worst I get a dropdown with nothing in it.
My aim is to get a dropdown with only those names starting with the first character entered in the combobox.
 

MarkK

bit cruncher
Local time
Yesterday, 22:33
Joined
Mar 17, 2004
Messages
8,178
What you can do is write a query that returns the rows you want, like...
Code:
Const SQL = "SELECT * FROM Table WHERE FirstName LIKE p0"
Now handle the change event of the combo, and using the Text property, open a recordset using your query, and assign it the list displayed by the combo, so...
Code:
Private Sub cbo_Change()
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = Me.cob.Text & "*"
        Set me.cbo.Recordset = .OpenRecordset
    End With
    Me.cbo.Dropdown
End Sub
 

John Sh

Member
Local time
Today, 16:33
Joined
Feb 8, 2021
Messages
408
What you can do is write a query that returns the rows you want, like...
Code:
Const SQL = "SELECT * FROM Table WHERE FirstName LIKE p0"
Now handle the change event of the combo, and using the Text property, open a recordset using your query, and assign it the list displayed by the combo, so...
Code:
Private Sub cbo_Change()
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = Me.cob.Text & "*"
        Set me.cbo.Recordset = .OpenRecordset
    End With
    Me.cbo.Dropdown
End Sub
Thank you Mark. That looks like a step in the right direction although not quite.
My table is a list of deceased servicemen/women who served at a local RAAF Catalina flying boat base during WWII.
Instead of returning a list of names, the query is returning the service number of the first name.
The relevant fields are "Number" and "Name"

My code is below. Please advise if I have done something wrong.
This is in the declaration section of the form.
Code:
Const SQL = "SELECT * FROM plaques WHERE Name LIKE p0"
and I tried this with the same result
Code:
Const SQL = "SELECT * FROM [plaques] WHERE [Name] LIKE p0"


Code:
Private Sub cbogetname_Change()
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = Me.CboGetName.Text & "*"
        Set Me.CboGetName.Recordset = .OpenRecordset
    End With
    Me.CboGetName.Dropdown
End Sub
 

MarkK

bit cruncher
Local time
Yesterday, 22:33
Joined
Mar 17, 2004
Messages
8,178
Using '*' as the field list in an SQL statement returns all the fields in the table, but I don't know what fields are in your table. I also don't know what fields you want to show in your combo. Be aware that the ColumnCount and ColumnWidths property of the ComboBox control will also limit and/or determine what fields exist and/or appear there.
Apart from that, I don't know what you are trying to do. I you elaborate on your purpose, and the details of your actual data, I may be able to be more helpful.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:33
Joined
May 7, 2009
Messages
19,169
you can use "combo search".
 

Attachments

  • CountriesDb(combobox filter-as-you-type).accdb
    960 KB · Views: 443

John Sh

Member
Local time
Today, 16:33
Joined
Feb 8, 2021
Messages
408
Using '*' as the field list in an SQL statement returns all the fields in the table, but I don't know what fields are in your table. I also don't know what fields you want to show in your combo. Be aware that the ColumnCount and ColumnWidths property of the ComboBox control will also limit and/or determine what fields exist and/or appear there.
Apart from that, I don't know what you are trying to do. I you elaborate on your purpose, and the details of your actual data, I may be able to be more helpful.
Replacing the "*" with the field name has corrected that problem. What I now get is:
Screenshot_27.jpg

So it is responding to the full cbo text rather than the first character entered.
If I then delete the blacked out part I get this, which is what I'm after. Is there a way to delete that part programatically?
Screenshot_28.jpg

I have changed the cbo event from changed() to keypress() and use ucase(chr(keyCode)) instead of me.cbo.text and now get a definition error.
Code:
Private Sub cbogetname_Keypress(KeyCode As Integer, Shift As Integer)
    Dim str As String
    str = UCase(Chr(KeyCode))
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = str & "*"
        Set Me.CboGetName.Recordset = .OpenRecordset
    End With
    Me.CboGetName.Dropdown
End Sub

Hopefully this gives you the info you need.
 

John Sh

Member
Local time
Today, 16:33
Joined
Feb 8, 2021
Messages
408
I fixed it.
I changed the cbo change() event to:
Code:
Private Sub cbogetname_change()
    Dim str As String
    keycount = keycount + 1
    str = Left(Me.CboGetName.Text, keycount)
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = str & "*"
        Set Me.CboGetName.Recordset = .OpenRecordset
    End With
    Me.CboGetName.Dropdown
End Sub

The public integer keyCount is set to 0 on cbo_gotfocus and cbo_lostfocus
I thank you all for your input.
 

Users who are viewing this thread

Top Bottom