ACCESS FEED: Re: [MS_AccessPros] option group error message

Hey John! I was able to work out a couple problems my form was having but I'm still stuck on some others.

1. I have LawIncidentID as a primary key for that table. I've managed to get it to autofill the other boxes related to the selected LawIncidentID, however, it won't allow me to add additional IncidentReports using the LawIncidentID I choose. I get a message "The changes you requested to the table were not successful because they would create duplicate values int he index, primary key, or relationship. ..." . I originally had the LawIncidents as the control source for my main form so I tried changing it to a subform on a main form with IncidentReports as the main table - same problem. What's my problem here?

2. I still can't get my ControlTypeID to determine my ControlWeapon in the subform. I can get it to work on the main frmReportSubjControl but when I try to adapt it for the subform I continue to get the request to enter parameter values. I use [Forms]![frmReportSubjControl].[Form]![cboControlType] to make it work on frmReportSubjControl. I tried [Forms]![frmUseofForceMain]![frmReportSubjControl].[Form]![cboControlType] to get it to work as a subform but I've had no luck.

Thanks again for all of your help!

Jessica

On Wednesday, October 15, 2014 3:09 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

The combo box to do the search must not be bound (have no Control Source). You can use the Control Wizard to set up the search, and it will generate the code to find the record selected for you.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 15, 2014, at 9:35 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I created the combo box that shows the existing LawIncidentID's and still allows the entry of a new LawIncidentID. Is there an Event that needs to be set up for the LawIncidentID control or for all of the other controls from that Table in order for the data from that LawIncident record to autofill the remaining fields?

As for the other problem, I have frmUseofForceMain with a subform frmIncident. frmIncident also has subforms in a tab control (Incident) including frmReportSubjControl (tab name Level of Control). I assume I have to mention all of this somehow in the wording in the Expression Builder. When I write the Expression for frmReportSubjControl I use: [Forms]![frmReportSubjControl].[Form]![cboControlType] and it works exactly the way I want.

Thank you.

Jessica

On Wednesday, October 15, 2014 12:00 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

Yes, use the combo box wizard to do a search on LawIncidentID.

When it's a subform, you can't access it as part of the Forms collection. It'll be something like:

Forms!NameofMainForm!NameOfSubformControl.Form!cboControlType

Note that it's the name of the SUBFORM control, which might not be the same as the form inside it.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 15, 2014, at 6:05 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Can that search feature for LawIncidentID be built into the form? A Query builder type thing?

The prompt says Enter Parameter Value then Forms!frmReportSubjControl!cboControlType . This is what I entered in the Query Builder on frmReportSubjControl along with a macro to Requery based on ControlTypeID. It all works fine in the actual form but not when it's a subform.

Jessica

On Wednesday, October 15, 2014 10:36 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

If another officer needs to create a report on an existing Incident, you need some sort of search feature to get the officer to the existing record to be able to add a new Report record.

You shouldn't get any parameter prompts. What is it asking for in the prompt?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 15, 2014, at 5:22 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John, I left my forms separate (the continuous form feature didn't let me do what I thought I wanted to do but this will work just fine). I was able to build the ReportSubjControl form so that my choice in ControlTypeID determined the choices available in ControlWeapon. It worked great until I made frmReportSubjControl a subform to my frmUseofForceMain. When I use the subform I get the pop-up "Enter Parameter Value". Is this an easy fix?

One other question: How do I make the form auto fill the basic Incident information if the LawIncidentID has been used previously? I set LawIncidentID as the PK iun my Incidents table - this shows the LawIncidentID (matches the Law Incident number on the actual police report for that case), Nature, IncidentDateTime, IncNoOfficers, IncNoSubjects. These should all be the same regardless of which officer is filling in the form for that incident. If I try to reuse the LawIncidentID now, I get a message that it's already been used.

Thanks!

Jessica

On Monday, October 13, 2014 2:57 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

