Insert Value Based on Combo Box

[Scorpion]

I am very close to figuring this out but need a little help. I need to have a field automatically update with the appropriate unit rate based on what I enter in the combo box.

What I am doing is entering a "Description" in a field and after I enter the appropriate value I want the "Unit Rate" to be automatically entered in the "Unit Rate" field.

I have created a query to filter the data down to the appropriate item and have also created a combo box in "Unit Rate". Now when I enter my data in "Description" the program puts the appropriate "Unit Rate" in the combo box but I have to use the mouse to press the arrow to select it. I want to be able to have that unit rate automatically entered. I also need to have the ability to override this and enter a different rate if I need to.

 

[AlaskanDad]

In your Description_After_Update procedure type:

Me.UnitRateComboBox = Dlookup ("[UnitRate]","TableWithTheUnitRates","[DescriptionUnitRateID] = " & Me.DescriptionComboBox

You can type over this value if you want to.

 

[Scorpion]

Code did not work. Probably something wrong on my end.

I put in the code in the expression builder part of the "Description" combo box After Update space. After I put it in and return to my form, after I put in a value for "Description" the form does not make any changes. Whan I go back to design view and look under the After Update part of "Description" the expression I build has disappeared????

[This message has been edited by Scorpion (edited 06-23-2000).]

 

[AlaskanDad]

The code I gave you is in Visual Basic, not an expression. In the On After Update event, you need to select "Event Procedure" instead of macro or expression. Once you have selected it, click on the "..." to the right of it. This will take you to the VBA code world. Then, paste the code in the procedure.

Try it out for size.

 

[Scorpion]

The code I put in is a follows:

Me.[Unit Rate] = DLookup("[Cost Rate]", "[Job Costing Descriptions]", "[Account] = " & Me.[Description])

When I enter "Description", Access comes up with an error that says:

The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object 'L0005''.

'L0005' refers to the "Description" I have entered in the field.

To brief you quickly on where I want to get information from.

In my Form "Job Costing" after I enter "Description" I want to have the Field "Unit Rate" updated automatically. The information for this unit rate can be found in the Union Query "Job Costing Descriptions". The Field that should be the same value as the value I entered in the "Description" Field of the Form is entitled "Account". The Field where the proper "Unit Rate" is located is entitled "Cost Rate".

The reason I have a Union Query is that the "Descriptions" for Job Costing come from 5 different tables, Employees, Equipment, Subcontrators, Materials, & Rentals.

Please help me rebuild this code so it is proper. Thank you very much in advance.

 

[AlaskanDad]

Your problem may just be in the syntax. I noticed a typo in your code. You wrote:

Me.[Unit Rate] = DLookup("[Cost Rate]", "[Job Costing Descriptions]", "[Account] = " & Me.[Description])

Eliminate the [] in the middle argument so that it reads:

Me.[Unit Rate] = DLookup("[Cost Rate]", "Job Costing Descriptions", "[Account] = " & Me.[Description])

Based on your narrative, this should work for you.

 

[Scorpion]

The same error message comes up.

I have entered this:

Private Sub Description_AfterUpdate()
Me.[Unit Rate] = DLookup("[Cost Rate]", "Job Costing Descriptions", "[Account] = " & Me.[Description])
End Sub

What is happening?

[This message has been edited by Scorpion (edited 06-23-2000).]

 

[AlaskanDad]

I'm afraid that I can't help you without rutting around in your queries and seeing things for myself. That error message is a mystery to me.

I'm sorry.

 

[Carol]

Another easy way to accomplish this is to add this in the criteria line of the field Unit Rate in your query:

[Forms]![JobCosting]![Description]

As long as your query is set up with the correct joins this will automatically update your Unit Rate depending on your description that you have entered.

 

[Scorpion]

I have done what you suggested but I still have to click the down arrow to select the unit rate in the combo box. You mentioned joins may not be correct. Can you expand further on that.

[This message has been edited by Scorpion (edited 06-26-2000).]