Solved

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

Posted on 2004-08-16
16
1,240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

735 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