Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009

    Unanswered: Subforum Value not updating correctly with SetValue

    I have just started to use Access, and most of the problems I have encountered so far have been fairly easy for me to figure out. This one, though, I can't even think of what would be wrong.

    I have a database for a fictional company that sells Opioids. In my invoice form (frmInvoice) I have a subform (currently tblInvoiceDetail that has ProdNum, Invoice #, ProdQty and Price fields). I am trying to get the price field to populate with the price from the table tblProducts.

    Currently, I have a macro that will set the value of txtPrice (Which is linked to tblInvoiceDetail.Price) to the price in tblProducts.Price. This is using a query to look up the price:

    SELECT tblProducts.ProdPrice, tblInvoiceDetails.ProdNum
    FROM tblProducts INNER JOIN tblInvoiceDetails ON tblProducts.ProdNum = tblInvoiceDetails.ProdNum
    WHERE (((tblInvoiceDetails.ProdNum)=[Forms]![frmInvoice]![tblInvoiceDetails].[Form]![cboProdNum]));
    The interesting thing about this method is that I have to either:
    1) Select the item from the combobox, go to another record, come back to the record I'm trying to fill in, re-select the item from the combobox
    2) Have another record in tblInvoiceDetail with that product number, with the price filled in from a previous doing of 1)

    I can run the query manually and input an item number, unless there's an entry in tblInventoryDetail it will come up blank for that item. Same goes if I try to just query from tblProducts.

    I would like to know what solutions there are for making this macro work. I'm aware that this can be done with VBA, but I would like to be able to work though this problem in case I have to deal with it in the future.

    EDIT: I should also mention that the subform is a continuous form. Using Access 2k3.
    Last edited by fusionpit; 02-06-09 at 17:57.

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    I don't use macros, but the simplest way to do what you need is to include the price field in the row source of the combo box. In the after update event of the combo you could have this in code:

    Me.PriceField = Me.ComboName.Column(x)

    where x is the column containing the price (it's zero based, so the second column would be 1). I suppose you could have a macro do that as well.

Posting Permissions

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