Search List box base on text box text.... (1 Viewer)

[rio]

Hi. I need some help to solve my problem. Error with this code

Private Sub txtsearchbox_Change()
Me.[stafflist].RowSource = "SELECT [txttblname].ID, [txttblname].Name, [txttblname].Address, [txttblname].Age" & _
"FROM [txttblname]" & _
"WHERE ((([txttblname].ID) Like " * " & [Forms]![SearchStaff]![txtsearchbox] & " * "));"
End Sub


* I also attach the db..

 

[arnelgp]

Private Sub txtsearchbox_Change()
Me.[stafflist].RowSource = "SELECT [txttblname].ID, [txttblname].Name, [txttblname].Address, [txttblname].Age" & _
"FROM [txttblname]" & _
"WHERE ((([txttblname].ID) Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*'"));"
End Sub

 

[rio]

arnelgp...

Still not working...

Me.[stafflist].RowSource = "SELECT [Staff - 2016].ID, [Staff - 2016].Name, [Staff - 2016].Address, [Staff - 2016].Age" & _
"FROM [Staff - 2016]" & _
"WHERE ((([Staff - 2016].ID) Like '* " & [Forms]![SearchStaff]![txtsearchbox] & " * '"));"

 

[arnelgp]

if your ID field is numeric:

Me.[stafflist].RowSource = "SELECT [Staff - 2016].ID, [Staff - 2016].Name, [Staff - 2016].Address, [Staff - 2016].Age " & _
"FROM [Staff - 2016] " & _
"WHERE [Staff - 2016].ID = " & Val([Forms]![SearchStaff]![txtsearchbox] & "") & ";"

if it is string/text:

Me.[stafflist].RowSource = "SELECT [Staff - 2016].ID, [Staff - 2016].Name, [Staff - 2016].Address, [Staff - 2016].Age " & _
"FROM [Staff - 2016] " & _
"WHERE [Staff - 2016].ID Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*';"

 

[rio]

arnelgp...
great... thanks for you help.. I used

Me.[stafflist].RowSource = "SELECT [Staff - 2016].ID, [Staff - 2016].Name, [Staff - 2016].Address, [Staff - 2016].Age " & _
"FROM [Staff - 2016] " & _
"WHERE [Staff - 2016].ID = " & Val([Forms]![SearchStaff]![txtsearchbox] & "") & ";"


and.. one more question... can i replace [Staff - 2016] with another text box value. for example is i want to replace [Staff - 2016] with [Forms]![SearchStaff]![txttblname].

is it the code same?

 

[arnelgp]

yes ofcourse.
if your staff tables are uniformly named, ie staff-2016, staff-2015, staff-2017, then all you need is a combobox or a listbox:

private sub form_load()
    dim db as dao.database
    dim td as dao.tabledef

    set db=currentdb
    for each td in db.tabledefs
        if td.name Like "Staff*' then
            'yourcombobox/listbox of staff table names
            me.lstStaffTables.Add td.name
        end if
    next td
    set td=nothing
    set db=nothing
end sub

then all you have to do is replace your with:

Me.[stafflist].RowSource = "SELECT ID, Name, Address, Age " & _
"FROM " & me.lstStaffTables & " " & _
"WHERE ID = " & Val([Forms]![SearchStaff]![txtsearchbox] & "") & ";"

 

[rio]

ok. i already have the text box name [txttblname] with this code

="Staff - " & Year(Now()). and in other form i already create new table also using this code.

so now i got the new table name with that code.

i used

Me.[stafflist].RowSource = "SELECT ID, Name, Address, Age " & _
"FROM " & me.txttblname & " " & _
"WHERE Name Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*';"

i change ID to Name.

But nothing happen...

 

[arnelgp]

since you have a space on your table and, you need to enclosed the table name in brackets:

Me.[stafflist].RowSource = "SELECT ID, Name, Address, Age " & _
"FROM [" & me.txttblname & "] " & _
"WHERE Name Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*';"

 

[rio]

yes.... it working... really great... thanks for your help arnelgp .

 

[arnelgp]

goodluck with your project!

 

[rio]

Sorry arnelgp... try to upgrade...

can I used OR to filter base on Name and Address field?

 

[rio]

it's ok... I already find the way. I used this code

"WHERE ((Name Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*')) OR ((Address Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*'));"

 

[arnelgp]

by all means you can:

Dim strWhere As String
strWhere = Trim([Forms]![SearchStaff]![txtsearchbox] & "")

If strWhere <> "" Then
strWhere = " WHERE Name Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*'" & _
" Or Address Like '*" & [Forms]![SearchStaff]![txtsearchbox] & "*';"
End If

Me.[stafflist].RowSource = "SELECT ID, Name, Address, Age " & _
"FROM [" & me.txttblname & "]" & strWhere

 

[arnelgp]

nice, you got it!