Jessica-
OK, let me know how you get along.
Two key pieces of code I added:
1) In the main form to handle LawIncident:
The LawIncident combo box also pulls columns for Nature, IncDateTime, and IncNoOfficers. If the user selects an existing ID (discovered by checking with DLookup), the code below fills in those three fields by copying values from the combo columns. (By the way, Column(0) is the first column.)
Private Sub cboLawIncident_AfterUpdate()
Dim strNoOfficers As String
' If on a new record,
If Me.NewRecord Then
' If the LawIncident number already exists,
If Not IsNull(DLookup("LawIncident", "IncidentReports", "LawIncident = '" & Me.LawIncident & "'")) Then
' Value entered already exists in the database, so copy the columns
Me.Nature = Me.cboLawIncident.Column(1)
Me.IncDateTime = Me.cboLawIncident.Column(2)
' Watch out for blank or Null in No Officers
If Not IsNull(Me.cboLawIncident.Column(3)) Then
' Get rid of blanks in the string
strNoOfficers = Replace(Me.cboLawIncident.Column(3), " ", "")
' If didn't end up with nothing
If strNoOfficers <> "" Then
' Copy the value
Me.IncNoOfficers = CLng(strNoOfficers)
End If
End If
End If
End If
End Sub
The other two bits of code make sure that the combo box for LawIncident is always up to date:
Private Sub Form_AfterDelConfirm(Status As Integer)
' Make sure Incident combo gets updated after deleting
Me.cboLawIncident.Requery
End Sub
Private Sub Form_AfterUpdate()
' Make sure Incident combo gets updated after saving
Me.cboLawIncident.Requery
End Sub
2) In frmReportSubjControl, code shows / hides controls based on what the user has picked in Control Type:
Private Sub cboControlType_AfterUpdate()
' Requery dependent Control Weapon
Me.CboControlWeapon.Requery
' First, hide all "optional" controls
Me.CboControlWeapon.Visible = False
Me.TaserSN.Visible = False
Me.CartridgeSN.Visible = False
Me.lstAppBack.Visible = False
Me.lstAppFront.Visible = False
' Now see which control type chosen and unhide stuff
' NOTE: You can also change what's in the associated labels
' For example, to change "Explain Control Use" to "Verbal Commands Given", do:
' Me.lblControlExplanation.Caption = "Verbal Commands Given"
Select Case Me.ControlTypeID
Case 1 ' Verbal Commands
' Leave everything hidden?
Case 2 ' Joint Locks
' Unhide Weapon, and front / back
Me.CboControlWeapon.Visible = True
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Case 3 ' MACH Hold
' Unhide Weapon, and front / back
Me.CboControlWeapon.Visible = True
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Case 4 ' Pressure Points
' Unhide Weapon, and front / back
Me.CboControlWeapon.Visible = True
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Case 5 ' Hand Strike
' Unhide front / back
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Case 6 ' Leg/Foot Strike
' Unhide front / back
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Case 7 ' Neck Restraint
' Unhide Weapon, and front / back
Me.CboControlWeapon.Visible = True
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Case 8 ' Impact Weapon
' Unhide front / back
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Case 9 ' Chemical Agent
' What to unhide here?
Case 10 ' Taser
' Unhide Weapon, front / back, Taser
Me.CboControlWeapon.Visible = True
Me.lstAppBack.Visible = True
Me.lstAppFront.Visible = True
Me.TaserSN.Visible = True
Me.CartridgeSN.Visible = True
End Select
End Sub
I merely guessed at which controls should appear / disappear, but the code skeleton should give you an idea of how it's done.
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
(Paris, France)
On Nov 7, 2014, at 4:46 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John, looking at the database you modified for me I think that was an older version that I uploaded. I've made changes since then. I will study what you've done with it though and apply those changes to the version I've been working on.
Thank you!
Jessica
On Friday, November 7, 2014 7:49 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
I think I fixed almost all the problems. LawIncident is now a combo box. If you drop it down, you can see all the other LawIncident numbers already recorded. If this is just another report on the same incident, you can pick that incident, and then in AfterUpdate I load Nature, DateTime, and number of officers from the previous report to try to keep them in sync.
I fixed your problem with the Requery of ControlWeapon - you didn't correct the parameter as I instructed. There's also code now in the AfterUpdate event of ControlTypeID that gives you an idea how to hide / show the appropriate controls.
Oh, and I removed the second text box that was bound to CartridgeSN - It makes no sense to have two controls updating the same field. Instruct the officers to either fill in both cartridge numbers separated by a comma or add a second field for the second cartridge. I assume there are never more than two used.
See UOF - JV Copy 2 that I just uploaded to Files / 2_AssistanceNeeded.
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
(Paris, France)
On Nov 6, 2014, at 11:58 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John, I uploaded the most recent version of my database called UOF- Copy. I really appreciate the help you're giving me with this.
Jessica
On Tuesday, November 4, 2014 3:35 PM, "Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
I have made a couple changes. I will upload again.
Jessica
On Tuesday, November 4, 2014 3:08 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
I went into the last UOF - Copy database you posted, changed your form to Data Entry no, and took a look at the records. I was able to add a record with the same incident L14105555 that was already saved in the database. I see no LawIncidentID, so no Primary Key. Did you tweak the database some more? Perhaps another upload is in order.
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
(Paris, France)
On Nov 4, 2014, at 9:53 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
By the way, the row source information you gave me works. Now to figure out the after update stuff. Thanks again John.
On Tuesday, November 4, 2014 2:39 PM, Jessica Hoback <hobackjessica@yahoo.com> wrote:
John,
When I try to create a new report using an existing LawIncidentID I get this message: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." My LawIncidentID is my primary key because it's unique to the incident and correlates back to our records management system. Do I need to have an auto numbered primary key instead?
I'm also wondering now if I'm using the wrong table as the control source for my main form. I added the unbound combo box to to the form header for my LawIncidents form and it works until I make it a subform. As a subform on my frmUOFMain (IncidentReports as the control source) when I choose an existing LawIncidentID from that combo box I get this message: "The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'Nature'". When I click OK it fills in the related fields the way it should. But then I can't add a new IncidentReport using it.
Jessica
On Tuesday, November 4, 2014 9:34 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
Yes, that sounds about right. I would put the LawIncidentID unbound search combo in the form header so it's not confused with the combo that's bound. I see no reason why it won't let you enter another report with the same LawIncidentID as is in another record. What is the exact text of the message, and what steps did you take to get 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
(Paris, France)
On Nov 4, 2014, at 2:58 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Thanks John. I will try that when I get some time this morning. The Control Weapons table has a ControlTypeID column that corresponds to the ControlType. That number is the parameter I have to enter to get around my subform problem.
I am just getting frustrated with the LawIncident thing. It won't let me add a new Report using an existing LawIncidentID. I would need one, unbound, combo box to look for existing LawIncidentID that will autofill the other fields (this is where I get the error message about duplicates) and one text box for LawIncidentID bound to that field in the LawIncidents table for adding new LawIncidents - does that sound right?
Jessica
On Tuesday, November 4, 2014 3:47 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
The whole purpose of having separate LawIncidents and IncidentReports was to allow multiple officers to file a report about the same incident. With your new design, each officer will create a new ReportID but will have to be careful to use the SAME IncidentID and fill in the same info about the incident date and time for everything to remain so that you can have multiple reports about the same incident. I recommend to keep the separate tables.
Moving SubjName, etc., to ReportSubjInjury table makes sense.
Your Row Source for CboControlWeapon needs to be:
SELECT ControlWeapon.WeaponID, ControlWeapon.ControlWeaponDesc, ControlWeapon.ControlTypeID
FROM ControlWeapon
WHERE (((ControlWeapon.ControlTypeID)=[Forms]![frmUOFMain]![frmReportSubjControl].[Form]![cboControlType]));
That "walks down" from the outer form to the subform control, then to the form inside the subform control, and finally to the other combo box.
In the AfterUpdate event of either the cboControlType or CboControlWeapon, you can examine the selection and then set the Visible property of the controls on the subform appropriately. This is much easier to do in VBA than with Embedded Macros.
I can see that your Control Types are as follows:
ControlTypeID | ControlTypeDesc |
1 | Verbal Commands |
2 | Joint Locks |
3 | MACH Hold |
4 | Pressure Points |
5 | Hand Strike |
6 | Leg/Foot Strike |
7 | Neck Restraint |
8 | Impact Weapon |
9 | Chemical Agent |
10 | TASER |
And your Control Weapons are:
WeaponID | ControlTypeID | ControlWeaponDesc |
1 | 2 | Straight Arm Bar |
2 | 2 | Iron Wrist Lock |
3 | 2 | Transport Wrist Lock |
4 | 3 | MACH 1 |
5 | 3 | MACH 2 |
6 | 3 | MACH 3 |
7 | 3 | MACH 4 |
8 | 3 | MACH 5 |
9 | 7 | Level I: Rear Neck Lock, No Compression |
10 | 7 | Level II: Compression Until Conscious Compliance |
11 | 7 | Level III: Compression Until Unconsciousness |
12 | 4 | Mandibular Angle |
13 | 4 | Hypoglossal |
14 | 4 | Infra Orbital |
15 | 4 | Jugular Notch |
16 | 4 | Brachial Plexus Clavical Notch |
17 | 4 | Brachial Plexus Origin |
18 | 4 | Brachial Plexus Tie-In |
19 | 4 | Suprascapular |
20 | 4 | Radial Nerve |
21 | 4 | Median Nerve |
22 | 4 | Common Peroneal |
23 | 4 | Femoral Nerve |
24 | 4 | Tibial Nerve |
25 | 4 | Superficial Peroneal |
26 | 4 | Other (explain) |
If you can give me some rules as to which controls should appear or disappear for a specific Control Type or Weapon, I can give you some sample code.
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
(Paris, France)
On Nov 3, 2014, at 10:50 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
To make my life easier I want to move the SubjName, subjDOB, and SubjGender fields to my ReportSubjInjury table and form rather than in it's own table and form. For our purposes, the name of the subject involved isn't terribly important, since that info is kept in our records management system anyway, but I do want to be able to query by gender and age. I also want to merge LawIncidents with IncidentReports. These changes would solve my first two problems. I will upload a database "UOF - Copy" that I've used to make these adjustments.
I don't know how to make the application location choices/photos visible only when necessary but I'd like to be able to do that with the TaserSN and CartridgeSN fields also and have it based on the choice made in Control Type Used (ControlTypeID). I thought I had it figured out but it messed up my ability to limit the choices in the ControlWeapon combo box based on Control Type choice.
Thank you,
Jessica
On Monday, November 3, 2014 11:57 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
Rather than try to address specific problems on your form, let's revisit the underlying design.
First problem I see is you have Subjects related to Incident Reports 1 to many, yet you have Subjects as a subform of Incident Reports, which is backwards. If there are potentially many subjects for one incident or a subject may appear in multiple incidents, then you need a linking table IncidentSubjects: LawIncidentID (or maybe ReportID), SubjectID, and perhaps other fields that detail how the subject relates to the incident or report.
Looking further down the form, you should not have two subforms both using ReportSubjControl as the Record Source. If a type of control requires that the officer pick front and back diagrams, those controls should be on the same form as Control Type Used and should show/hide (setting the Visible property) as needed.
Fix those problems first, then let's see where you need to go from there.
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
(Paris, France)
On Nov 3, 2014, at 6:12 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John, For some reason your last email was flagged as spam and I just saw it. I'm afraid I thought you were fed up with me :)
I have uploaded my database again. frmUseofForceMain is my problem form. I have a combo box for existing LawIncidentIDs and a text box for adding New LawIncidentIDs. It will not allow me to reuse and existing LawIncidentID for additional IncidentReports. Should the LawIncidents table be the record source for this main form as it is now or should IncidentReports be the record source for the main form with LawIncidents as a subform? I've tried it both ways with no luck.
The other problem is on the tab control Level of Control. In my frmReportSubjControl I was able to set it that the options for Technique (ControlWeapon combo box) depended on the choice selected in Control Type Used (ControlTypeID combo box). When this form is a subform however I have had no luck getting it to work with entering a parameter.
My SubjectID is not linking to the IncidentReports table the way it should be either. I'm sure this is a simple and obvious fix, but I'm stuck.
Thanks!
Jessica
On Friday, October 24, 2014 10:38 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jessica-
I'm having a hard time visualizing your problems. Can you post a copy of your database to Files / 2_AssistanceNeeded?
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
(Paris, France)
On Oct 24, 2014, at 4:16 PM, Jessica Hoback hobackjessica@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
(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
(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
(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
(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
(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
(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
(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