Dependent Combo box issue

[pratim09]

I have 2 combobox in a form . cmb1 and cmb2 . On the cmb1 on change event i am populating the values of cmb2 according to sql query in code. First time the cmb2 values populates correctly .Also on change property of cmb2 populates other values in the form.

The problem is 2nd time when I select cmb1 for some other value the cmb2 retains the values of the first cmb1 search along with the new ones for the recent cmb1 value.Ideally it should show teh new values only.

How do i get rid of these.

[rpeare]

Is the control source for the second combo box using the contents of the first combo box as a criteria?

If so you likely just need to use

me.combobox2.requery

after you update the contents of combobox1

[pratim09]

Yes the 2nd combo box takes the result of first combo box as criteria.As u suggested i did the requery on combo box 2 on the after update event of combox 1 but its not working.Actually when I put a 2 break pt one on the on change function of combobox 1 and one on the after update event and try debugging I see that the after update event is getting called before on change event .Hence its not working that way .Any help?

[rpeare]

try using the on exit property of the first combo box to refresh the second combo box

[pratim09]

i tried it but its not working.I am attaching a small sample of my app .Plz let me know if you can help on this.

[rpeare]

I assume you're talking (in this question) about combo box cmbReleaseName and cmbWorkIdentifier.

I'm also assuming that when you go from cmbReleaseName you're attempting to update the contents of cmbWorkIdentifier based on the contents of cmbReleaseName. That's called a cascading combo box. If that is indeed what you're trying to do you're making it way more complex than it needs to be, and your tables are really not set up very well. It looks to me like you're attempting to add items to your database on the fly and have them appear in your combo box list. That's possible, but none of your tables has a single field with an identifier. So when you add an item (I don't know if that part is working) it's not filling out the rest of the information. In this case you're just extracting information from the WorkIdentifier field of your Tbl_Project_List Table where someone can add whatever they want which is an extremely bad idea. Let's say someone adds something called 'projstep1 and another person adds something like 'Proj Step 1' these, though they should be the same, are going to be be see as different items in your database so any grouping is going to be severely thrown off.

What you should have is an entirely different table that has a list of work identifiers (auto number unique identifier) then possibly another table that has a list of releasenames and which workidentifiers go with those release names then filter your combo box based on the contents of that table.

Also 1 of the 4 tables in your example database does not have a unique identifier (different from a primary key) tbl_Work_Activities.

There are a lot of problems with this database but if you want to get it working as a cascading combo box with your current setup I would just change the ROW SOURCE TYPE property of your second combo box to Table/Query. Change the ROW SOURCE to

SELECT TBL_PROJECT_LIST.WorkIdentifier FROM TBL_PROJECT_LIST WHERE (((TBL_PROJECT_LIST.ReleaseID)=forms!frmhourstrackingadd!cmbreleasename));

Then you don't need any of the code you currently have in the on change or on exit property of the first combo box.

[anwaar]

It can easily be done using DLOOKUP function in Access VBA.

Save your required values in a table that have 2 columns like "CustomerName" and "CustoemrID."
Call both columns in two seprate Queries.
Assign one query to cmb1 and second query to cmb2.
Now use "DLOOKUP" function in VBA in "cmb1.Lostfocus" event procedure.
Now if you select one value in cmb1 and move to any other control value in cmb2 will autometically update.

In case of any further query, let me know, I will share that particular file with you

[pratim09]

thanks.Can u plz share the file

[anwaar]

Here it is

[pratim09]

thanks anwar but can u plz provide the password to look into the code for the Dlook function