READY TO LOSE IT!!!

[Scorpion]

I need my form to automatically display the appropriate cost rate for an item after I select it in a combo box. As I enter an item in "Account" (eg. E0701) I want the field labeled "Cost Rate" to automatically enter in the cost rate for "E0701" (eg. $12.00). All accounts and appropriate cost rates can be found in the union query called "Job Costing Descriptions". I need help trying to figure this out!!!!!!!!!!!!!!!

 

[Carol]

Put the following code (as an Event Procedure) in the "After Update" of your combo box:

Sub Account_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Cost Rate] = '" & Me![Account] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Change your field names accordingly.

Good luck

 

[Scorpion]

In the code, where does the query "Job Costing Descriptions" fit in.

 

[Carol]

This query is the recordsource for your form. There is no code required here. Put the code I gave you in the After Update of the combo box. Once you select an account, the cost rate, along with all fields will be automatically shown on your form.

 

[Scorpion]

When I run the form, an error message comes up stating "No Current Record". The basis for this form is not the query "Job Costing Descriptions" it is the table "Job Costing". "Job Costing Descriptions" is a union query which brings together five tables into one query (Employees, Equipment, Rentals, Subcontractors, & Materials). The union query brings all of these tables together along with its appropriate "Cost Rate". To give you a better understanding I will lay out my form for you.

I have columns with the heading as follows in order of which I enter them:

Date - Date work occured
Job # - Job worked on
Class - Type of work completed
Activity - Detailed description of work
Cost Type - Designation between Labour, Equip, etc.
Description - Where I enter the account code
Units - Number of hours, tonnes, etc.
Cost Rate - Automatically entered based on entry in Description
Total - Automatically calculated multipying Units & Cost Rate
Comment - General comments

Any suggestions.

 

[Carol]

Your recordsource for your form should be the query that displays all fields that you want populated. Your combo box should be an unbound combo box with its source set to either a sql or an underlying query that you can obtain the "Cost Rate" from. Use the column widths and bound column of this query to show only the Account field. It seems to me that you have set your forms query incorrect. Set both your combo box and form query to your Job Costing Descriptions union query.

Good luck.

 

[Scorpion]

My union query "Job Costing Descriptions" does not show all of the fields required for the form. I am using the form for data entry purposes. I need to have the record source as the table "Job Costing" but read the union query "Job Costing Descriptions" to find the appropriate unit rate.