Hidden Text Boxs

[Neowynds]

OK, I am still working on my Security Incident Report database. In my form frmSIR I have a text field called [Parties Involved:]. This text field is bound to tblTable1. What I need is after the user Types in a party involved in the Incident, the database needs to prompt them and ask if there are any more parties involved. If the answer is yes then another text box needs to become available on the form with the additional text box being linked to [Additional Parties1], [Additional Parties2], etc. on tblTable1. If the answer is no the cursor needs to skip the hidden text boxes and move to the next field. Is there a way to do this? This is my first major database to need so many functions, so I know little to no code. Also when the database is done I will need to make it web accessable through a secure website accessing the company network.

Thanks in advance for any help,
Neowynds

 

[Eupher]

Well, the hidden fields business is easy enough. Just set the "Visible" property of the fields you wish to be initially invisible to "No". Then in the After Update event for the [Parties Involved] field, put in code to make the next field visible, i.e.

Me![Additional Parties1].Visible = True

... and so forth for each successive field.

But I'm a little concerned by this kind of data structure. How many potential additional parties do you envision? What happens when the number of additional parties exceeds the fields you've allotted for them? If total additional parties will only ever be one or two it's probably not a big deal, but if there is a potential for a large number, then I would consider "normalizing" your data and displaying your additional parties entries via a listbox or separate form. If this concept is unfamiliar to you (as it was to me not so long ago!), do a quick search on "normalizing" or "normalised" databases and see what you come up with. Post back if you need more help.

Ken S.

 

[Neowynds]

Eupher,

Thank you for the quick response.
Sorry for the delay on my part, my manager was wavering as to if I should keep working on the database or not. I spoke with a friend with more Access experience than myself and he has shown me what you meant by normalizing. However I am still having some problems with it.

I have got frmTable1 that has all my report info on it and a subform within it called frmParties subform. frmTable1 is linked to tblTable1 and frmParties subform is linked to tblParties. I have the Primary Key on tblTable1 as an auto number field labeled ReportID. I have the Primary Key in tblParties as an autonumber field labeled PartyID. Now this is where I get lost. I was told I would need a third table with each of the unique identifiers (PartyID and ReportID) with a composite key to where when I ran a report it would match the PatyID to the ReportID for the particular report I was running and list all parties with the same corresponding ReportID. I have been struggling trying to learn how relationships and composite keys work and am totally lost. Can you or anyone else help, maybe explain how I am going to have to do this or point me to a web resource that explains relationships and composite keys a little better?

(Desperate)Neowynds

Neowynds

 

[willir]

Neowynds

Oh man, I lost the original post (again)

Check out the Access forum for Tables and Relationships.

JermeyNYC has an article on his web site -- "Fundamentals of Relational Database Design" by Paul Litwin

http://www.ABCDataworks.com

Also, a walk through...
thread700-628486 I perfer to look at what I need in terms of outcomes. Design the basic tables and supporting tables. Define the relationships + more tables. Test with sample data.

Since you are in a hurry...

You already identified two required tables. I am going to use more friendly names plus adlib a bit since I do not have all the info required...


tblOfficer
Officer or supervisor

OfficerID or BadgeNO - primary key identifying person
LName - text, last name
FName - text
+ contact info


tblIncident
documentation on incident - your key table

IncidentID - primary key, autonumber
StartDate - date, date incident occurred / commenced
EndDate - date, date of resolution
OfficerID or BadgeNo - foreign key to Officer - links officer to Incident
IncidentStatus - text - open, closed, researching, parked, unresolved, etc


I digress...
In a relationship, there are three basic relationships.
- one-to-one - does not happen often, special circumstances
- one-to-many - occurs frequently. Several people can live in one house
- many-to-many - kids in high school. A student will have several teachers; a teacher will have many students.

The relationship between officer and Incident in thi sexample is one-to-many. One officer or supervisor will have many incidents. The foreign key, in this case the OfficerID or BadgeNo is stored on the "many" side or child side (tblIncident) to link the records to the parent record (tblOfficer).


tblParty
Document info on parties

PartyID - autonumber, primary key
IncidentID - long number, foreign key, used to link to Incident
LName - text
FName - text
+ contact info
+ perhaps memo field for comments

Here is a curve.
How can you tell which party was involved in more than one incident?

In actuality, you may have certain parties that are involved in more than one incident and you may want to track this.

This would be a many-to-many relationship. An incident can have more than one party. A party can have more than one incident.

tblContact
Contact info

ContactID - autonumber, priamry key
LName - text
FName - text
+ contact info


tblParty
Remake of the parties involved table

IncidentID - long number, foreign key for Incindent
ContactID - long number, foreign key for contact
+ other party infomation unique to this encounter
Primary key is IncidentID + ContactID

This is a joining or intermiary table. Remember how for the foreign key is always stored on the "many" side. In this case, you need this joining table to link the two tables.

Now, if required, you can review incident info by partys or contact, as well as looking at an incident to see impacted parrties.


Another curve...
We have not discussed how you want to store the comments - best to use a memo field. But lets say using one long memo is not pragmatic -- you may end up wanting a memo for the initial encounter, research notes plus a summary.

tblReport

ReportID - autonumber, priamry key
IncidentID - long number, foreign key to incident
OfficerID or BadgeNO
ReportDate
ReportCategory - text, if you want to summarize various reports - summary, etc
ReportDetail - Memo

This way you can use one memo for a short encounter, or several memos by different officers for long encounters.


Next is establishing relationships. Jermey's link covers this. Also consider getting a good hands-on book.

Briefly,
- Open up the Relatiohship window - tools, relationships.
- Add tables required (right click to get dialog box)
- Click and select primary key in the parent table, drag it to the foreign key in the child table
- Right click on the joining line and select "Edit Relationship", and then ensure "Enforce Referential Integriy" is selected.

It takes a bit of work, but I suspect your boss will be impressed if you can get the basics down.

Richard
(okay, tyring to post again - this time I will copy first!)

 

[willir]

Neowynds

More on documenting your reports. If you decide to go beyond a memo field, here are a couple of ideas that may add some sparkle

tblReport

ReportID - autonumber, priamry key
IncidentID - long number, foreign key to incident
OfficerID or BadgeNO
ReportDate
ReportCategory - text, if you want to summarize various reports - summary, etc
[blue]ReportTitle - text
ReportLink - Hyperlink to external document[/blue]
ReportDetail - Memo

The incident reports could be displayed in a continuous form with the date and title being depicted with a button that opens the full report.

The hyperlink could link to an external document such as a word processing file, a web site, a log file, etc. This way, although documentation may be spread over several locations, the Access database link would act a central access point.

Richard

 

[Neowynds]

Thank you both this clarifies things greatly.

Richard,
The hyperlinking is a good idea, is there a visual basic code I could use to, say make a DAO popup box so that when an officer is filling out the database form he can type in or browse for the documents address?

Thanks again for the quick responses and continual help

Neowynds

 

[willir]

Neowynds

One of the options for for field types is hyper link.

I personally have not played around using this type of code since I am a hands on person in some respects, but the Access interface is fairly simple...

In the form view, place the cursor in the hyperlink field. Options now to insert the link are...
- CTRL-K (short cut)
- From menu, select Insert -> Hyperlink
- Click on the icon with the globe + chain link
- Right click, select Hyperlink -> Edit Hyperlink

Anyone of these will bring up the HyperLink dialog box. Browse to the document or web page.

After the link has been inserted, the user just has to click on the link to open the document.

Richard

 

[Neowynds]

Many thanks for the explanation and tips I will be exploring this as an option.

Neowynds