Results 1 to 4 of 4
  1. #1
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44

    Using a value from an table not bound to form


    I have a table that tracks items provided to clients of a social service agency. There are fields for each of the items that potentially could be provided with a corresponding field for the amount spent. Since one family can receive more than one of a particular item, the fields for the items contain the number of items provided. For example, if a field is called Beds, it contains a number of beds purchased for a family. The corresponding field BedCost contains the amount spent (the number of beds times the price). Since the amount allocated to be spent for a bed changes, I have a separate table called tblItemsH containing only one row with a field for the current cost for each item, e.g. there is a field called BedPrice, another called CribPrice, etc.

    I’m using a form for data entry—actually it’s a subform, since the main form contains other case information from a related table (one-to-one relationship). What I want is that when the user enters the number of items (say, 2) in the txtBeds textbox, the amount spent text box, txtBedCost, will populate with amount spent, the number purchased multiplied by the price for that item from the BedPrice field in the tblItemsH table.

    I tried it a number of different ways, but I can’t get it to work. The simplest way seems to be an After Update event in the txtBeds text box that populates the txtBedCost text box with the contents of the BedPrice field from tblItemsH and multiples it by the number of items from txtBeds, but I can’t get the code right.

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    If I've followed your scenario correctly, this should do it:
    Code:
    Private Sub txtBeds_AfterUpdate()
     If Nz(Me.txtBeds, "") <> "" Then
      Me.txtBedCost = DLookup("BedPrice", "tblItemsH") * Me.txtBeds
     End If
    End Sub

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    hilian is offline Advanced beginner
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    44
    I used the code you provided, and it works.

    Many Thanks,

    Henry

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Glad we could help!

    Good luck with your project!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using bound form to access last record
    By ismith in forum Forms
    Replies: 1
    Last Post: 12-20-2011, 10:36 AM
  2. Bound form with bound combobox
    By Jerry8989 in forum Access
    Replies: 2
    Last Post: 12-05-2011, 01:50 PM
  3. Combo Box non bound output to table
    By burnsey in forum Forms
    Replies: 3
    Last Post: 04-26-2011, 07:29 AM
  4. Replies: 0
    Last Post: 05-09-2010, 08:43 AM
  5. Bound a unbound text box to the table
    By Brian62 in forum Forms
    Replies: 1
    Last Post: 11-06-2009, 11:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums