Solved

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

Posted on 2004-08-16
16
1,217 Views
Last Modified: 2008-02-01
Hi there,
Here is the problem: Have 2 fields on the same form, both combo boxes. Depends on the value selected on the first field (field A), the second field (field B) has to provide a list of values related to the value in field A. The Kelly blue book has the best example, where when you select a make (e.g. Toyota), the model field will automatically provide a list of all Toyota models. How do we do this in MS-Access?
I know in Excel we can use the indirect function to do it.
Any hint will be appreciated,
Regards.
0
Comment
Question by:HKBoyz
  • 9
  • 7
16 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 11813888
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
0
 

Author Comment

by:HKBoyz
ID: 11814368
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
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11815502
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
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:HKBoyz
ID: 11815534
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.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11817934
You will need to run the same logic in the Form_Current event.
0
 

Author Comment

by:HKBoyz
ID: 11820265
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.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11822490
The Enabled property should be Yes and the Locked property should be No.
0
 

Author Comment

by:HKBoyz
ID: 11823052
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??
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11823324
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
0
 

Author Comment

by:HKBoyz
ID: 11823527
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.
0
 

Author Comment

by:HKBoyz
ID: 11824100
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?
0
 

Author Comment

by:HKBoyz
ID: 11825648
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.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11829174
" ... 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
0
 

Author Comment

by:HKBoyz
ID: 11830279
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.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 11839087
Try making the column width of the bound column very small, not 0 which is the same as hiding) and You might only see a thicker left hand border than usual instead to the ID field.

ColumnWIdth: 0.0007";2"

Steve
0
 

Author Comment

by:HKBoyz
ID: 11840818
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

829 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