Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20

    Lightbulb Multi Value Text Fields that allows users to key in data

    Hello – I am relatively new to MS Access and just joined this forum this evening. I apologize if this question has been asked and answered many times, but I couldn’t find it with my simple search.

    I am creating a database for change proposals (CP’s) for my company’s engineering department. Each CP could impact multiple drawings (theoretically, this could be 1 to infinity) so I would like the user to be able to enter in all of the drawings that will be impact by the CP. I don’t think I can use simple text as in the future, users need to be able to easily conduct database searches to find drawing numbers that may be on previous CP’s (and if any of those CP’s are currently open, closed, deferred, etc.).



    We have well over 100,000 drawing numbers and that number grows daily so I am thinking a lookup table won’t be feasible. Maybe some sort of multi value text field that is NOT a lookup? IS THIS POSSIBLE? IF SO, HOW?
    If anyone has a better idea, I am open to suggestions.

    Many Thanks in advance!!!!!

  2. #2
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20
    Also, if anyone is aware that this topic has been answered already, I would appreciate if you could point me to that thread....

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Don't use multi-value fields - they are (IMO) an abomination invented by MS, they violate relational database theory, and can lead to all kinds of problems later on.

    Your data structure is one-to-many, i.e. one CP affects one or more drawings. In fact, if one drawing can be affected by more than one CP (which to me seems reasonable), you have a many-to-many relationship. To implement a many-to-many relationship, you need three tables: ChangeProposals, Drawings, and a third table to join the other two.

    Welcome to the forum - feel free to ask if you need more help. Better to get things straight before you start, rather than later when you can't back up.

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    multivalue fields should be avoided wherever possible.

    One of the things that you can't do with them is index them. Which means any query where you want to find records with one or more values within the list will do a sequential search which is very slow.

  5. #5
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20
    Thanks for the reply! You are correct, we could have both situations. (1)more than 1 drawing could be impected by a CP (2)more than CP can impact a single drawing. One of my goals is to have quick and easy visibility to the situation where a user is considering writing a CP on a drawing and that same drawing is already on an open CP (in that case, we may want to modify the original CP to include this additional change proposal.

    I will give your recommendation some thought to see if I can make it work, but I am struggling a bit to visualize how it would work.

  6. #6
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20
    Thanks for the advice! Do you have any ideas how I would solve my challenge?

    One more piece of data is that I could have 2 situations (1)more than 1 drawing could be impected by a CP (2)more than CP can impact a single drawing. One of my goals is to have quick and easy visibility to the situation where a user is considering writing a CP on a drawing and that same drawing is already on an open CP (in that case, we may want to modify the original CP to include this additional change proposal.

  7. #7
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you need three tables

    tblDrawings
    DrawingPK autonumber
    DrawingName text
    ...
    ...

    tblCPs
    CPPK autonumber
    CPName text
    ...
    ...

    tblLinks
    LinkPK autonumber
    CPFK long
    DrawingFK long

    in the links table you will have many same CPFK's (which link back to CPPK in tblCPs) and many same DrawingFK's (which link back to DrawingPK in tblDrawings) e.g.

    LinkPK..CPFK..DrawingFK
    1.........1........1
    2.........1........5
    3.........1........6
    4.........3........5
    5.........3........7
    6.........4........6

    so from this table you can see that drawing 6 is affected by CPs 1 and 4, drawing 5 by CPs 1 and 3, drawing 1 by CP 1 only

    conversely you can see that CP 1 affects drawings 1,5 and 6, etc

  8. #8
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20
    Again, thanks for your time and trying to help me out. I admit, I must be slow, but I am still confused on how this works. I get the concept you explain above, and set up the exact scenario you have in your example. But my question, is how does the data in the LinkPK table get populated?

    The way I envision this working is the end user will populate a form (most likely a 2 part form). Most of the stuff on the main form will essentially go into the "tblCPs". Then it must ask them to enter all of the drawing numbers impacted by this particular CP. I think the 2nd part of the form can be used for this. But then how does the linked table get filled in and work?

  9. #9
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    how does the data in the LinkPK table get populated?
    you would populate it from a subform.

    Assuming your 'trigger' is a CP the main form would have tblCPs as the recordsource

    on the main form you would have a subform control using another form, (we'll call it sfDrawings) to display the list of drawings the CP affects.

    the subform control would have its linkchild property set to CPFK and the linkmaster property to CPPK.

    The subform itself (sfDrawings) would have tblLinks as its recordsource and would have just one control - a combobox with SQL based on tbldrawings as its rowsource. e.g.

    SELECT DrawingPK, DrawingName From tblDrawings ORDER BY DrawingName

    set the following combo properties

    bound column - 1
    number of columns - 2
    column widths - 0

    and set the form to be either continuous or datasheet

    And that is basically it.

    _____________________
    _____________________


    You haven't described your business sufficiently but if in the CP table there is a field which would limit the combo drawing list from all drawings (e.g. ProjectFK) do the following:

    the subform control would have its linkchild property set to CPFK;ProjectFK and the linkmaster property to CPPK;ProjectFK

    the combo box rowsource would become

    SELECT DrawingPK, DrawingName From tblDrawings WHERE ProjectFK=[Parent].[ProjectFK] ORDER BY DrawingName.

    and on the mainform, you would no doubt have a combobox or textbox to identify the project. If so, then in that control after update event you would put

    sfDrawings.form.DrawingPK.requery

    to refresh the list to different drawings if you change the project.

  10. #10
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20
    Ajaz - After all of your help, I think I am getting close to solving my challenge. I have tried to follow your directions as closely as possible and now have the sub-form and it works just fine as long as the drawing number is already in the table.

    However, I still need to figure out how to allow the end user to enter one manually if it does not already exist. I have tried a few things, but haven't gotten that quite yet. Do you have anymore advice?

  11. #11
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    how to allow the end user to enter one manually if it does not already exist
    the answer to this depends on how your data is set up.

    The easiest option is to have a form for entering drawings (I guess you'll need one anyway) and in the combobox properties you will see on the data tab 'list items edit form' - enter the name of your drawing form.

  12. #12
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20

    Ajax - You must be the most Knowledgeable on the planet!!

    I am really begining to feel dumb as I think I had the data set up in the way you describe. I tried to attach the DB in case you could spare a couple of more minutes to look at this, but I cannot seem to get it down to an attachment size acceptable for this forum.

  13. #13
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    do these things:

    1 remove any sensitive data
    2 compact and repair
    3 zip it

    it's 3am here so signing off for the night, can respond sometime tomorrow

  14. #14
    dhaugland is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2015
    Location
    North Dakota
    Posts
    20
    Yes, i wish it were that simple....... I am working on my company laptop and it is locked down so tight that I cannot get any ZIP software. They even have all of my USB ports shut off........ However, I was able to email to my wife and she was able to upload to dropbox.com........ Hopefully, you will be able to access it at the following link.

    https://www.dropbox.com/s/6s4vbbed2l...2-1.accdb?dl=0


    Again, thanks again for all of your help!!! If this actually works, I will definately owe you.

  15. #15
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I'm surprised your company does not have any zipping facility - what if you right click on the file and select send to - do you not have a compressed folder option?

    See attached - it is a zipped file, you should be able to click on it, select open, then drag the file to your desktop.

    CP Log Rev z2-1.zip

    I've created a form based on your drawings table and added the form name per


    you will see on the data tab 'list items edit form' - enter the name of your drawing form
    your main form needs some tidying and I've moved and added a bit of automation to resize the subform so it fits on the screen.

    the new form for tables also has a bit of automation to switch between adding new drawings and updating existing ones

    to utilise, on the main form, try to add a new drawing number that does not exist - you are prompted to see if you want to add one. If you do, the drawing form opens in data entry mode, you enter the details and close the form - now the new drawing can be selected.

    Your drawing table - you have revision number as required - what do you enter for the first drawing (i.e. no revision?). You also have a calculated field - which I do not recommend - google 'access calculated fields' for reasons why.

    Finally looks like you need to do some more thinking around your process - the way you have it set up, a cp is linked to a specific drawing and revision number. What happens if you create a later revision? That link, although still there will not be linked to the later revision - this may not matter (i.e. current revision=previous revision amended by CP). Anyway, for you to think about

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multi users and Split Database
    By cohrsman in forum Access
    Replies: 4
    Last Post: 08-01-2013, 09:13 AM
  2. Replies: 2
    Last Post: 08-31-2012, 02:20 PM
  3. Multi users login form
    By johnwatkins35 in forum Forms
    Replies: 3
    Last Post: 05-15-2012, 11:39 PM
  4. Multi Users
    By brobb56 in forum Access
    Replies: 1
    Last Post: 09-20-2011, 04:07 PM
  5. Multi Users on shared drive
    By wallen in forum Access
    Replies: 0
    Last Post: 08-25-2008, 08:57 AM

Tags for this Thread

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