Solved

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

Posted on 2004-08-16
16
1,136 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now