# [SOLVED] Filter records - combo box - same table



## vudan (Sep 5, 2011)

Hello.

Again, I have a simple task to do, but couldnt find any normal answer or way to do it. Forums are full with complicate solutions and not related to my issue (of course). So lets see the task:

I have a formA in which I want to search for a person through Combo Box. I have made a querry (Q1) in which I have put 3 fields (for example: PersonID, Name, CarType). PersonID is pKeyA. So, my querry lists all the records from TableA with given setup and that is shown in ComboBox. What is the problem, that if I have the same person with different CarTypes, and when I choose the second or third or any other CarType for the same person, the record on the FormA is not changed to that selected item.

In other words, if a Peter has 3 cars only the 1st car is displayed on the Form. I know that there should be 2nd table in which would be the CarTypes, but this is the status of this database. It is a little late to fix it :sigh: Here is the code I have entered in AfterUpdate row in ComboBox Property sheet:

Private Sub Combo119_AfterUpdate()

Me![PersonName].SetFocus_ 'This is the row where the form displays record for the selected data from combobox?'
_
DoCmd.FindRecord Me!Combo119, acEntire_ 'This is the row what I dont want. It goes to the next record if I hit ENTER in combobox. I have left it here with no reason'_

End Sub

I didnt want to offend others with my title, but I have seen that Albert EMC2 is the most active office solution "manager" ray: Of course, other office gurus are welcome too :wink:

Thanks in advance


----------



## AlbertMC2 (Jul 15, 2010)

*Re: Filter records - combo box - same table*

Hi

Thanks for the vote of confidence but unfortunately I am definitely not the most qualified office support tech - I am just the first one to have answered your previous thread.

I just want to clarify something. 
Do all the fields in question (PersonID, Name, CarType) all belong to the same table?
If so how is it that PersonID is the primary key and therefore there cannot be duplicates? Or am I misunderstanding?


----------



## vudan (Sep 5, 2011)

*Re: Filter records - combo box - same table*

Hello Albert 

I am very honored when someone helps me and You did.

Lets clarify...
All the fields belong to the same table. PersonID is the pKey and it is autonumber.
You are right about that pKey. If it is set to NoDuplicates, then every record is treated as unique one.... Is there a solution, that picking a name with its attribute from ComboBox, programs check the records from the fields within querry, compares it with records in table and shows it on form if it is true? Similar to this:

NAME ATTRIBUTE ID (optionally showed)

John 100/2011 20
Jack 110/2011 21
Hugo 111/2011 22
Hugo 5547/2010 23
Hugo 4787/2009 24
Jerry 11147/2009 25
John 112/2012 26

It is only necessary to show data for the selected record. I do not need a list of all attributes belonging to a person. :4-book:

I know that I could make a table just for these attributes, but if what Im asking is impossible or too complicated, then maybe You can give me some advice how to append this solution to that what You have helped me with before.

Thanks


----------



## AlbertMC2 (Jul 15, 2010)

*Re: Filter records - combo box - same table*

Hi

Sorry but I am hopelessly confused. Your combo box is working right? and it displays ALL the records correctly as you have written in your previous post:

```
NAME      ATTRIBUTE      ID (optionally showed)

John      100/2011       20
Jack      110/2011       21
Hugo      111/2011       22
Hugo      5547/2010      23
Hugo      4787/2009      24
Jerry     11147/2009     25
John      112/2012       26
```
So then when you select one of the items in the combo box it shows all the details on the form for that item. Is the form blank before you select a name from the combo box?

Can you give an example from the names listed above what you want it to show and what it is showing?


----------



## Dragoen (Apr 10, 2009)

*Re: Filter records - combo box - same table*

Hi vudan,
Like Albert I am not certain what you are asking for so I'll tell you how it should be set up and then you can compare that to what you have. Say you have a form with a combobox on it - cboDrivers. On the Combobox properties there is a bound column that is usually set to the first column. Normally this column is not displayed since it is a key value for the record. The column width for the bound column would be set to 0" so you might have a Column Widths entry of 0";1";0.75" if you were displaying Name and Car type in the combobox. Some of the other properties would be: Bound column = 1 and Column Count = 3.

So now when you visually pick "Dragoen Lamborghini" from the list, you are really picking the record's key value stored in the bound column for that entry. Since that is a unique value, you will only get information related to my Lamborghini records (hey, I can dream) when you use that key value in your queries. Then when you reference Form1.cboDrivers you are referencing a key value.

