Searching on combobox values

[sara1027]

Hi I have created a split form from a query which has a combobox which has 3 colomns ID, UserFirstName and LastName. The form displays UserFirstName in a combobox and LastName in a bound textbox. My problem is that I have created a Search button and textbox where the user can enter the UserName and look up all the records associated with that user. If I put in the UserFirstName it brings up no records but if I enter the ID number it works, however I would like to be able to use the UserFirstName to look up the records - how can I do this. My Macro is [UserFirstName] Like "*" & [Forms]![Assets Query]![searchTxt] & "*". I have tried [UserFirstName].column(1) Like "*" & [Forms]![Assets Query]![searchTxt] & "*" but it doesn't work any help please would be gratefully appreciated. Thanks

 

[Minty]

Have you used a lookup field in your table design ? That can cause an issue with the real stored data being hidden.

http://access.mvps.org/access/lookupfields.htm

 

[sara1027]

Yes I am using a lookup up for the name of the user associated with the asset. The users are in their own table form which I get the names.

 

[June7]

Why would WHERE clause compare combobox with textbox? That makes no sense.

Why filter on first name? What if there are multiple employees with same first name?

Review http://access.mvps.org/Access/lookupfields.htm

 

[arnelgp]

is VBA possible as a solution.
create a button that will do the actual search (mine is Command7).
on its click event add the code:

Private Sub Command7_Click()
    '* NOTE:
    '*
    '* On DLookup() change "id" to the pk of the table (table1)
    '*      change "table1" to the name of the Lookup table
    '*      change "UserFirstName" (if not same) to the name on table Lookup
    '*
    Dim id As Long
    id = Nz(DLookup("id", "table1", "UserFirstName Like '" & Me.SearchTxt & "*'"), 0)
    DoCmd.SearchForRecord acDataForm, "Assets Query", acFirst, "UserFirstName=" & id
End Sub

 

[sara1027]

"Why would WHERE clause compare combobox with textbox? That makes no sense." Not sure why you have put this in not helpful really - as I said I was asking if it is possible to get the value of the second column in a combobox to use as a search value.
"Why filter on first name? What if there are multiple employees with same first name? " because I would like to see how many assets are associated with a use. The number of users is small so multiple name not that much of a problem

 

[arnelgp]

Are you refering to my post. The actual value of combo is numeric.

 

[Uncle Gizmo]

The number of users is small so multiple name not that much of a problem

Well, that's like saying it's ok to drive on the wrong side of the road at 3am because there is no traffic at that time of night.

Why not just do it properly?


Sent from my SM-G925F using Tapatalk

 

[arnelgp]

i you like you can cgange the recordsource of your form.

Private Sub Command7_Click()
    if trim(me.searchTxt & "") = "" then
        me.recordsource="select * from yourQuery/Table"
     else
        me.recordsource= "select * from yourQuery/table where UserFirstName in (select id from table1 where UserFirstName Like '*" & Me.SearchTxt & "*')"
    end if
End Sub

 

[sara1027]

My reply was to June7 but I haven't tried your solution yet I'm not to sure of VBA yet but I will try it. Thanks for the update

 

[sara1027]

Well, that's like saying it's ok to drive on the wrong side of the road at 3am because there is no traffic at that time of night.

Why not just do it properly?


Sent from my SM-G925F using Tapatalk

Not sure what you mean do it properly - if you mean not use the name then what do you suggest? It would be nice if you were a little bit more clearer in your message. I thought you were here to help!

 

[Gasman]

Not sure what you mean do it properly - if you mean not use the name then what do you suggest? It would be nice if you were a little bit more clearer in your message. I thought you were here to help!

You can build the combo with first and surname in the source.
So you would see

Sara Jones
Sara Smith
Sara Williams


and easily identify the correct person.?


All you need is for a new employee to join with the same first name as an existing employee, and all your hard work is for nothing.?


HTH

 

[sara1027]

