Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ms Access - How to FIlter a combo box on Field Data

Posted on 2013-06-24
9
Medium Priority
?
1,419 Views
Last Modified: 2013-06-25
I have a task to clean up a data set.  I have one table (b) that has an employee  id and a combined last name, first name field.  Another table (a) contains other data along with a first name and last name field.  I am using a combo box to do a look-up by name and auto populate the other fields.  Is it possible to build a combo box that will filter the combo name field LIKE the last name field in the form?

In other words my task is to assign employee ids to all employees listed in table A. I thought the task might be easier if there a way to filter the combo box so that it displays names that begin with last  name that is displayed on the form.  If  Robinson is the name in the last name field can I filter the combo box to select data that appears as Robinson, John.....Robinson, Mary in the look up table.  Possible?
0
Comment
Question by:Sbovino
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39272935
Hi,

it's not completely sure at what conditions you want to filter. But in general you can always reload a list of a combobox to show a new contents. For example, you can use the "AfterUpdate" event of the last name field to set a new rowsource for the combobox by creating a SQL command which only loads the wanted records. For example:

SELECT ID, LastFirstName FROM tblB WHERE LastFirstName LIKE "*" & Nz(Forms!YourFormName!LastNameField) & "*"

Open in new window


You only need to adjust the SQL command to fit your needs.

Cheers,

Christian
0
 

Author Comment

by:Sbovino
ID: 39273189
I see the concept.  If I understand correctly I will use the afterUpdate event of the last name field to to set a new row source for the combo.  I am not seeing how this SQL command attaches to the combo query.  Thanks!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39274334
Hi,

I cannot say that exactly from your question, it would be easier to have a little example. But the concept would be: If you change something in one field then an event tells the other field to update it's list. This method would also apply to examples where you have combined comboboxes (like selecting a country first, then the city of the chosen country) or if you enter something in a field and while entering, change the list of results (like the dropdown in Google for example), here it would be the Change event of the combobox.

Cheers,

Christian
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Sbovino
ID: 39274651
Christian:

Going back to your original post, the first thing I tried was altering the SQL statement that the combo uses with your suggestion...  This worked on the first record.  When I move to a new record all my attempts at forcing the combo to requery have failed.  In other words, using your SQL when I enter the first record the combo box filtered to like names.  Moving on to the new record the combo box results from the previous record remained despite my coding several events to force the requery.  Any suggestions on what event would force a requery so that when I arrive at the next record and click on the combo box it requries?  Many thanks.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39274735
Hi,

you would need to reset the SQL command back to the original one (in other words: In the example above without the "WHERE" part). The best place to do that is to use the Form_Current event.

Moreover you should keep in mind that Access saves the setting of the combobox each time you change to design mode and save the form. Means: If you have the original SQL command without the "WHERE" part as default in the combobox and you entered something in the form which causes the SQL command to be changed like in the example above and you then go back to form design mode, the SQL command WITH the WHERE-part is still in the combobox RowSource and if you then save the form this will be saved, too.

If you reset that in the Form_Current event this will be done also when the form loads initially as this event fires after Form_Load when the first record is displayed.

Cheers,

Christian
0
 

Author Comment

by:Sbovino
ID: 39275628
Christian

I am using this code in the Form_curent event.  

Dim strsql As String
strsql = "SELECT [Emp List].Name, [Emp List].Location, [Emp List].[Emp ID] " & vbCrLf & _
"FROM [Emp List] " & vbCrLf & _
"ORDER BY [Emp List].Name;"
Me.Combo14.RowSource = strsql
Me.Combo14.Requery

Now when I move to a new record I am not filtering per the original  SQL in the combo box that follows your example (like)  

Hoping you can point me in the right direction.
0
 

Author Comment

by:Sbovino
ID: 39275899
Solved this.  The query in the combo box is as you suggested.  You saw the code above which is working.  What i did was to put code in the on enter event of the combo box changing the SQL statement to the version with the where clause.  Seems to work just fine although I don't know that this is the best way to do it.  When I enter the combo it only has filtered selections based on the last name field on the form.  Thanks,

Steve
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 39275906
Hi,

you don't need to add "vbCrLf" to SQL strings, it would be ignored, makes the thing more unreadable.

You also don't need to requery the combobox after setting the new SQL string - because if you change the RowSource property a requery will automatically be performed by Access (makes the think slower because you query the combobox RecordSource twice for each record when you move through the records).

The code is OK, this would reset the combobox, but to make it a little bit faster I recommend to first check if the contents of the RowSource is not already the contents of your strsql:

strsql = "SELECT...."
If Me.Combo14.RowSource <> strsql Then Me.Combo14.RowSource = strsql

Open in new window


In this case moving to a new record would only reset the combobox if it was changed before.

So now you also must have another code where you SET the SQL query with your other field. That should be done in the AfterUpdate event of this field. So the workflow would be: If you are in the LastName field and enter anything the combobox would be filtered to this value at the time when you leave the Lastname field. If you then go to another record your combobox should again show all entries.

If that's not what you want please create a little demo database with the two fields and a table with some demo data and upload it here.

Cheers,

Christian
0
 

Author Closing Comment

by:Sbovino
ID: 39276131
Concise and clear assistance.  Thank you for your help an suggestions.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question