Results 1 to 13 of 13
  1. #1
    makivelli is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5

    Plus Sign Button to Add Additional Text Boxes

    Good Day All,



    The image below is a input form I am working on. I talked with the department that will be using it and they said they would like the option to add multiple defendants (that's the First and Last name fields). I don't know that name of the option I am thinking of but its the clickable plus signs you see on some things you fill out online. I believe I last saw one when I was filing out my benefits and got to the part where it asked to add dependents.

    Anyway, I guess I would need the form to make a new record for each defendant upon clicking the submit new record button.

    Click image for larger version. 

Name:	Plus Sign.PNG 
Views:	50 
Size:	22.7 KB 
ID:	35459



    Thanks for any and all help,

    Joe

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can we see your table structure?

    Looks like you currently have 1 defendant's first and last names as fields in your case table?
    I think you'll want to break out the defendants from the case table to create the one to many relationship (one case has many defendants).

    (Also, the form's title vs context of data input fields is confusing.. but that's just me)

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Hi Joe,

    As KD suggests it would be helpful to see your table structure as well as an idea of the purpose of the DB.

    My main DB tracks all aspects of criminal cases in our office. I do criminal defense investigations. Just guessing from your form that your on the other side of the table.

    I would suggest that you put a lot of effort into your table designs and relationships before delving into building forms,etc.
    Most of my cases go through many different phases over the months or years they are pending. They almost all begin in the District Courts and wind up in Superior Courts. There is often different judges involved along the way as well as changes in prosecutors and attorneys. Charges are sometimes dropped or dismissed or elevated or added.

    Some food for thought as far as tracking participants in a case and designing your tables.
    I assume you want to store more information on a participant other than just their first and last name. Other critical information would include their address, phone number, DOB, SS#,etc.
    Good DB design would dictate that that information on a person is stored once and not duplicated. Over the course of a case a persons address and phone number often changes therefore that data may need to be in separate tables to maintain a history.

    We need to avoid conflicts of interest (such as a past client being a victim or witness with a new client) so we need to be able to see if anyone was ever involved in a past or pending case. Because of this, I have to go to great lengths to ensure that a person being entered into the DB has not been entered in the past. I do this by running a procedure to look for exact matches on first and last names as well as soundex matches and levenschtein distances to account for spelling mistakes (Shawn vs. Shaun or Smith vs. Smyth)

    I could go on forever but i'm not sure it's relevant to your situation so i'll leave it here. HTH.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As others have said ---we need to see your tables and relationships. But I also think you should give readers some detailed requirements. Adjusting/modifying a control(s) on a form is part of user interface, but it seems this goes deeper into business requirements. Better to build a model based on all requirements, then test/vet that model before getting into physical database.
    Legal cases often involve multiple victims, multiple witnesses, multiple layers etc.
    It is often a good practice for context setting to show a few typical "cases" with test data to give some indication of the types of data values that are "typical"/"representative" of the "business.

    This may be overkill or off topic, but there are a number of free data models at Barry Williams' site.
    Here is a link to some related to Law Enforcement. It may help with some table and relationship questions.
    Good luck with your project.
    Last edited by orange; 09-13-2018 at 06:41 AM. Reason: spelling

  5. #5
    makivelli is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5
    Thank you all for the replies!

    @kd2017

    I will attach a screenshot of what I think is the "table structure" you are looking for. If it is not just let me know and I will get what you need. I have also attached a screenshot of the relationships.

    I agree about the title vs. context but I have no control over the title unfortunately.



    @moke123

    I am still relatively new to access but am learning fast. This is the first one I have attempted alone and am realizing that I should do as you suggested and put more effort into the table designs and relationships.

    I was asked to create this database for the three people who interact with the victims and witnesses of cases so they can enter the case data and run a report at the end of the week to show what cases have a court data the following week so they can attend the hearing. I posted about the report in a previous thread but realized there was way more work to be done before I can continue with that.

    I am indeed on the other side of the table. I also have very little experience working in this field of litigation support tech. So, I don't know a lot about the court process. But I am learning. I would assume a lot of those changes go on as well. I have had several meetings with the people I am making this for and have asked a lot of questions about what they want from this but there was no mention of other critical information. I can only hope they don't decide to add it later. They can update current entries (change of Judge, court location, etc) from this input form if needed.



    @orange

    The people I am working to make this database for have provided all the fields they work with. They have been using an excel spreadsheet for a long time but would now like to do more with the data.

    Thank you very much for those links!




    Click image for larger version. 

Name:	Listing_Table.jpg 
Views:	35 
Size:	102.2 KB 
ID:	35465
    Click image for larger version. 

Name:	Listing_Table_Continued.PNG 
Views:	34 
Size:	33.1 KB 
ID:	35467

    Click image for larger version. 

Name:	Tables.PNG 
Views:	34 
Size:	3.9 KB 
ID:	35468

    Click image for larger version. 

Name:	Relationships.PNG 
Views:	33 
Size:	26.9 KB 
ID:	35466

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Per the original question to achieve having many people associated with one VW_listing you will need to create a new table for these people, and another table to link the person with the listing. This is called a many-to-many relationship, I suggest googling many-to-many relationships and studying up on the concept if it's a little fuzzy.

    I have the following design change suggestions:
    Click image for larger version. 

Name:	Untitled123.png 
Views:	33 
Size:	53.5 KB 
ID:	35469

    Can one VW_Listing have multiple charges? If so you would need a many-to-many relationship between those two tables as well.

    (I enjoyed your test data )

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Ah, makes a lot more sense to me now. Victim witness advocates on the Fed side. Have some friends in the Federal Defenders Office.

    I can only hope they don't decide to add it later.
    famous last words! better plan for it.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    I took the suggestions by kd2017 and added a little bit if my style.... mostly my naming convention.
    A table that has a prefix of "jnct" is a junction table (many-to-many). These tables have compound indexes set to prevent duplicates (as indicated by the red boxes). I added a junction table for multiple charges per docket. (hope I said that right)

    Click image for larger version. 

Name:	Presentation1.png 
Views:	25 
Size:	144.1 KB 
ID:	35480

    Any field with a suffix of "ID_PK" is an autonumber field that is the PK. The foreign key fields have the suffix of "ID_FK' (of type Long Integer).

    I'm at a stand still waiting on data, soooo I was bored....
    Attached Files Attached Files

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    May I suggest you split out the agency to a junction table with a corresponding look up table. I would imagine that you would have more that one agency involved in a case.
    I would also add a FK to the jnctListingpeople table to indicate their role - (Victim, witness, LEO,etc) with a corresponding look up table.
    you may also delete the Number of victims field as that can be computed on the fly.
    Another possibility is a fk in events jtbl to indicate whether it was held or continued to another date.

    edit: just noticed
    These tables have compound indexes set to prevent duplicates (as indicated by the red boxes)
    Its possible to be charged with numerous counts of the same crime so limiting jnctChargevwlist may need tweaking (ie. kidnap a family of four would result in four counts of kidnapping)

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Just another thought to keep in the back of your mind...

    Take a look at Albert Kallal's super easy word merge http://www.kallal.ca/msaccess/msaccess.html

    I would assume your VW advocates need to correspond with the witnesses, etc.
    My users love this feature in my database as they can generate subpeonas, letters, motions, etc. with the click of a few buttons.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @moke123,
    Excellent points!
    Never occurred to me about "charged with numerous counts of the same crime".

  12. #12
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    I agree, delete the compound unique index in jnctChargeVWList. Kidnapping a family of four, four charges, would be four entries in that table. That means you probably need to store more information in it, because otherwise it would be just four rows with an autonumber 1,2,3,4 and the same USAO_Num_ID_FK and ChargeID_FK on all four rows.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @makivelli

    I really recommend you consider the advice given so far and make a model of the probable tables and relationships.
    Then take some representative "cases" (test data) and some test scenarios and convince yourself that the model can successfully handle the "test cases" and give you access to required info/data for queries/reports etc.
    We used to play a game "stump the model" when vetting the data model before developing the physical database.
    It will save you time.


    Good luck.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-03-2018, 11:12 PM
  2. Open Google Map Using Button based on Values from 2 Text Boxes
    By pjordan@drcog.org in forum Programming
    Replies: 3
    Last Post: 06-21-2017, 11:24 AM
  3. Replies: 8
    Last Post: 11-21-2014, 04:39 PM
  4. How to create a Sign out button in access
    By uronmapu in forum Access
    Replies: 12
    Last Post: 07-12-2012, 07:34 PM
  5. Populate text boxes with a command button
    By Brian62 in forum Forms
    Replies: 3
    Last Post: 09-30-2011, 12:52 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