Results 1 to 14 of 14
  1. #1
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9

    form of data entry, need linked subitems - how to?

    I am an access newbie



    I have volunteered to work on a database for a local police force

    This very plain database has a form to submit an "incident report" with a bunch of fields. They have been manually adding "vehicles (VIN, color, model etc)" in a comment section.

    I think they are better off having a separate table of "vehicles" to link vehicles to each incident report. Some incident reports might have many vehicles, or only 1, or none.

    I have created a separate table for "vehicles" with all necessary fields, and entered some random data.

    I want to open my "Incident Report" form and simply attach a vehicle from my vehicles table, or add a new vehicle.

    In the end I want to browse my incident reports, and see all the linked vehicles for each report.

    I cannot seem to get my "Incident Report" form to easily enter a vehicle that saves and is attaches to each incident report.

    I tried creating a subform but it doesnt save the data, and is always blank, when I return to that incident report.

    I tried another subform linking to the "vehicles" table, and somehow ended up getting this error about invalid expression too complex....

    I feel like this is easy, but I just dont know the right path.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the db with some dummy data.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    What relationship do you want - each incident can have many vehicles and each vehicle can associate with multiple incidents? That is a many-to-many relationship and requires at least 3 tables.

    tblIncidents

    tblVehicles

    tblIncidentVehicles

    Build a form/subform arrangement with Master/Child Link properties of subform container set. Main form bound to tblIncidents and subform bound to tblIncidentVehicles with combobox to select vehicle from tblVehicles. Use combobox NotInList event to add new vehicles 'on the fly' during data entry.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    Can you describe what tblIncidentVehicles should look like?

  5. #5
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    My incident tbl (PD IR) has 15 fields.

    My vehicle tbl just has test data in it, typical fields for vehicle attributes.

  6. #6
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    My primary form which populates the Incidents table(and bound to PD IR tbl). I am not sure how to create the 3rd table "IncidentsVehicles". Googling this retrieves so many basics, I cannot find relevant answers.

    I have a section of white space in this form for my "Vehicle" list/addition.

    How do I create "subform bound to tblIncidentVehicles" (table and form)?

    I think I understand the combo box logic, but without the subform and tblIncidentVehicles, and I cant really try it out....

  7. #7
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    Click image for larger version. 

Name:	PD IR-incidents.jpg 
Views:	12 
Size:	13.7 KB 
ID:	46254

    Click image for larger version. 

Name:	vehicles.JPG 
Views:	12 
Size:	81.5 KB 
ID:	46255

    My 2 tables.....

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    You have Duplication of data in this table

    Registration: 234234 is in Twice
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    the same license place, can get moved to a new/different vehicle.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    I am sure that is illegal. A licence plate belongs to 1 vehicle.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    Any DMV will allow you to move your registration plate to a different vehicle.....hence you can end up with same registration plate on different vehicles. I got your private msg, but i have to scrub data before i can send anywhere.....working on it.

  12. #12
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by mike60smart View Post
    I am sure that is illegal. A licence plate belongs to 1 vehicle.
    One vehicle at a time perhaps, unless it's a transporter plate or dealer plate.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  13. #13
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    Vehicle 1 gets in accident with vehicle 2.

    Incident report and vehicles table now has plate 1 and plate 2, tied to vehicle 1 and 2 respectively.

    Vehicle 1 destroyed, can have its plate re-registered to vehicle 3.

    Vehicle 3 gets in an accident

    Now vehicle 1 and vehicle 3 have same registration plate in database.

    Vehicle 1 is not removed because it is used for investigation of historical data.

  14. #14
    ragedude is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    9
    The same vehicle could be entered 100 times, if it has any data that has changed, like plate, owner...etc. it would only be added as same vehicle for an incident, if nothing has changed....owner..plate..etc

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

Similar Threads

  1. Replies: 2
    Last Post: 12-11-2015, 02:55 PM
  2. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  3. Replies: 3
    Last Post: 02-11-2014, 03:26 PM
  4. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  5. Replies: 1
    Last Post: 05-17-2012, 05:02 PM

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