Default value fails (1 Viewer)

[Buster]

I have an item table with one field being UnitCost. I have a JobDetail table with one field being UnitCost. Both tables are linked by ItemCode.
My data capture form shows fields from both tables. What I want is for the JobDetail.UnitCost field to have the Item.UnitCost field as its default value. I have entered [Items]![UnitCost] as the default value in the properties data tab. However this gives a #Name error in the JobDetail.UnitCost field. This is because the Item.UnitCost field is the standard price that may occasionally be changed by a special price in the JobDetail.UnitCost field.

The Control Source for the JobDetail.UnitCost field is UnitCost and this works fine with no default value.
The record source for the form is:
SELECT JobDetails.*, Items.ItemDescription, Items.Detail, Items.Supplier, Items.Units, Items.Width, Items.UnitCost AS Items_UnitCost, JobDetails.ItemCode, JobDetails.Area, JobDetails.Action, JobDetails.Quantity, JobDetails.MarkUp, JobDetails.VAT, JobDetails.Deposit, JobDetails.UnitCost AS JobDetails_UnitCost FROM Items INNER JOIN JobDetails ON Items.[ItemCode] = JobDetails.[ItemCode];

How do I set Job.UnitCost to have a default value of Item.UnitCost

 

[arnelgp]

you do not need fields from item table, only the fields from jobDetail table.

if ItemCode is a combobox in the form, you include the Items.UnitCost as another Column in the combobox.
then you add Code to the ItemCode combobox AfterUpdate Event:

Private Sub cboItem_AfterUpdate()
'if the combobox has 3 columns:
'ItemCode, ItemDescription, UnitCost (combo is 0 based, so column 0 is actually column 1)
'Assign Items.UnitCost to JobDetails.UnitCost
Me.UnitCost = Me.cboItem.Column(2)
End Sub

 

[Buster]

Tried this and get compile error - Invalid outside procedure

Private Sub cboItem_AfterUpdate()
Me.UnitCost = Me.cboItem.Column(6)
End Sub

Private Sub ItemCode_AfterUpdate()
Me.ItemCode = Me.cboItem.Column(0)
End Sub

Option Compare Database

DoCmd.Close acForm, Me.[Check Report]

End Sub

 

[Buster]

Oops - problem solved. I had a rogue report that had faulty VB code attached.
Now working. Thank you.

Another question. Is there a way to populate some fields on a data entry form with data from the previous record saved.

 

[isladogs]

The error was in the last 3 lines:
a) Option Compare Database should be the first line in each code module
b) There is no Private Sub line before DoCmd.Close acForm ....

You can reuse the previous record values by first saving these as variables or tempvars then using those values to pipulate the new record.
Or you can set the default value of each field to the last saved record.
Both methods work. Take your pick ....