Filling table with data 1

[JAES]

Oops, no subject on my first post.

Any assistance would be appreciated. I'm sort of new so I hope I can explain my problem correctly.

Table 1 is CaseFiles and has the columns CaseFileNum, CaseAgent, Crime and EntryDate. The basic information regarding a case is stored here when a case is opened and usually does not change once entered.

Table 2 is CaseFileSuspects and has the columns CaseFileNum, SuspectID and SuspectType. This table stores all the suspects (from a master Suspects table) that have been assigned to a particular case. Suspects can be assigned to many cases in their “career”.

Table 3 is InvestigationDetails and contains all information regarding the individual investigation. A single case file can have up to 40 investigations. Among the 25 or so columns are CaseFileNum and InvestigationNum. Until now, when printing the investigation information I included all suspects assigned to that case file. Now I need the ability to not print some suspects. I wanted to have a subform in the InvestigationDetails form that would show all suspects assigned to the case and be able to uncheck those that I didn’t want to be included in the printed reports. In addition, I need to be able to identify any suspect that was arrested during this particular investigation.

I made a new table InvestigationSuspects that had the columns InvestigationNum, SuspectID and a Yes/No column for Arrested. I have no clue as to how to get the information to the new table for use later.

Again, any assistance would be appreciated.

Jeff

 

[Trevil]

Hi Jeff,

How many users of this application? Is this a 'front-end / back-end' database, or is everything in one MDB?

If more than one user, I think you will need a variation of your new table (InvestigationSuspects).

With multiple users in one database, if you turn the flag on or off, then other users may get unhappy.

One solution would be to create a temp table with all the flags set to TRUE whenever you open the form. Then your subform would allow some records to be flagged as FALSE. The query for the report would simply look for records that are TRUE.

As far as creating the temp table originally, if there are multiple users and they share the same database, you will need some naming convention (i.e. 'xxxx' + WorkstationName; or 'xxxx' + timestamp; etc). If each user has their own front-end, the naming problem goes away.

If single user or each user has their own front-end:
1. Define your table with the flag.
2. Wherever you select the CASE, add code that:
a. deletes all suspects from temp table.
b. appends all suspects for that case; set flag to true.
3. Your subform will allow changing the true/false flag.

If multi-user AND sharing same database, it gets messy because you will need some way of changing the temp table name in your SQL for both your subform and the report. I can suggest something if this is the case (no, not that CASE).

Wayne

35+ years of 'progress' -- can't we all just go wire boards again?

 

[SiJP]

Suspects can be assigned to many cases in their “career”.

Lol

Seems like you already have a good idea on how to do this - create a form that has the fields from InvestigationSuspects on it (InvestigationNum, SuspectID and a Yes/No column for Arrested also maybe a yes/no for "print on report"?).

Add this as a subform to your InvestigationDetails form, and ensure you link the two via the InvestigationNum (using the parant/child links).

When you create a new entry in InvestigationDetail, then start adding data to the sub form, you will find that your InvestigationSuspects table gets populated..

Give it a go.. let us know of any probs!

------------------------
Hit any User to continue

 

[JAES]

There are between 15 to 20 users of the database. I have split it so the data resides on a server and the rest is on each computer for speed.

After reading the two posts I think I will append all CaseFileSuspects information for that case file to the InvestigationSuspects table when a new investigation is opened. Then I can use the subform to edit the data as needed. I will have the Arrested and Print check boxes and will feed reports off that table.

I have to maintain which suspect was assigned to which investigation for future printing of reports so I don't think a temp table will work.

All I need to do now is figure out how to append the information after I enter the case file number on the InvestigationDetails form so it is available when I gointo the subform.

Jeff

 

[Trevil]

I have another question (or two)...

I believe at the top level you have table A which is 'CaseFiles' and table B which is 'Suspects master?'. Table C is 'InvestigationDetails' and I assume has a defined relationship with 'CaseFiles'. Finaly table D is 'CaseFileSuspects' which I assume allows you to relate many cases to many suspects.

Are suspects related to table 'CaseFiles', table 'InvestigationDetails' or both? If you need to relate a subset of CaseFileSuspects to one or more InvestigationDetail records, then I assume you have another table (i.e. InvestigationSuspects?).

