?
Solved

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

Posted on 2004-08-16
16
Medium Priority
?
1,422 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 1500 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

862 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