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

    Question Need Form EP to display txt data based on input, and also write PK data to form's table

    Hi there,
    Hope that title wasn't too confusing. I really need help in writing an event procedure for the following form scenario:

    On frmAddresses, after a postal code has been entered for a new address, I would like a field called 'Deliverylocation' to display the appropriate 'deliverylocation' Name, already assigned to that postal code, and write the 'deliverylocation' PK to the appropriate integer field in tblAddresses.

    I have a table with 6000 postal codes and their appropriate deliverylocation IDs:
    tblDelLocPostals

    • DelLocPosID - unique integer
    • PostCode - text
    • DelLoc - integer

      • tblDelLoc - Delivery locations: Id and name (about 25 recs)

        • DelLocID - unique integer
        • DelLocName - text





    I think I need an 'After Update' event procedure, on the postal code field (on frmAdresses), but I have no idea how to go about writing it. I just need the DelLoc 'NAME', to become visible to the inputter and, the delLocID to be written to tblAddresses... think I've said that twice..

    Any guidance would be greatly appreciated.

    Cheers,
    Shannon

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The qry of the Combo box would have the postal code, ID.
    Afterupdate will load the ID into the othe field by
    txtID = cboZip.column(1)

    The ID & the zip were picked by the user, so all values are available...just be sure the combo properties have
    2 columns
    col widths= 2;0. (The user doesn't need to see the IDs.)


    ( if I may add, I see no reason to key zip codes with a number field. Zip codes are their own key. Unless you're doing special things with multiple IDs for 1 zip)

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you describe, in plain English, what you are trying to accomplish? An example would also help clarify your post.

    I'm not following the purpose of DelLoc.

    What exactly is address in your set up?
    Some common address types are:
    - physical location
    - mailing
    - shipping/receiving
    - billing
    - HQ/corporate

    There is a complete guide to Canadian Addressing here.
    Just curious as to where you obtained 6000 postal codes, and how you keep them current.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You're missing a foreign key FROM tblDelLoc TO tblDelLocPostals

    This is assuming for any given postal code you have several different locations to deliver an item to

    Code:
    tblDelLocPostals
    DelLocPostal_ID
    PostCode
    
    tblDelLoc
    DelLoc_ID
    DelLocPostal_ID
    DelLocName
    in other words you have a ONE to MANY relationship between tblDelLocPostals TO tblDelLoc

  5. #5
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22
    Hello Guys,
    Thank you for taking time to reply.

    I'll start by saying I'm a 'Red Green' (novice) programmer, and if you're not from Canada, you probably won't know what that means.. think duc tape...that's all I got.
    I'm going to start with a reply to Orange's post, though also answering Ranman's questions/tips, and probably will bore you with too much detail, but here goes..

    Our family business sells/delivers seasonal gardening products to consumers. We achieve and deliver 80% of our sales in 3 months. Data entry needs to be swift! This is year five of the db that keeps track of all those customers and orders. (I was here with my first db questions year one!) We meet 40% of our clients at consumer shows at the beginning of the year, past clients and new alike. Our db allows for one form to enter new contacts (until they become customers) into tblContacts, tblAddresses, tblShowDetails, tblCommunications .

    tblAddresses is all those delivery addresses, with 10% being billing-only or secondary/multiple delivery addresses (for same contact). The table had just requisite delivery info including 'Postal Code', entered each time manually just like address1, address2 etc.

    Now that we have 6000+ postal codes, from all those contacts (and have mapped them overtime to know they exist!), we opted last year to break them (postal codes) into DeliveryLocations, so as to better help with offering delivery dates. Way easier than dealing with towns/cities etc as a means of dividing our delivery territory. (Orange.. I don't know HOW we're going to maintain them, insofar as they change, new ones get added etc.. that's a journey in itself).

    I arbitrarily cut our delivery area into 25+/- 'locations' and assigned their names to tblDelLoc, 3 fields - pk-auto#, 'name' and description. With the help of postal code maps, I allocated all 6000 postal codes to their respective locations... which become tblDelLocPostals. The second table contains those two fields (postal code and fk_DelLoc) plus a pk-auto number field. (That hopefully will clear up any confusion for rpeare) Again like Ranman said .. PK..maybe not needed, and I thought that too, but have also read several articles on pk or no pk in that type of sitch.."you never know if it'll come in handy... so just do it", (lol don't google that .. I'm paraphrasing). If someone can tell me why it's a nuisance, I can dump it tomorrow... no affection for the field.

    So anyway, a new field 'DeliveryLocation' was then created/added into tblAddresses, to hold the FK from tblDelLoc. I haven't really done anything with tblDelLocPostals yet, accept that it allowed me to populate tblAddresses in an update query.

    So currently frmContacts (data-entry form for new customers who phone-in or the consumer-show version) still asks to input a postal code manually, to tblAddress.. it doesn't as RanMan inferred, get selected from a cb of Postal codes, though that may be the next step.. (more on that in a min, only cuz I've read this post twice..).

    So my deal now is, when entering a new contact, we currently have to arbitrarily assign a 'deliveryLocation' from cbDelLoc and hope that we didn't pick the wrong one, based on the address; but a new person doing data-entry would not necessarily know that. 10,000+ plus contacts/customers in tblContacts already have the correct 'DeliveryLocation' in their address info, it's just new entries that will be a challenge.

    So that's why I thought if the postal code field was entered (manually), it could immediately trigger an event procedure that would write the correct delLoc to tbladdresses for that contact.

    The error side with this occurs when there's a postalcode that doesn't exist in tblDelLocPostals.. but that's a problem for another day.... L 'please' OL.

    My reference above to Ranman, and the whole cb thing, is that maybe I could just replace the postalcode field on the frmContacts with a CBox as he described (2 fields, postal code and FK from tblDelivery Locations ... but I still need to know how to deal with that FK in code, to get it into tblAddresses... I don't know code.. I can copy/paste it, sometimes understand it .. but never write it from scratch by myself.. humble limitations.

    Anybody that made it this far, hopefully you three aforementioned guys... sincere thanks.
    I'll be around for a while.

    cheers
    shannon

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I arbitrarily cut our delivery area into 25+/- 'locations' and assigned their names to tblDelLoc, 3 fields - pk-auto#, 'name' and description. With the help of postal code maps, I allocated all 6000 postal codes to their respective locations... which become tblDelLocPostals.
    Can you give a few specific examples of data in your tables?
    I've attached a jpg of some info re PostalCodes for Aurora, ON.

    I have worked with Canadian Postal Codes in various applications usually dealing with all of Canada. There are new codes, modified codes, deleted codes etc. We had contract with Canada Post and as I recall a new postal code file was available every 3-4 months.
    Attached Thumbnails Attached Thumbnails aurorapoatal.jpg  

  7. #7
    123seven is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2011
    Location
    Aurora, ON Canada
    Posts
    22
    Here you go, Orange.. results of a query to show you town names/id as well, for additional info


    Click image for larger version. 

Name:	DeliveryLocations.jpg 
Views:	9 
Size:	141.7 KB 
ID:	20242

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

Similar Threads

  1. Replies: 3
    Last Post: 03-27-2014, 05:05 PM
  2. Replies: 7
    Last Post: 05-08-2013, 11:16 AM
  3. View Data based on Form Input?
    By 0REDSOX7 in forum Forms
    Replies: 7
    Last Post: 11-29-2011, 10:25 AM
  4. Replies: 2
    Last Post: 06-17-2010, 04:15 PM
  5. Replies: 1
    Last Post: 06-14-2010, 02:31 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