Create Select List Based on Results from Previous Field

[Scorpion]

I want to learn how to create a select list based on results from a previous field. I am creating a data entry form for inputting daily project reports. As I carry on through the data entry fields I enter a field entitled "Cost Type". In this field I enter a specific code which reflects the type of cost that I am entering. The costs are broken into five groups Labour(L), Equipment(E), Subcontractors(S), Rentals(R), and Materials(M). Each of these groups has nine different sections to into (L1 - Asphalt, L2 - Concrete, etc.). Each of these groups also has a table created to reflect the information that I am looking to extract.

The field after "Cost Type" is "Description". What I would like to achieve is that based on what I have entered into the "Cost Type" field (eg. L1, R2, etc.) a combo box reflecting the specific corresponding table will be available in the "Description" field. I will then be able to select the appropriate description (Employee, equipment, etc.) required for this entry.

 

[AlaskanDad]

Have the recordsource for the Description combo box as a query. The query would have two main columns: Cost Type and Description. The first column would have a Criteria statement such as:

Like Forms!yourmainform.CostTypeTextBox

This would filter out all of the descriptions except for those that matched the CostTypeTextBox where you entered your Cost Type.

 

[Scorpion]

Thank you for your reply.

I tried your solution, but each time I run the query from the form, it asks me to enter parameter value. I want the query to automatically read the entry in the "Cost Type" combo box and evaluate the select list from there.

Also, in the query I want to be able to recognize only the first letter of the "Cost Type". Cost types are numbered, E1,E2,E3, etc. and all I need it do do is match the first letter of the Cost Type (eg. E) with the first letter of the group of "Descriptions".

 

[AlaskanDad]

Which parameter does it ask for? In the Criteria line are you using the name of the combo box in:

Like Forms!yourmainform.CostTypeComboBox

Your Description combo box should have the query as its recordsource so you don't have to run the query from the form.

If you want to only check the first letter of the CostType then change the Criteria line to read as follows:

Like Left(Forms!yourmainform.CostTypeComboBox,1) & "*"

 

[Scorpion]

The solution you submitted was perfect. Thank you very much. The only concern I have now is that this form is a continuous form (which I want)and once I put the first entry in and got to enter the second entry when I get back to "Description" it will only give me the same select list as the first entry and I need it to once again show the proper select list. I tried to requery the list but when I do that the form resets itself to being blank.

 

[AlaskanDad]

Add an After Update procedure to your Cost Type combo box. Have it requery the form after you update it. This should reset the record source in your Description combo box.

 

[Scorpion]

I created a macro in which I put in the "Action" column "Requery". I then went back to the form and under the Cost Type Combo Box After Update I put in the Macro Requery. Now when I enter the cost type in the form, the form flashes and resets itself to being blank as if I had finished the record. The record is now in my table but it did not give me a chance to enter "Description" or any of the other columns.

 

[AlaskanDad]

Alright then.

How about this? Instead of requering your form (obviously that doesn't work) try the following for your After_Update procedure. Set the focus to your Description combobox and then requery only the Description combobox. You should be able to do this through macros or better through VBA.

I used to work solely through macros thinking that I would never need VBA. I was wrong. It's the difference between playing with Legos and building with steel beams.

 

[Scorpion]

I am fairly new to this and do not know how to use VBA at all and am unsure how exactly to get the macro to do what you are suggesting. If possible please help show me the way.

 

[AlaskanDad]

If this is your first time, I'll be gentle.

You already know how to find the On After Update event procedures. That is where you previously set it to run a macro. Now, instead of running a macro, set it to "Event Procedure". Now, click on the "..." to the right of "Event Procedure". You are now in the VBA code world.

The two commands that I described above would be written like this:

Forms!NameOfYourFormHere.DescriptionComboBox.SetFocus
Forms!NameOfYourFormHere.DescriptionComboBox.Requery

That's it.

 

[Scorpion]

Code Worked Perfectly.

Thank you very much, I appreciate your time & help.