Results 1 to 2 of 2
02-18-13, 13:38 #1Registered User
- Join Date
- Feb 2013
multiple select using option button or checkboxes
I'm new at this but I'm having a problem with some coding. I haven't worked with VBA for awhile and am a little unsure how I would code this. I'm designing a database for my senior project and I have a "select all that apply" and would like to have them which checked/clicked to go into the column Crime. I've tried different things and I have the 0, 1 value for both checkbox and the radio button. I've attached a picture of what I have on the form. I'm confused on how I need to start the code for it. I've tried the If Then statement but that hasn't worked. I've been told to do Parent--Child relationship but unsure how to do that as well. I've Googled and haven't had any luck finding what I need for this database.
Any help would be awesome! I'm sorry if I don't make much since, it's the case of the Mondays.
02-18-13, 14:38 #2Super Moderator
- Join Date
- Jun 2004
- Arizona, USA
A parent-child table is a child, or sub-table, which is dependent upon the parent table.
In your main table, there has to be a primary key for the records. (if not, you are already in trouble!) This primary key field can be automatically generated if no suitable primary key can be used from the data you are using in the table.
The child table would have a column in it with the same data type (and often the same name) as the primary key of the parent table. In addition it will have another key column, and the combination of the second key column and the parent key column must be unique. For instance in an ordering system, the order number is often a primary key in the parent table, and the order number and order line number are the primary key in the child table. In this example, the parent table is the ORDERS table, and the child table is the ORDER_DETAILS table.
If you are storing information about incidents at a school, you would have a table containing ONLY information specific to the incident as a whole, and a child table (or tables) which contain information about each sub-incident. In your case, you would have a child table which would contain the incident ID, the crime id, and possibly other information about this particular crime. A Crime Details text field, and comments, for instance.. Only one instance of a crime could be assigned to a given incident, though,
The other area where tables are related (but not in a parent-child relationship) is where they are being used as lookup tables. A listing of state abbreviations and the state name is a common lookup table. In your case, you might want a lookup table, where an automatically generated (or not) CrimeID key field is used, along with the description of the associated crime, and a display order field. Although the ID key could be used for order of presentation, as well as unique key number, this isn't a good idea.
You can then query the crimes lookup table, retrieving the description, id, and sorting by the display order. Then, at form load, load the description into the text property, and the ID into the itemdata property of the new item in the combo box for each record of the recordset.
The advantage of lookup tables are that you can change the description of the 'crime' simply by editing the lookup table text. And, you can change the display order on your combo box just by changing the display order field in the lookup table. (You might want the most common-occurring 'crimes' at the top of the combobox list, and the least common at the bottom, for instance.)
However, in the long run, you might be be better off with a many-to-many relationship.
You would still have the crime table, still used to populate the combo box. And the Incident table, holding information about the incident as a whole. The many-to-many table, also called an intersection table, would be added. This table holds the Incident ID, the Crime ID, and supplemental, supporting data. (memo, description, supporting info, etc.)
If we assume this table was named Incident_Allegations, it would have a compound primary key consisting of both the IncidentID, and the CrimeID. The IncidentID would also be a foreign key pointing to the Incident table, and the CrimeID would be a foreign key relating to the Crimes table. (and, if you ever needed to add the possibility of multiple instances of a given crime ID associated with a single incident, add a field for IncidentCrimesID, which for a given incident and Crime ID, would start with (and defaults to) 1, and end with N. (it starts over for each IncidentID/CrimeID) You would include this field in the table's Primary Key definition.
Last edited by loquin; 02-18-13 at 14:44.