How to replicate the MS-Excel indirect function in MS-Access

you use the value of the first combobox as criteria for the rowsource of the second combobox.

1 table has Makes and another has Models with the MakeID that is related to tblMake.

Private Sub cboNumberOne_AfterUpdate()
    Me.cboNumberTwo.RowSource = "SELECT ModelID, ModelName FROM tblModel WHERE MakeID=" & me.cboNumberOne.Value
End Sub

Steve

Steve
Thanks for the answer. After I did all these I got the following compile err msg: Sub or Function not defined. It highlights the "MakeID" part in the where clause as in your example. I of course substituted all the parameters with my own.
HKBoyz

It sounds like your string parsing is a bit off, can you post your line of code so we can take a look at it?
Is you MakeID a string or a number in it's base table?

Steve

Hi Steve
Pls. disregard my last comments. I got it. Now here is a new problem.
Field b only get refreshed if I updated field A. However if I navigate to another record by clicking on other fields of that record (i.e. not selecting or updating field A) then field b list doesn't get refreshed. It dispalys the whole list (which contains models of other makes). Does the same logic need to be applied to any forms property?
Thanks.

You will need to run the same logic in the Form_Current event.

Steve
Just one other thing and I hope this is the last one. It looks like I can only choose the first option from field B. i.e. If there are 10 models and first one being Camry that's the only one I can pick. Even if I click on others (they are being shown on the list) it will still select Camry. IT's a combo box.

The Enabled property should be Yes and the Locked property should be No.

I have those settings right. Do I have to add an event procedure to the Model combo box, e.g. after update or on enter....etc??

Perhaps you have the wrong field in this part ...
   WHERE MakeID=" & me.cboNumberOne.Value

can you post your code so we can take a look?

Steve

Here are my codes:
Private Sub module_id_AfterUpdate()
    Me.Function.RowSource = "SELECT Module_id, function FROM t_function WHERE Module_ID = " & Me.Module_ID.Value
End Sub

Funciton is combo2. Modile_id is combo1. I also have the same code in form_current event.

Steve
I added the following codes to the change event of field B (Function) but get a runtime error 2113: The value you entered is not valid for this field.

Private Sub function_Change()
   Me.Function.Value = Me!Function.Text
End Sub

Any clue?

Steve
I changed the codes to:
Private Sub Form_Current()
    Me.Function.RowSource = "SELECT Function_id, function FROM t_function WHERE Module_ID = " & Me.Module_ID.Value
End Sub
which solved the problem of not able to select other values on the combo box (Function_id is actually the value we need to store, not module_id, and that caused the not selecting problem. Sorry, my fault.) However the combo box got emptied after focus moved to another record, while the actual value is being written to the table.

" ... the combo box got emptied after focus moved to another record, while the actual value is being written to the table."

If you go to a new record then there will be no value for Module_ID yet so Function should be empty, is this what you are referring to?

I would suggest that you change the name of your controls to be prefixed with the control type to make it clear in your code when you are referring to a control or when you are referring to the actual value of the field from the table. Typical prefixes are lbl for lable, txt of Textbox, cbo for combobox, lst for listbox etc.

Private Sub cboModule_ID_AfterUpdate()
    Me.cboFunctionID.RowSource = "SELECT Module_ID, function FROM t_function WHERE Module_ID = " & Me.cboModule_ID.Value
End Sub

Private Sub Form_Current()
    Me.cboFunction.RowSource = "SELECT Function_id, function FROM t_function WHERE Module_ID = " & Me.cboModule_ID.Value
End Sub

Steve

Steve
I made the changes and didn't see any difference. Let me consolidate my previous messages and make it clear what I want to achieve. Here are my table structures:

Module table: module_id, module
Function table: function_id, module_id, function.
Req table: req_id,..........,module_id, function_id

The 3 tables are joined.

The main form is based on the Req table. Once users select the module and function (the id columns i.e. the bound columns are hidden so they see the module and function columns i.e. the non-bound columns only) their ids are written to the Req table.

The problem is, according to an MS knowledge base article, when we change a function and move focus to another record, the function in the previous record will be eliminated (from the recordset).

I worked around it by unhidding the bound column of function (ie. the function id) and it got rid of the elimination problem. The problem is we don't want users to see the function_id column on the form. Since we can't hide it, once a function is selected only the id will be dispalyed, not the function name itself. Is there any way to work around this?

Thanks.

I tried that but after a value is selected only the id/bound column is displayed. Changing the id column to a very small width will only make it "invisible" when you pull down the list.
Anyway, I found a solution in the MSN support network. I created a text field which will display the value of the unbound column, and limit the width of the combox box to just show the pull down key, so the two fields together look like one.
THanks for all your help. I will give you the points.