Unable to save subform field data to table

  • Thread starter Thread starter drewship
  • Start date Start date
D

drewship

Hello all. This was posted under a different title but received no responses
so I am resubmitting this.

What I have is a subform based on the below query which works correctly up
to a point.

SELECT [Order Details].*, Products.*, CCur(Nz([Quantity],0)*[UnitPrice]) AS
[Extended Price], [Order Details].ServiceType, Products.[Service Type]
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID;


There is a field on the 'Order Details Subform' called 'ServiceType' that
has a control source of 'Products.Service Type' and is set using a 'Set'
macro with an Item of [ServiceType] and an expression of 'DLookUp("[Service
Type]","Products","ProductID = " & [ProductID])'. This correctly populates
the subform with the 'Service Type' from the 'Products' table.

The problem I am having is I need the value of 'ServiceType' to be saved to
an 'Orders Details' table in the 'Service Type' field. Since the Control
Source is already being used by 'Products.Service Type'. I tried using the
below 'Set' macro (just one example of many things I tried...this seems the
most logical to me) to take the value of 'ServiceType' from the 'Order
Details Subform' and save it to the 'Orders Details' table', but this does
not work.

This is located in the 'After Update' section of the 'Order Details Subform'
'ServiceType' field. I believe that after the 'ServiceType' field is updated,
the macro should run and save the current value of 'ServiceType' to the
desired table, but it does not.

Item is set to '[Order Details]![Service Type]'
Expression is set to '[Forms]![Add an Order and Details]![Order Details
Subform].[Form]![ServiceType]'

Can someone please help me out? Thanks in advance!!
 
Have been working on several projects including this one.
Currently the control source is set up so that the Order Details table
contains a combo box that has the Products table as the control source. This
allows for the user to see the actual product being selected unstead of the
ProductID. The Order Details table is what the subform is based on.

When I set the control source for 'ServiceType' to Order Details, nothing is
displayed in the subform for 'Service Type' and nothing is saved to the
table. When I select a product, the 'Unit Price' displays and saves
correctly, but the 'Service Type', which is on the same table and same record
and the 'Unit Price' does not display or save. This is very frustrating!!

Since this database is being modified from the Order Management database
downloaded from Microsoft, I am not sure how to make the changes without
adversly affecting something else. I am going to keep working on it but any
and all help is greatly appreciated. I can upload/send a copy of the test
database if that helps.

Thanks,
Andrew

S.Clark said:
My guess would be to set the control source for 'ServiceType' to Order
Details, instead of the other table. This will handle the autosave for you.
Then you can manually handle dealing with retrieving and storing of
Product.ServiceType via code. This should work much smoother.

drewship said:
Hello all. This was posted under a different title but received no responses
so I am resubmitting this.

What I have is a subform based on the below query which works correctly up
to a point.

SELECT [Order Details].*, Products.*, CCur(Nz([Quantity],0)*[UnitPrice]) AS
[Extended Price], [Order Details].ServiceType, Products.[Service Type]
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID;


There is a field on the 'Order Details Subform' called 'ServiceType' that
has a control source of 'Products.Service Type' and is set using a 'Set'
macro with an Item of [ServiceType] and an expression of 'DLookUp("[Service
Type]","Products","ProductID = " & [ProductID])'. This correctly populates
the subform with the 'Service Type' from the 'Products' table.

The problem I am having is I need the value of 'ServiceType' to be saved to
an 'Orders Details' table in the 'Service Type' field. Since the Control
Source is already being used by 'Products.Service Type'. I tried using the
below 'Set' macro (just one example of many things I tried...this seems the
most logical to me) to take the value of 'ServiceType' from the 'Order
Details Subform' and save it to the 'Orders Details' table', but this does
not work.

This is located in the 'After Update' section of the 'Order Details Subform'
'ServiceType' field. I believe that after the 'ServiceType' field is updated,
the macro should run and save the current value of 'ServiceType' to the
desired table, but it does not.

Item is set to '[Order Details]![Service Type]'
Expression is set to '[Forms]![Add an Order and Details]![Order Details
Subform].[Form]![ServiceType]'

Can someone please help me out? Thanks in advance!!
Click to expand...
Click to expand...
 
Don't know what happened to my reply this morning, but I have figured out a
way to make this work.

It seems that if a cell is populated with data from another cell, code in
the After Update event will not fire. I placed my code in the After Update of
the Quantity cell, unlocked and enabled it (had the quantity defaulted to 1
since that is the most it could ever be), and when I add the quantity, the
code fires and the Service Type is displayed and saved.

If there is a way to have multiple macros fire from a single After Update
event, that would allow me to set the quantity dafault back to 1 and lock the
cell.

Any ideas???

Thanks,
Andrew

S.Clark said:
My guess would be to set the control source for 'ServiceType' to Order
Details, instead of the other table. This will handle the autosave for you.
Then you can manually handle dealing with retrieving and storing of
Product.ServiceType via code. This should work much smoother.

drewship said:
Hello all. This was posted under a different title but received no responses
so I am resubmitting this.

What I have is a subform based on the below query which works correctly up
to a point.

SELECT [Order Details].*, Products.*, CCur(Nz([Quantity],0)*[UnitPrice]) AS
[Extended Price], [Order Details].ServiceType, Products.[Service Type]
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID;


There is a field on the 'Order Details Subform' called 'ServiceType' that
has a control source of 'Products.Service Type' and is set using a 'Set'
macro with an Item of [ServiceType] and an expression of 'DLookUp("[Service
Type]","Products","ProductID = " & [ProductID])'. This correctly populates
the subform with the 'Service Type' from the 'Products' table.

The problem I am having is I need the value of 'ServiceType' to be saved to
an 'Orders Details' table in the 'Service Type' field. Since the Control
Source is already being used by 'Products.Service Type'. I tried using the
below 'Set' macro (just one example of many things I tried...this seems the
most logical to me) to take the value of 'ServiceType' from the 'Order
Details Subform' and save it to the 'Orders Details' table', but this does
not work.

This is located in the 'After Update' section of the 'Order Details Subform'
'ServiceType' field. I believe that after the 'ServiceType' field is updated,
the macro should run and save the current value of 'ServiceType' to the
desired table, but it does not.

Item is set to '[Order Details]![Service Type]'
Expression is set to '[Forms]![Add an Order and Details]![Order Details
Subform].[Form]![ServiceType]'

Can someone please help me out? Thanks in advance!!
Click to expand...
Click to expand...
 
Back
Top