If you want to relate the control applied to a specific resistance, then you need to link ReportSubjControl to ReportSubjResistance - replace the ReportID in ReportSubjControl with ReportSubjResistanceID. That will put Control as a subform to Resistance and not on a separate tab. Choosing the ControlType would still be in ReportSubjControl. The code would look at what ControlTypeID was set and show / hide appropriate controls on the ReportSubjControl subform based on the input required for that Control Type. It's a simple matter of checking the value and setting the Visible property of controls to True or False.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 13, 2014, at 9:36 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thank you John. I'm starting to understand what you are doing.

My next question would be, do you think I could combine resistance and control into one? So my ReportSubjResistance table would get a new field for ControlTypeID (FK) to go next to the ResistanceID selected. The user could choose the resistance displayed, explain the resistance displayed then choose the control type they used.

If that works, is it then possible for the next combo box to automatically switch to whichever value list applies to the ControlType selected? For example, if MACH Hold is selected in ControlTypeID the list of the 5 different MACH Holds would be available for selection (based on the MACHHolds table)? I imagine that if this is possible it's a programming nightmare for the likes of me.

Thanks again.

Jessica

On Saturday, October 11, 2014 4:08 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

I uploaded a modified copy of your database. I "fixed" the problem entering Resistance data. Control needs some work, too.

Note that with the corrected database design, the officers will enter one ROW per resistance or control method. You can't use multi-check boxes unless you want to design an unbound form and a bunch of code to load / save the data.

The file is in 2_Assistance Needed / UOF - JV Copy.accdb

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 10, 2014, at 9:29 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John, I've uploaded the database again. My main form is frmUseofForceMain. I'm having difficulty with the subfrmReportSubjControl and subfrmReportSubjResistance. I need to have the option to check multiple items in the various ControlTypes which I've never had trouble with before now. Now I can't get check boxes without everything having the same list of choices.

I don't really understand the paragraph in your previous email on Oct 9 when you talk about hidden labels and specific instructions so maybe this has something to do with the problems I'm having? I also don't think I put the subfrmSubject in the right place.

I'm so sorry to be such a pain. Thanks!

Jessica

On Friday, October 10, 2014 4:31 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

If you followed my design, you should be able to enter multiple ReportSubjResistance records for each report. Each one will have an Effective Yes/No entry. What does your data entry form look like, especially the Resistance tab?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 9, 2014, at 11:35 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I built the tables you suggested John (with a few minor tweaks for my sake) and added tables for the application areas to go along with the body diagrams but as I try to build the forms I'm struggling to make things work. I will upload a PDF of what I would like a subform for ControlType and ReportSubjControl to look like. I can't see how these subforms will collect the date I need with the fields you suggest.

Anyone ready to kick me out of the group yet? I really do appreciate your patience. I'm learning a lot from all of you!

Jessica

On Thursday, October 9, 2014 1:46 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

Yes, that should probably be in a separate table related to ReportSubjControl. Rows will exist only when ControlWeapon is TASER.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 9, 2014, at 8:30 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks John. That's a lot of tables! I printed the email and highlighted the various PK and related FK so that I think it might actually make sense to me.

As for the body diagrams: in my original plan there is a list box next to each diagram with the description that coincides with the points on the body (Ex. 1 = head, 2 = neck, 3 = right shoulder, etc.). I suppose these will be an additional table?

Jessica

On Thursday, October 9, 2014 2:56 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

Here's what I come up with from looking at the form:

Incidents: IncidentID (PK), IncidentDate, IncidentTime, IncidentNoOfficers, IncidentNoSubjects

Officers: OfficerID (PK), OfficerName, OfficerBadge

Subjects: SubjectID (PK), SubjLastName, SubjFirstName, SubjDOB, SubjGender

IncidentReports: ReportID (PK), IncidentID (FK), OfficerID (FK), SubjectID (FK), OfficerInj (yes/no), OfficerInjury, OfficerTreatLoc

Note: Unique index on IncidentID, OfficerID, SubjectID to ensure only one report per Incident, Officer, and Subject

