Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: Save value from subform to table

    Hello all.

    The title of this question seems easy enough but I have been beating my head against a brick wall for several days. What I have is a subform based on the below query which works correctly up to a point.

    Code:
    SELECT [Order Details].*, Products.*, CCur(Nz([Quantity],0)*[Unit Cost]) 
    AS [Extended Price]
    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

    Code:
    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

    Code:
    [Order Details]![Service Type]
    Expression is set to

    Code:
    [Forms]![Add an Order and Details]![Order Details 
    Subform].[Form]![ServiceType]
    Can someone please help me out? Thanks in advance!!
    Last edited by drewship; 09-21-09 at 13:11.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This

    "SELECT [Order Details].*, Products.*, CCur(Nz([Quantity],0)*[Unit Cost])
    AS [Extended Price]
    FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
    Details].ProductID;"

    doesn't look like a recordsource for a query which is updating records but instead looks like a recordsource for a read-only subform.

    Usually your recordsource for a subform to update records would instead look something like this:

    "Select * from [Order Details] WHERE [ProductID] = " & Forms!My_Main_Form_Name!ProductID & ""

    ie. It doesn't have calculated values and the recordsource is typically based only on the relational table with criteria.

    (and for the ProductID field on your subform, you put in for the "DefaultValue" property of that field: =Forms!My_Main_Form_Name!ProductID - which defaults the ProductID value to the main form ProductID value for new records.)

    Service Type (for the Products table) would then be on the main form (as part of the Products recordsource) and you would set the value of Service Type on the main form versus populating Service Type (for the Product table) on the subform.

    Keeping your forms limited to a recordsource based on 1 table (versus joined tables) prevents problems with updating the data on the subform. Sometimes you have to test (ie. manually) to see if a field can be updated and then disable/lock it or write code against updating it.

    Note:
    DLookUp("[Service Type]","Products","ProductID = " & [ProductID]) could also be put in the DefaultValue property of the Service Type field.

    Macros are an ugly beast. I wouldn't write/test a macro but instead look at my vba syntax for troubleshooting:
    (see this set of posts and my responses: http://www.dbforums.com/microsoft-ac...orm-combo.html)
    Last edited by pkstormy; 09-21-09 at 22:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2009
    Posts
    48
    Thanks pkstormy. I am still learning about databases and have been modifying the Order Management Database provided by Microsoft. I just changed the names of the example to fit my needs for this form/subform combination. I did add a table to the query that the subform is based on so I could get the [Service Type] and a cost field (the values are selected from another table via combo box) added to the form in the hopes I would be able to save data to it. Basically, the subform has a combobox from which I select a product (which is on the Products table). I want the associated [Cost] of the product and the [Service Type] (both are on the Products table) to display on the subform and be saved to the [Order Details] table. I have created some other items in this database that seem to work as desired, but I do not know what to do with the subform.

    I will look at the information you provided and hope it will shed some light.

    Thanks again!!
    Last edited by drewship; 09-22-09 at 09:27.

  4. #4
    Join Date
    May 2009
    Posts
    48
    Ok...I tried to modify it a bit to make it easier after reading what you posted as well as the links and some trial and error on what I really needed.

    It starts like this:

    I have an Order Details Extended query that is based on an Order Details table that has the required fields, some of which populate correctly with data.

    Code:
    SELECT [Order Details].*
    FROM [Order Details];
    The query is the recordsource for the Order Details Subform which has 3 fields.

    ProductID which is a combobox with a controlsource of ProductID and the following rowsource:

    Code:
    SELECT DISTINCTROW Products.*, Products.ProductName, Products.[Option Period], Products.[Service Type] FROM Products ORDER BY Products.ProductName, Products.[Option Period], Products.[Service Type];
    This correctly allows me to select the desired product. UnitPrice and ServiceType are fields in the Products table and are needed below.
    In the After Update of the ProductID field I have:

    Add an Order and Details Macros.SetUnitPrice : AfterUpdate

    In the Item section of the Set macro, I have:

    [UnitPrice]

    and in the expression section of the Set macro I have:

    Code:
    DLookUp("[Unit Cost]","Products","ProductID = " & [ProductID])
    This correctly retrieves the [Unit Cost] from the associated product that matches the selected ProductID. This value is then correctly set to the [UnitPrice] field of the Order Details Subform which is the second of the three fields. This value is correctly displayed on the subform and is saved to the table.

    The third field is ServiceType which has a controlsource of ServiceType.
    In the After Update of the [UnitPrice] field I have:

    Add an Order and Details Macros.SetServiceType : AfterUpdate

    In the Item section of the Set macro, I have:

    [ServiceType]

    and in the expression section of the Set macro I have:

    Code:
    DLookUp("[Service Type]","Products","ProductID = " & [ProductID])
    The only difference from the previous Set macro is the [ServiceType] and [Service Type] names in place of [UnitPrice] and [Unit Cost]. This does not work. Nothing is displayed or saved and there are no error messages. [ServiceType] is a Text field and [UnitPrice] is a currency field. Not sure if it matters but I thought I would bring it up.

    From your reply:
    Note:
    DLookUp("[Service Type]","Products","ProductID = " & [ProductID]) could also be put in the DefaultValue property of the Service Type field.

    I tried DLookUp("[Service Type]","Products","ProductID = " & [ProductID]) and =(DLookUp("[Service Type]","Products","ProductID = " & [ProductID]) but received a #Error in eace new Service Type field of the Form. It only displayed while I was working on the current record.
    )

    I have uploaded a stripped-down version of the database.
    Can you see anything I am missing?

    Thanks!!
    Last edited by drewship; 09-23-09 at 11:14.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try this for the dlookup...

    =DLookUp("[Service Type]","Products","[ProductID] = " & Forms!MyFormName!ProductID & "")

    You may need to directly reference the form/field as I had described in my link and those set of threads.

    (again, see this set of posts and my responses: http://www.dbforums.com/microsoft-ac...orm-combo.html)

    I didn't notice when I copied/pasted that statement that you only had =" & [ProductID] (a typical for macros.)

    Also,
    This (for your recordsource of the subform)...
    SELECT [Order Details].*
    FROM [Order Details]
    Should probably be...
    SELECT [Order Details].*
    FROM [Order Details] Where [ProductID] = " & Forms!MyMainFormName!ProductID & ""

    (even though you can establishes a link in the properties of the subform (which you may want to check), I typically like to do this for recordsources in subforms.)
    Last edited by pkstormy; 09-22-09 at 23:34.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    May 2009
    Posts
    48
    Thanks pkstormy.

    The database I uploaded yesterday was TOO stripped down. I have uploaded a completely functional database...with the exception of the "Service Type" problem.

    I tried some of the examples on the "Refer to Form and Subform properties and controls" page but I do not understand where these examples should go. I tried them in the "Control Source" of the "ServiceType" on the "Order Details Subform", but only received various error messages in the "ServiceType" field.

    Since the both the "ProductID" combobox and the "ServiceType" field are on the "Order Details Subform", I didn't/don't think the examples apply, at least in my very limited understanding.

    I am using the DLOOKUP you provided but that does not seem to affect anything.

    I tried to combine the original query (not the stripped-down version I previously posted) with what you provided for the recordsource:

    SELECT [Order Details].*, Products.*, CCur(Nz([Quantity],0)*[UnitPrice]) AS [Extended Price]
    FROM Products INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID
    WHERE [Order Details].ProductID=" & Forms![Order Details Subform]!ProductID & ";

    I get a "Data type mismatch in criteria expression" error. If I understand it correctly, one field referenced is numeric which is being compared to a text field which is an incorrect operation. Since the only change is the addition of the WHERE statement, that is where I need to look.

    The "Order Details Subform' ProductID Control Source is the "Order Details" table ProductID. This is a numeric field, but there is a lookup row source of

    SELECT DISTINCTROW Products.*
    FROM Products
    ORDER BY Products.ProductName;

    which returns and saves the actual name of the product instead of the ProductID. I do not know if this is the issue and do not know what I would need to change in the WHERE statement to make it work. And still don't know if this would fix the actual problem.

    Going to read your posts again and search the forum some more...
    Attached Files Attached Files

  7. #7
    Join Date
    May 2009
    Posts
    48
    I have been looking at your calendar examples but I have not been able to see how your subform compares to mine with respect to how to select and display/save. I will continue my research and hope that I find something that will facilitate my understanding.

    Thanks!!

  8. #8
    Join Date
    May 2009
    Posts
    48
    This project is back at the top of my priorities so hopefully someone will have some insight on how to get this working. I have uploaded the most recent test database.
    Thanks,
    Andrew
    Attached Files Attached Files

  9. #9
    Join Date
    May 2009
    Posts
    48
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •