Results 1 to 4 of 4
  1. #1
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    62

    how to auto update values in a lookup once selected?


    Hi,
    I'm building an Invoice system for a cattle farm. It consists of the main form and a sub-form where i select the cattleID that are to be sold. The CattleID is a look-up field (whose data source is tblCattle).

    The weakness is when i've selected say CattleID '55-107', i go to the next record entry on the subform...the same cattleID appears again from the lookup field list and so it could be entered in twice! One idea was to use an update query ?? but i wouldn't know how this could be done.

    I've been reading a wee bit about subqueries...but again, i wouldn't know if i'm on wrong path? Any help on this would be much appreciated...if a solution exists.

    Thanks
    t

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The weakness is when i've selected say CattleID '55-107', i go to the next record entry on the subform...the same cattleID appears again from the lookup field list and so it could be entered in twice! One idea was to use an update query ?? but i wouldn't know how this could be done.

    I've been reading a wee bit about subqueries...but again, i wouldn't know if i'm on wrong path? Any help on this would be much appreciated...if a solution exists.
    A subquery is what you are after...partly

    I'm going to assume that an order can have many cattle but a particular cattle can only be on one order

    Let's assume that your table structure look like this:

    tblCattle
    -pkCattleID primary key, autonumber
    -txtCattleNumber

    tblOrders
    -pkOrderID primary key, autonumber
    -dteOrder (order date)
    -fkCustomerID foreign key to customer table

    tblOrderDetail
    -pkOrderDetailID primary key, autonumber
    -fkOrderID foreign key to tblOrders
    -fkCattleID foreign key to tblCattle


    You can prevent duplicates in your order detail table by specifying that the fkCattleID be unique.

    For the combo box to reflect only those cattle that were available to be ordered (i.e. not already on an existing order) you would need a subquery.

    That query would look something like this:

    SELECT tblCattle.pkCattleID, tblCattle.txtCattleNo
    FROM tblCattle
    WHERE pkCattleID not in (Select fkCattleID from tblOrderDetail);

    The problem comes in in working with the combo box in the subform. If you select one cattle in the subform and then try to add another, the combo box does not get requeried to exclude the cattle you just entered. Now if you move to another order record it will requery the combo box in the subform. But you would be right back to where you started if you attempt to add a second record via the subform.

    What I would suggest is to not add cattle via the subform but just use the subform to display the cattle that are already tied to the order. You would use an unbound combo box and a button. Behind the button would be code to append the cattle you chose in the combo box to the order. The code would then requery the selection combo box as well as the subform.

    Thinking about it, it actually might be confusing to the user to have a subform where they cannot enter data. Perhaps just a list box showing cattle tied to the order would be less confusing.

    I've attached a sample database with everything I describe above. If you look at frmOrders you will see the combo box/button and list box. I left the subform in for illustration purposes but it is not necessary and can be removed.

  3. #3
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    62
    Hi,could you post your attachment as MS Access 2003 format please? i don't have 07 (sorry...i know i should have said)

    thanks

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Sorry, I meant to check what version you had and forgot. I've attached a copy of the DB in Access 2003.

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

Similar Threads

  1. Auto fill a field from multiple lookup
    By shahemaan in forum Forms
    Replies: 4
    Last Post: 06-13-2014, 04:00 PM
  2. Replies: 7
    Last Post: 06-14-2011, 03:45 PM
  3. Replies: 0
    Last Post: 03-08-2011, 05:56 PM
  4. Auto fill field upon New Record selected
    By NOTLguy in forum Programming
    Replies: 27
    Last Post: 12-05-2010, 05:12 PM
  5. Replies: 1
    Last Post: 03-27-2010, 06:13 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