Results 1 to 6 of 6
  1. #1
    jfn15 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    26

    Selective search function for many to many relationship

    I have 3 tables that are related on a many to many basis. I deal with large organisations, and within those, with individuals. Each client must nominate 3 address types - postal, residential and business. Often, clients share an address (usually business, but not always). Therefore, one address can have more than one client associated with it.
    My CLIENTS table and ADDRESS table have many records (all this is coming from an excel sheet). I know how to create a main form (which needs to be based on clients), and how to add a subform based on the junction table. My problem though is one of data entry. I've been requested to set up a search form (I think I can handle that part), but the user needs to be able to open the search form, ascertain whether an address exists - if does, then add it for the relevant client. If an address does not exist it needs to be added, and then the AddressID passed back to the client (or rather to the junction table, with the correct AddressID and ClientID).

    I'm wondering how best to handle this using VB as I don't have any programming experience. Ideally, I'd like the user to be able to specifiy what type of address they are adding, before selecting or adding that address. So perhaps a button to 'add a postal address' or 'add a business address'? Or is there a better way, say a combo box that lists the three address types so the user could select an address type, search for it (or add it), and then pass the relevant values to the underlying junction table.

    I'm fairly new to Access so I hope my questions don't sound too idiotic!

    Is anyone able to assist me here? Many thanks!

  2. #2
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Unless you're dealing with major corporations (where duplicate addresses will proliferate), and maybe even then, I'd ignore the fact that multiple clients have the same address. Otherwise, you can end up with changes to one client's address affecting a different client.

    "Look, I need you to add my suite number to my address. My stuff keeps getting delivered to the wrong floor."
    "Who changed my address?"

    Thus, I'd change my thinking of "Address" to "clientAddress". You still probably have a junction table with address type on the junction record, because the same client address may be used for different functions.

  3. #3
    jfn15 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    26
    Hi Dal, thanks for your reply.

    Your suggested is how I structured it when I was first directed to create this database. However this was not acceptable and so I must now handle this by keeping 'people' and 'places' in separate tables. As this presents a many-to-many relationship, I need to know how to enable the admin. officer to easily enter each address (and it's type) against each person. The direction I have is - a person's details are entered before anything else, and then the postal and residential address (with the the address type), and if applicable, also the business address. As many of the addresses (even for new clients) are already stored in the system, we need to allow the user to first find and select a postal, residential or business address if it exists. If not, then we need to add that address and have it then available to be chosen as either a postal, residential or business address for a specified person. Unfortunately I can't load the database onto a public forum, but if you could see the type of information we deal with, and the rather complicated work processes we are required to follow, I think you'd see their point. I have no wriggle room in this.

  4. #4
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Can you give me more on "This was not acceptable"? What issues did you encounter? I'm assuming from your wording that you just have a huge database.

    The most likely steps (most of which you have already done.)

    1) Define an Address table
    2) Define a Person table
    3) Define an Address Type table
    4) Define a linkage table (THIS person has THAT address and it is THIS type of address).
    5) Determine an address lookup function/interface

    I would suggest that you create a hash index of some sort to use to find the address. For instance, (assuming primarily US addresses) street number, zip-5, first four characters of street name.

    Have the user enter the street address and zip first, then do a match. There shouldn't be too many copies of 12345 Main St in Zip Code 75211 to choose from. Click one to autofill the remaining fields, or continue typing to complete the entry of a new one. The difference in emphasis here is that the user isn't "searching", the user is entering two fields and then glancing at the prospective matches before proceeding with the remainder of the fields.

    Your address fields would have to be organized correctly for this - any c/o: in the first line or attn: in the last line would have to be on the Linkage record, not the address record. Any "Baxter Building" or "Service Center" in the first line would have to be ignored for hashing purposes. And your user is going to have to have the ability to override the match if she sees something that you really don't want to do.

    IMHO, you'll also have to lock down each true address record - as opposed to linkage record - from update if there is more than one unique person/entity attached to the address.

  5. #5
    jfn15 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    26
    Sorry - should have been more descriptive - there will be a great many 'addresses' in the database, as we have to be very, very specific (right down to room number, building level etc.). So, we don't want to have a specific address in more than one location. I've since browsed various web pages and found this code that enables me to open the search form and select an address (don't really understand it as I'm a complete novice at coding, but it seems to work to a degree). However it doesn't address the issue of assigning an address type to an address before saving that addressID against a ClientID. Thus my question - is there a simple way to allow the user to specify "I want to find an address in the search form, and select it as an address type, for this specific client"? Or perhaps this is something I am just too inexperienced to property accomplish?

    The code I found is as follows:

    If Me.Dirty Then Me.Dirty = False
    DoCmd.OpenForm "frmSearchAddress", , , , , acDialog, Me!ClientID

    'Copy ClientID and AddressID to the tblClientAddress junction table
    CurrentDb.Execute "INSERT INTO tblClientAddress ( ClientID, AddressID ) " _
    & "VALUES ( " & Me.OpenArgs & ", " & Me!subfrmSearchAddress.Form!AddressID & " );", dbFailOnError


    I've looked up the Me.Dirty property - I think I understand that (curious description though?) I think I also get the 'insert into' part, but I'm still trying to get my head around OpenArgs and what effect it has on the above code.

    By the way, I do thank you for taking time to offer assistance. It's most appreciated.

  6. #6
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Me.Dirty = True just means that something has been marked as "changed". If you set "Me.Dirty = False", that forces Access to save all the changes, if any.

    So, you are forcing a save before popping the frmSearchAddress form in Dialog mode.

    I'd look at the design of your tblClientAddress junction table. That's where I would put the "what type of address is this" flag, as a third field.

    I don't see why the INSERT switches back to using Me.OpenArgs for the insert, when Me!ClientID is right there in the code just above, and available, and would be more readable (what I call "self documenting").

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

Similar Threads

  1. Combo box search function
    By Carouser in forum Forms
    Replies: 10
    Last Post: 08-13-2012, 10:21 PM
  2. Replies: 3
    Last Post: 04-28-2012, 10:12 PM
  3. Search Function
    By sk88 in forum Access
    Replies: 2
    Last Post: 01-05-2012, 12:33 PM
  4. Creating a search function
    By jlclark4 in forum Forms
    Replies: 7
    Last Post: 12-30-2010, 02:03 PM
  5. search function
    By Eric1066 in forum Access
    Replies: 0
    Last Post: 10-23-2009, 10:23 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