Creating new record

[ellixer]

I have a form with tabbed sub forms. When i try to create a new record it lets me start typing but then gives an error msg. I will not let me enter information unless the key that is used to connect them (the id numbers) is typed into every table first. Then i can create a new record with that id number.

[jzwp11]

Have you set up your relationships between the table behind the main form and those related tables behind the subforms? Can you post your table structure and identify which tables are involved in your form issue?

[ellixer]

I have relationships, ive never touched access before this so its probably wrong.

[jzwp11]

You're right, your structure is not correct. For example, you have beer, wine, liquor etc as fields in a table. They should be records in a table, that way you can add more items without having to change the tables structure (i.e. adding more fields). You should probably take a look at this site on database normalization. Normalization are the rules used to design a database. You might also take a look at this site; it has several tutorials that might be of value.

[ellixer]

Thank you, I've been having a hard time trying to find something on normalization that works with the amount of data i work with, so its been very confusing going.

[jzwp11]

We are here to help, so after you have had time to digest normalization and still have questions pertinent to your table structure, just post back.

Another thing I generally recommend is that if your field names are specific to particular items then more than likely they should be records in a table not fields. The table/field names should be more general. For example, your table that had the beer, wine, ecstasy, marijuana etc. These could all be considered as controlled substances, so why not just have a table to hold all of them

tblControlledSubstances
-pkControlledSubID primary key, autonumber
-txtSubstanceName

Another example, if you have multiple types of fees, consider putting them in a table as records

tblFeeTypes
-pkFeeTypeID primary key, autonumber
-txtFeeTypeName

After you have digested normalization, the next step would be to analyze your data/process. The analysis will help start to flush out the tables you will need and the relationships between them.

Just as some general recommendations, it is best not to have spaces or special characters in your table and field names. Also, you will want to be aware of reserved words in Access. The most common ones I see are the words date and name. It is best not to use reserved words as table or field names. Also, Access has the capability of lookup fields (combo and list boxes) at the table level. Using them, however, is not recommended. This site provides more details. Combo and list boxes are best used in forms.

[ellixer]

Ok that seems simple enough, but i want to be able to use a form to input all of this data to make it easier, as its a lot of info. Having the form with the participants info as the main, and all the others as tabbed subforms was how i was trying to do it.

[jzwp11]

I understand about the forms, but you must get your table structure set up properly--first. A successful database application depends on a properly normalized table structure. Without a solid structure, the application will turn into a nightmare. So when you have applied the rules of normalization and rebuilt your structure could you post it for us to review? If you have trouble with the structure as you are trying to redesign it, please post back with any questions

[ellixer]

Hmmm, im a tiny bit confused about where you said changing the beer, liquor to records instead of fields. They are yes no responses, so i just thought it would be easier to put them as fields.

[jzwp11]

They are yes no responses, so i just thought it would be easier to put them as

I understand why you used yes/no fields, but what would happen if you want to add another substance? You would have to restructure the table and any queries, forms or reports that use that table (essentially rebuilding the database). It would be much easier to add a new substance as a record, then you would not have to rework your table, forms, queries and reports.

Not knowing much about your application, I assume that you are documenting which substance or substances are associated with a person. Instead of checking a bunch of yes/no boxes, you would just associate the specific substance or substances to a person. Here is a basic structure.

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblSubstances
-pkSubstanceID primary key, autonumber
-txtSubstanceName

tblPeopleSubstances
-pkPeopleSubID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkSubstanceID foreign key to tblSubstances

Just as a note the pk...ID is joined with its corresponding fk...ID to form the relationships between the table. (pk denotes primary key, fk denotes foreign key, txt denotes text field)

[ellixer]

Ok, i think i have a better understanding. I think I understand the keys better, its just really meshing out all the tables. Aye, for my first ever database they gave me alot to do.

[jzwp11]

The first database is always the hardest! If you have questions as you restructure your design, please post back.

[ellixer]

Ok, lets see how horribly wrong I might be with this. This is what ive got so far.

tblParticipants
ParticipantsID
LastName
FirstName

tblDetails
DetailsID
address
phoneNumber

tblPartcDetails
PartcDetailsID
ParticipantsID
DetailsID

and so on.

[jzwp11]

The structure you proposed is not wrong, but does it apply to your application? Having a junction table (tblPartcDetails) implies that a participant can have many details (addresses/phone numbers) and that an address/phone number can apply to many participants. If that is the case in your application, then the structure would be fine.

[ellixer]

Ahh, Im dumb.