How to apply filter on combo box & passing value between forms

[maxchang9]

First of all, my Access is written in Chinese, but ill try my best to illustrate my question
-------------------------------------------------------------------------------------------------
Table A Import ingredients ----->Form I
Table B Import Details ----->Form II
Table C Ingredients Details ----->Form III
Table D Supplier Details

Relations:

Table D 1--------∞ Table A 1--------∞ Table B


Table D 1--------∞ Table C 1--------∞ Table B
------------------------------------------------------------------------------------------


I Got a Form I (Table A) that contains a sub-form (Table B)
User insert date/ branch/ supplier on the boxes above (belongs to Table A), and insert Ingredient name / price/ number in the sub-form. (belongs to Table B)


The 1st column in sub-form ( name of ingredients) are link to Table C which hold all ingredients details. I create a combo box so user may select.

The problem is, when the list gets too large, user will find difficult to find the right ingredient. (since Chinese character can't sort in alphabetical order)

The solutions i came up with are:

1. Apply filter within the combo box (in sub-form : ingredient): Since user previously select the "supplier" in the combo box above, is there a way to filter out other ingredients which aren't link to the supplier

2: Introduce search feature: I create Form III(Table C) which allow user to search ingredient through typing. (I wrote macros within the button).



The problem is, how do i pass the selected data from Form III back to Form I (inside it's sub-form)??

I am still learning Access while making this, so any suggestion will be appreciate. Thank you.

[June7]

Option 1 sounds like you are asking for what is called cascading or dependent combobox. This is a common topic.

Option 2 could be done with code behind Form III that sets: a) global variable or b) TempVar or 3) textbox on Form I

[maxchang9]

Thanks man, i spend hours on digging cascading combo-box and finally got it to worked. However, as i proceed ,more questions occur.

1. In order to apply cascading combo-box in the sub-form, i set criteria ([forms]![formI]![combo box]) on it's source (table B) .

However, when i edit the tableB: ingredient (without open Form I), a pop up occur and ask me for parameter ( which i have to enter supplier manually before i start edit)

Is there a way to fix this? ( exp: if Form I not open, do not apply this criteria)

2. I learned how to create and set tempVar value while loading a form, however i am struggling in passing value between different objects (within same form)

i.


For instance, how do i pass value from textbox -> subform ? if using macro/expression, which is "action" am i going to use? Do i need a button to assist? or it can be done in focus/dbl click event?
-------------------------------------------------------------------------------------------------------------------------------------------------------------
ii.


how do i set TempVar (i created it when this Form III is load) to the value i highlight when the user click it's corresponding checkbox (on its left). The Form is a continues form

Thank you so much for the answer. The biggest upset is not knowing the "correct question" for the answer i seek. So even a hint or keyword will be very appreciate.

[June7]

I have never used TempVar. But I suppose you would use checkbox AfterUpdate event to set the TempVar value.

Suggest using GotFocus event of the conditional combobox to set its RowSource or just Requery.

However, be aware cascading combobox (or listbox) that has lookup alias will not work nice on Continuous or Datasheet form.