ReportSubjInjury: SubjInjuryID (PK) ReportID (FK), SubjInj (yes/no), SubjInjDesc, SubjTreatLoc, SubjUncon (yes/no), SubjInfluenceID (FK), SubjInjComments

SubjInfluence: SubjInfluenceID, SubjInfluenceDesc (Rows: Alcohol, Suspected Alcohol, Chemical Drug, Suspected Chemical Drug, Other)

ReportSubjResistance: ReportSubjResistanceID (PK), ReportID (FK), ResistanceID (FK), ResistanceExplanation (multiple potential rows per Report)

Resistances: ResistanceID (PK), ResistanceDesc (Rows: Psych Intimidation, Verbal Threats or Non-Compliance, Passive, Defensive, Active Aggression, Deadly Force Assaults)

ReportSubjControl: ReportSubjControlID (PK), ReportID (FK), ControlTypeID (FK), ControlLocation, ControlUseNum, ControlEffective (yes/no), ControlWeapon (Technique, MACH, Impact Weapon, Taser #), ControlDetails

ControlTypes: ControlTypeID (PK), ControlTypeDesc (Rows: Verbal, Joint Locks, MACH Hold, Pressure Points, Hand Strike, Leg/Foot Strike, Neck Restraint, Impact Weapon, Chemical Agent, TASER)

I'm not happy with the design of ReportSubjControl, but I can't see any easier way to do it and cover all the bases. I have no clue how you'll handle the body diagram. The subform you use to collect this info will have to have very specific instructions about how to fill in the data for each type. Perhaps have several hidden labels (Visible = False) that describe what to do with each Control Type. In After Update of ControlTypeID, reveal the specific instruction and perhaps show/hide the applicable controls.

For all: Select Yes or No for ControlEffective

For Verbal: enter ControlUseNum (number of commands given) and ControlDetails

For Joint Locks: enter Technique Used in ControlWeapon and any details in ControlDetails

For MACH Hold: enter MACH Used in ControlWeapon and any details in ControlDetails

For Pressure Points: enter ControlLocation, ControlUseNum, and ControlDetails

For Hand Strike: enter ControlLocation, ControlUseNum, and ControlDetails

For Leg/Foot Strike: enter ControlLocation, ControlUseNum, and ControlDetails

For Neck Restraint: enter ControlDetails

For Impact Weapon: enter ControlLocation, ControlUseNum, ControlWeapon, and ControlDetails

For Chemical Agent: enter ControlDetails if applicable

For TASER: enter ControlLocation, ControlUseNum, the TASER # in ControlWeapon, and Cartridge Serial Numbers in ControlDetails

Your subforms will go three levels:

Incidents

Reports

Subject Injury, Subject Resistance, Subject Control

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 8, 2014, at 11:35 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I realize my SubjectInfo things aren't specific to the subject - and by subject this would be the person upon whom force was used - but I couldn't think of another name. We don't need victim information because that is all covered in the actual incident report. This form is just for keeping tabs on what techniques are used, what level of force was used and what level of resistance the subject displayed. The most important information about the subject involved is their age at the time of the incident (juvenile vs. adult) and their gender.

There may be more than one officer involved in an incident and if they all applied force they will all be required to complete the report explaining their actions, not the actions of other officers. There will only be one LawIncident number in this situation. There may also be more than one subject involved but there will be one report for each subject because the actions taken by officers would likely be different for each subject.

As for primary keys, can those 3 tables share a primary key? If I wanted the report as a whole to have an ID # could that be the primary key for all three tables?

I have to think about what you're saying in regards to the LevelofControl. They are all different types of control techniques - Verbal Commands, Joint Locks, MACH Holds, Hand/Foot Strikes, Neck Restraints, Impact Weapons, Chemical Agent, and Taser. It's just that there are specific Joint Lock techniques and specific MACH Holds, etc. while Hand and Foot Strikes will be less specific. A couple of them might require an explanation if the technique used was something not department trained. I haven't completed that form as you might be able to tell.

I did upload the original form. We want to make the Level of Resistance section more detailed.

Thanks!

Jessica

On Wednesday, October 8, 2014 4:12 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

It might also help if you could scan and upload a copy of the blank forms that the officers use now to provide the data. Also let us know if the officers might fill out multiple forms per incident - with the same incident number.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 8, 2014, at 11:03 PM, John Viescas <johnv@msn.com> wrote:

Jessica-

First thing I noticed is tables LevelOfResistance, LevelOfControl, and SubjectInfo have no Primary Key. ALL tables should have a Primary Key. You have Officers related to SubjectInfo - is there one officer per subject? And the SubjectInfo table seems to mix info about Subjects and any Force that was used or Injury sustained. Is it not possible that a Subject (I assume a violator and not a victim) has more than one force applied, more than one potential injury, and more than one officer involved? Do you need to capture Victim information? Where would you do that?

Level of Control seems to me to apply to an individual subject, not the incident itself. And fields like HandStrike, LegFootStrike, NeckConstraint, etc. all refer to different types of control methods. When you have multiple columns for the same type of thing (a control method), those are a "repeating group" in DB design parlance, which is a no-no. Control methods should be in a separate table with one row per method, and there should be a linking table (perhaps to Subject) that indicates the one or more control methods used. You have a similar problem with the LevelOfResistance table.

When looking at columns in a table, ask yourself if several of these columns are just different types of the same thing. If the answer is yes, then they should be in another table with one row per type of thing.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 8, 2014, at 10:22 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica - That's what we're here for. It's much easier for us to see exactly what's going on if we can get our hands on the database. Just hoping your theme colors are not orange and purple. [grin]

Bill Mosca

---In MS_Access_Professionals@yahoogroups.com, <hobackjessica@yahoo.com> wrote :

I will upload the database - titled UOF - Copy

I'm a little scared to show this to a group of Access Experts.

Jessica

On Wednesday, October 8, 2014 2:09 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

I need to see your table design to help you figure out what's wrong.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 8, 2014, at 9:08 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

The "it works great" was premature... I attempted to add a second report to a previously used LawIncident and got an error message. Should this be a combo box so that if the LawIncident number has already been used it pops up in a list? There may be more than one officer submitting a report on a LawIncident number.

Every time I think I'm making progress I hit a wall!

Jessica

On Wednesday, October 8, 2014 1:06 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

I would need to see a synopsis (Primary Keys, Foreign Keys, and the most relevant fields) of your table design to guide you further. At first blush, I don't think you need to put OfficerID in more than the Officers table and the Incidents table.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 8, 2014, at 7:38 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks John! That works great. My next question is this:

I want to be able to count stats (ex. number of reports submitted, use of specific techniques, etc) by officer and by shift. Can I have these fields shared between tables also? Right now the reporting officer and shift are in only one table. If I add them to two other tables, can I relate those tables by more than one field?

Jessica

On Wednesday, October 8, 2014 10:33 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Jessica-

A better technique is to put it all on one form. Edit the main table on the form (should be down to just a few fields now, right?), then put four subforms on tabs in a tab control for the four sub-tables. If you set the Link Master Fields / Link Child Fields of the four subforms to LawIncident, Access will automatically link them for you.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 8, 2014, at 5:25 PM, hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I think I've improved my table structure significantly by splitting my original table with too much information into four smaller tables. My incident number, "LawIncident", is in all four tables and it's the primary key in one table.

I've created a form for each table and put a "Next" button on the bottom of each that opens the next form in the series. I would like the incident number from Form1 to auto fill the Incident number for Form2 and so on. I also want each page to be attributed to the same reporting officer with them having to fill in the blank on each page. Is this possible?

Is this the best way to get all of my information to relate back to the original LawIncident?

Ideally I will be able to pull a complete report that displays all of these pages together.

Thanks,
Jessica