Results 1 to 3 of 3
  1. #1
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22

    help with form procedure based on related value in table

    I have a form frmNewEnterAddress, that populates tblAddresses with a yes/no field called DefaultAddress. How do i restrict the addition of further address records, with the same ContactID, from being able to select 'yes' when that ContactID already has a record with a default address set to 'yes'.



    I have a combo box lookup for "ContactName" frm tblContacts, and though it's likely that all contacts already have a 'DefaultAddress', some may not. I thought some procedure on the selection of the combobox contactiD could trigger an event, such as making the yes/no control unavailable (don't know the term) and maybe a msg "This contact already has a default address, do you wish to change it to this new address" then what ever needs to happen in either case.. would appreciate any other suggestions - also, really don't know how to approach writing the procedure other than thinking it starts with and IF statement

    Thanks for your time..
    Last edited by 123seven; 04-11-2011 at 07:01 AM. Reason: wrong title

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you need to record the old address for any reason? Could a contact have more than one address?

    If so, then you may wish to have a separate Address Table.
    (And possibly an AddressType table).

    In some business applications, it is necessary to record
    -- physical location address
    -- mailing address
    -- shipping address
    -- billing address
    ......

    A separate address table would contain the
    ContactId as a FK to the Contact Table.

  3. #3
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22
    Yes there are multiple address for some contacts, as some of the contacts are client/contractors whom are billed to but orders are shipped directly to their clients. All addresses (our clients or our client's clients) are stored in tblAddresses. ContactID does exist in this table as FK.

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

Similar Threads

  1. Add Record based on related value
    By top1hat19 in forum Access
    Replies: 0
    Last Post: 03-08-2011, 12:45 PM
  2. VBA public procedure in a form
    By gg80 in forum Programming
    Replies: 3
    Last Post: 09-12-2010, 04:55 AM
  3. Replies: 1
    Last Post: 04-13-2010, 12:18 PM
  4. Pass a form property to a procedure
    By trb5016 in forum Programming
    Replies: 2
    Last Post: 02-17-2010, 04:15 PM
  5. stored procedure return value to access form
    By rbw940 in forum Programming
    Replies: 0
    Last Post: 10-13-2008, 01:31 PM

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