Update Form Values (1 Viewer)

[scouser]

Hi to all. Its been a while since I worked with Access.

I have attached a sample database with some dummy data.

frmPackages
frmPackageSubform

User Input:
User enters Package Master Record
User enters Package Details.
The user can select a ProductID from a combo box. This then updates Quantity and Product Cost fields

The user can amend the quantity.

The issues:
1: frmPackages has a field TotalPackageCostPrice. This should update 'After Update' 'Event Procedure' on ProductID combo box. However when the user selects ProductID from the combo box the value is not immedialty shown. If you place your mouse in the field it updates to show the value.

2: If the cost price is increased in frmProducts the increase is not refelcted/updated in frmPackages on load.

I hope that all makes sense.

Kind Regards,
Phil.

Note: The attached database was created for Access 2007-2013

 

[scouser]

Here is the event procedure code snippets:

Private Sub cboProductName_AfterUpdate()
Dim intTotalCost As Double

Me.ProductDescription = Me![cboProductName].Column(1)
Me.ProductCost = Me![cboProductName].Column(2)
Me.Quantity = 1

DoCmd.RunCommand acCmdSaveRecord


intTotalCost = DSum("ProductCost", "[tblPackageDetails]", "PackageID = Forms!frmPackages!PackageID")
Forms!frmPackages!TotalPackageCostPrice = intTotalCost

End Sub

Private Sub Quantity_AfterUpdate()

Dim intTotalCost As Double

Me.ProductCost = Me.Quantity * Me![cboProductName].Column(2)
DoCmd.RunCommand acCmdSaveRecord

intTotalCost = DSum("ProductCost", "[tblPackageDetails]", "PackageID = Forms!frmPackages!PackageID")
Forms!frmPackages!TotalPackageCostPrice = intTotalCost

End Sub

Thanks,
Phil.

 

[Minty]

You probably need a form Refresh or Requery to update the values.

 

[scouser]

I have tried the following:

Private Sub Form_Load()

    cboProductName.Requery
 
End Sub

This has not produced the desired result.

I think I need a way to requery the Products table on form load?

Set the ProductCost to equal tblproducts.Unitprice * tblPackageDetails.Quantity where tblPackageDetails.PackageID = frmPackageDetails.PackageID

or possibly not

Kind Regards,
Phil.

 

[scouser]

OK, I am working on a slightly different design...
Thanks,
Phil.

 

[scouser]

I have uploaded a slightly different design.

I feel I have resolved one of my initial questions however 'TotalPackageCostPrice' in frmPackages does not behave as required.

I want it to reflect the sum of ProductCost in frmPackagesSubform after update of either cboProductName or Quantity.

The user has to manually re-enter quantity (frmPackagesSubform) and then click (set focus) TotalPackageCostPrice (frmPackages).

Any advice appreciated.
thanks,
Phil.

 

[scouser]

Here is the code I am trying to tweak...

Private Sub cboProductName_AfterUpdate()

Dim intTotalCost As Double

'Forms!frmPackages!TotalCostPrice = Me.ProductCost
'DoCmd.RunCommand acCmdSaveRecord

intTotalCost = DSum("ProductCost", "[qryPackageSubForm]", "PackageID = Forms!frmPackages!PackageID")
Forms!frmPackages!TotalPackageCostPrice = intTotalCost
'DoCmd.RunCommand acCmdRefresh

End Sub

Private Sub Quantity_AfterUpdate()

Dim intTotalCost As Double

'Forms!frmPackages!TotalCostPrice = Me.ProductCost
'DoCmd.RunCommand acCmdSaveRecord

intTotalCost = DSum("ProductCost", "[qryPackageSubForm]", "PackageID = Forms!frmPackages!PackageID")
Forms!frmPackages!TotalPackageCostPrice = intTotalCost
'DoCmd.RunCommand acCmdRefresh

End Sub

Phil

 

[JHB]

The problem you are experiencing can be traced back to one thing, you try to retrieve data before the data is written to the table.
To write data to the table insert Me.Dirty = False in your code.

Generally, you should not save calculated totals in tables, if you need totals then run a query instead.

Instead of using DSUM in code, place a control in your subform which sum up the values (make it invisible), then you can refer to this control from your main form.

 

[scouser]

Hi JHB, many thanks. Me.Dirty = FALSE is a new one to me.

I am in the process of implementing your advice by removing the calculated totals from the tables + tidying up the unwanted VB.

Great work and much appreciated.
Phil.

 

[JHB]

You're welcome, good luck.
If any problem write back!

 

[Wiz47]

Hi JHB, many thanks. Me.Dirty = FALSE is a new one to me.

I am in the process of implementing your advice by removing the calculated totals from the tables + tidying up the unwanted VB.

Great work and much appreciated.
Phil.

I generally run it as:

If me.dirty then
Me.dirty = false

End if

The only difference is it checks to see if the record has been saved before saving it again.

 

[JHB]

I generally run it as:

If me.dirty then
Me.dirty = false

End if

The only difference is it checks to see if the record has been saved before saving it again.

No need for checking if Me.Dirty is true in this case, it is, because the code line is placed in the after update event!

 

[arnelgp]

you can also do that on the After Update of the form, when the record is saved.

 

[Wiz47]

No need for checking if Me.Dirty is true in this case, it is, because the code line is placed in the after update event!

That's true, good point.