Just to add, the RowSource query for cboDrivers could be like:
Select DriverID, Driver, CarType from tblDrivers order by Driver, CarType;

Hope that helps,


----------



## vudan (Sep 5, 2011)

*Re: Filter records - combo box - same table*

Hello all.

Thanks for the tips, but things about column width and bounding is not a problem. The setup for it is: Bound column=1 (only with this value works; if I enter 2 or 3 combo box doesnt show the data) and Column Count=3 (with this I see all the fields in the combo box clearly).

Combo box is showing the fields just as Albert and I have post earlier: John-100/2011-20 etc.) My problem is, when I pick another John-112/2012-26 then I dont get data for this John but the all data stays from the "previous" John :smile: (that means all). That refers to Hugo 5547, Hugo 4787 too (it shows only Hugo 111)... I have checked if there is a change in the bottom of the form where the navigation arrows for the records are (next, previous...) but it stays on that record where John 100 is (or Hugo 111 is...)

The Raw Source Query for the combo box was set to:

SELECT [Pacijent Query].[Name], [Pacijent Query].[Attribute], [Pacijent Query].[PacijentID] FROM [Pacijent Query] ORDER BY [Name], [Attribute];

And to answer the last question from Albert; I have set up the database in a way that if a user opens the database, form loads automatically with a new record. Every record (Hugo 111, Hugo 4787, John 112...) has different data on that form and if the user wants to check Hugo 4787 he/she always gets Hugo 111. One more thing, if a user picks the record Trinity 555 (for example) and then pick the Hugo 4787 record, form will show Hugo 111 data. Like other Hugos and Johns dont exist 

Hope this cleared some misunderstandings :wink:


----------



## AlbertMC2 (Jul 15, 2010)

*Re: Filter records - combo box - same table*

Hi

You don't really need a "VBA" solution for this. When creating the combo box select "*Find a record on my form based on the value I selected in my combo box*". This then creates a *macro *assigned to the *After_Update* event that makes the record you selected display on the form.
I noticed that when you select the other options when creating the combo box then I get the problems you seem to be having.
See the attached database and notice the macro assigned to the After_Update event on the combo box.

Hope this is what you wanted.


----------



## vudan (Sep 5, 2011)

*Re: Filter records - combo box - same table*

ray:

Thank You Albert so much .... and thanks is going to Dragoen too. This works as I wanted. I would write it down what has been done for others if they have a similar problem as I had:

I have deleted the existing ComboBox (other way it will not work), created the new one using wizard. I have choose the option _I want the combo box to look up the values in a table or a query_ and selected that data are coming from the table from where form is taking data. I have deleted query too, because that was unnecessary. On _AFTER UPDATE_ field I have selected _macro builder_, found _SearchForRecords_ option and entered the next line in _Arguments _field (or if you want down in _Where Condition_ field):

="[ID] = " & Str(Nz(Screen.ActiveControl;0))

where ID is your pKey or field from where this macro will pick up the record (if I interpret wrong correct me). The Str(Nz... line I dont recognize, maybe other could tell.

And woala, click-pick-read :wave:

Thanks a lot. Cheers :4-cheers:


----------



## Dragoen (Apr 10, 2009)

*Re: Filter records - combo box - same table*

Hi vudan,

Glad that Albert was able to get you fixed up with a quick easy fix. That Nz function eliminates Nulls. If the value of Screen.ActiveControl is null, it replaces it with the value of the second parameter, in this case a 0. A very useful function.


----------



## AlbertMC2 (Jul 15, 2010)

*Re: Filter records - combo box - same table*

Hi

Glad you got it sorted.

*STR *= converts what ever is in the brackets to string. So it will convert the ID to a string representation. (Str function)

*NZ *= Returns a value if if a varaible is null or another value if it is not null. 
(NZ function)

*Screen.ActiveControl* = returns a reference to the object that has the focus at the time, which in your case is the combo box. (Screen.ActiveControl function)


Therefore putting it all together:
NZ returns the value of the combo box (Screen.ActiveControl) but if the the value is null then it returns 0. Str then converts this number to a string.


----------



## vudan (Sep 5, 2011)

*Re: Filter records - combo box - same table*

Hello guys.

I really appreciate the help and the explaining of these code lines. Really irritates me if I dont understand the code 

I hope you will be around if I get stuck in some VBA + .mdb problem.

Moderator, please consider this thread SOLVED :wave:


----------