I assume you already have the forms in place to maintain the list of suspects related to CaseFiles and/or InvestigationDetails. When you open a new investigation, do you automatically populate the existing list of suspects, or is that list created by selecting from your SuspectMaster?

Re "All I need to do now is figure out how to append the information after I enter the case file number on the InvestigationDetails form so it is available when I go into the subform", unless I misunderstood something, you could have "After Update" events that fire to append your information and to refresh your subforms.

Am I close?



35+ years of 'progress' -- can't we all just go wire boards again?

 

[JAES]

Yes, I think you are close (and have brought me along too!)

Suspects are not related to CaseFiles or InvestigationDetails tables. That is where I am using the tables CaseFileSuspects and now InvestigationSuspects. I need to be able to know all suspects that were assigned to a case file and all suspects that were assigned to an investigation within that case file (many times these are the same). When opening a new case file the suspects are chosen from the SuspectsMaster table and stored in the CaseFileSuspects table, creating a relationship between a suspect and the case file.

When you open a new investigation the CaseFileNumber is the first field that you fill in. That verifies that the case file number is valid and allows you to continue. At this time I would like to populate the InvestigationSuspect table with the existing suspect information from CaseFileSuspects for that case file. (wow, now I'm getting confused!)

I think you answered part of my question by suggesting that I place the append code in the AfterUpdate event in the CaseFileNumber data field. All I need to do now is figure out the code for the AfterUpate event.

In addition, when I append the InvestigationSuspects table with the suspect information I need to add the investigation number too. That I'm still unsure of how to accomplish.

Your efforts are appreciated. Your have at least got me on the correct path.

Jeff

 

[Trevil]

OK, I think I have enough to go on, but I've just got to jerk your chain a little. You say "Suspects are not related to CaseFiles..." and then " I need to be able to know all suspects that were assigned to a case file..." -- hmmm, that kinda sorta maybe sounds like there's a relationship. But onwards!

Since I don't know what your "new investigation" form looks like or what controls you may already have, I'll give a few options. One important issue will be how is the key generated for the new investigation. I realize it will be a combination of CaseFileNumber + something, but does someone manually assign the something or is it generated (i.e. an AutoNumber field)? I will assume it's manually entered since that would be easier.

1. Once you fill in the CaseFileNumber and type in any description or other stuff, I assume you will click some button (such as 'Save This Stuff' or 'Add Suspects').
2. In the 'On Click' event for that button, you will need to be sure the new record has been saved. I assume your form is bound to a recordset and that is the source of data for the fields. The click event can save the keys.
3. Once the record is saved you can populate table 'InvestigationSuspects' with the following SQL:

INSERT INTO InvestigationSuspects ( SuspectID, CaseNbr, InvestigationNumber )
SELECT CaseFileSuspects.SuspectID, CaseFileSuspects.CaseNbr, [Forms]![frmInvestigation]![txtInvestigationNumber] AS InvNbr
FROM CaseFileSuspects
WHERE (((CaseFileSuspects.CaseNbr)=[Forms]![frmInvestigation]![cboCaseNumber]));

Note that it filters suspects based on the case number entered on the form, and fills in the Investigation number from the box on the form.

I created a sample database so I could generate the SQL, so if there are other questions, just let me know.

Good Luck!

35+ years of 'progress' -- can't we all just go wire boards again?

 

[JAES]

OK, I accept the flogging as given. Relationships, like in life, are stil a little fuzzy to me!

That did it. I had gotten to the point where the investigation number was not being added when you last post came in. It all became very clear and works like a charm. Many thanks to you and this forum for your assistance. I'll check in next time I have a unsolvable problem :-D

Jeff

 

[JAES]

Hey, all of a sudden the subform is not picking up the data that was just inserted to the table. It was working fine and I must have screwed up something. You can go to the next record then return and the information will be there. What did I do?

Jeff

 

[Trevil]

I believe you have code that runs a query that will insert the suspects when you open a new investigation. Depending on what controls you have on the subform (is it a listbox of suspects?) you willl need to issue something like the following after you insert the records: me.subformname.requery or me.subformname!listsuspects.requery



35+ years of 'progress' -- can't we all just go wire boards again?

 

[JAES]

That was it, thank you again for your assistance.

Jeff