This is what I have but when the search filter [UserFirstName] Like "*"&[Forms]![AssetsQuery]![searchTxt]&"*" is used and I put in Sara it comes up with no recoreds but if I enter the ID number it brings the record up. My lookup is taking data from a table Users which has a FirstName and LastName fields and shows the FistName and LastName in two columns of my combobox. I understand that the combobox is actually 3 columns which has the ID, FirstName and LastName so my question is how to search on the FirstName using the filter above. Thanks

 

[Gasman]

When I have done this, I have used a textbox for entering the string?
You were asked whether you were using a lookup earlier, but I am not sure if you understood the question?
Access can 'helpfully' supply a lookup in tables. So when you look at the table you see Sara, but what it actually stores is teh ID of Sara from the lookup table?


Most people I believe emulate the lookup, but then it is evident that the ID is stored in one table and the related data in another. I fell into this trap on my first Access data base, and have never got around to changing it yet, but because I am now aware, I have not used that feature since.


Is this your scenario at all?

 

[isladogs]

See attached for an example that might help
After selecting a last name using the combo, all contacts with that name are displayed in a listbox.
NOTE: there are deliberately two Susan Jones in this example

Select a record from the listbox to open the contact details

I have other examples where I have two combo boxes: last name & first name so users can use either to select a person

 

[sara1027]

See attached for an example that might help
After selecting a last name using the combo, all contacts with that name are displayed in a listbox.
NOTE: there are deliberately two Susan Jones in this example

I am using two tables Assets and Users (The users hold information on the user like a contact) The Asset table holds the data for all the assets including the name of the user taken from the user table. Only the First Name and Last Name are displayed. I am using a search text box on my Asset form which allows searching on a an asset e.g. all Dell computers or one particular asset but I would also like to be able to search on the user (Frist or Last Name) to see which assets are associated with that user. My Asset table uses a look up to find the users name and on the form it is a combobox I am using another split form to search for records

 

[sara1027]

When I have done this, I have used a textbox for entering the string?
You were asked whether you were using a lookup earlier, but I am not sure if you understood the question?
Access can 'helpfully' supply a lookup in tables. So when you look at the table you see Sara, but what it actually stores is teh ID of Sara from the lookup table?

Most people I believe emulate the lookup, but then it is evident that the ID is stored in one table and the related data in another. I fell into this trap on my first Access data base, and have never got around to changing it yet, but because I am now aware, I have not used that feature since.


Is this your scenario at all?

This is my database set up. I have a table Assets which holds the data on all the assets as well as the user associated with that asset. The user names are taken from a table Users using a lookup to select the first name and consequently the last name. On the Asset form this is done by using a combobox. I have another split form which the user can search for a particular asset or assets that are associated with a user. This is where I am having difficulty. I am using a text box for the user to enter the name and supposedly a list of assets associated will show in the datagrid. This is not happening unless I enter the user ID. I kept the search a text box so that the user can also search on other criteria.
thanks

 

[isladogs]

I'm not sure that your description or screenshots add anything that will help us understand why you are having issues.
It may be due to your use of lookup fields at table level as these mask the underlying data and make it hard to troubleshoot. Strongly recommend you get rid of these.
OR it may be one of the unwanted 'features' of using a split form. These are notoriously difficult to modify successfully. For that reason, many of us avoid them. Have a look at this thread for an emulated split form with all the same functionality but none of the disadvantages.
https://www.access-programmers.co.uk/forums/showthread.php?t=294421
Use the final version done by static

If none of this helps suggest you upload the two tables, form and anything else related to it. Replace with dummy data if you wish.

 

[sara1027]

Thanks will try this method and abandon the idea of a split form - it is also proving hard to open as a popup and keeping a reasonable size. Seems split forms aren't the thing!!
Thanks for all your help

 

[isladogs]

Thanks will try this method and abandon the idea of a split form - it is also proving hard to open as a popup and keeping a reasonable size. Seems split forms aren't the thing!!
Thanks for all your help

They're fine as long as you don't try to modify them much.
The emulated version works fine....after a lot of iterations to get to the final result
However I never use them - emulated or otherwise - form & subform are better IMO.

The other built-in form to avoid is the navigation form - similar issues

I recommend you get rid of table level lookups no matter what approach you use