Solved

MS Access

Posted on 2014-10-09
22
137 Views
Last Modified: 2014-10-22
I have 4 tables created in my MS Access database.  I am trying to enter data into a form that I created, however when I go to enter in information other than (Last,First name) it will not allow me.  I have looked in all the settings and cannot figure out why the forms will not allow any entry information into certain fields.  The only thing I am thinking is that it is a relationship issue.  Attached are my relationships and my form.  It allows me to enter in Dusm_Last and First, but all other fields remain blank.  I have posted previous questions regarding the relationships of this DB and I think they should be fine now.  Let me know of any suggestions.  Thanks.
Relationship3.PNG
Form.PNG
0
Comment
Question by:K K
  • 8
  • 7
  • 3
  • +2
22 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40371735
Without a sample database and a very clear explanation of what this system is doing, ... it will be difficult for us to get you a targeted solution.

A first guess might be to temporarily get rid of the "Join Types" in your relationships unless you are *absolutely sure* you need them in all situations.
0
 
LVL 57
ID: 40371744
In short, because of the relationships you have defined, it means that other records must exist first.

 So let's take the field interview.

 Your relationship says that an interview can have more then one subject (which btw I don't believe is correct), so you should have a main / subform combination for the data entry.   The main form would be about the interview, and the subform would list each of the subjects.    This represents the one to many, or parent and child relationship.

 The other thing your dealing with is look up tables.  In other questions, you indicated that an officer would conduct an interview.   So how a normal form for this would be structured is:

1. A form tied to the interview table.

2. A combo control on the form to choose an officer.    The drop down would display all the officers from the officer table, and when one was chosen, it would store the OfficerID in your interview record (remember, we talked about a copy of a records primary key being stored in another table as a pointer is called a foreign key?  this is it).

3. What if an officer was not in the list?   A NotInList event would be triggered, allowing you to popup a second form to capture the officer record.  You'd then close that form, come back to the interview, and now be able to select that new officer in the combo control.

Make sense?

Jim.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40371884
What is the record source for the referenced form?
0
 

Author Comment

by:K K
ID: 40372215
Here is a overview of my DB: I am creating a DB to record all Field interviews  at a Police Department between Officers and subjects.  Attached are my current relationship tables.  1 Officer will interview 1 subject per interview.  There can be many subjects interviewed by an officer, but they will all have their own record of info.  The officer data isn't as important as much as the collection of subject information, but still would like to track it.  

Jim, as you advised I created a form for Interview field and added the drop down control for officers in one form.  How can I put in the subject info on the same form?  When I try this it allows for me to enter in Interview and officer data, however will not allow me to enter in new data into the subject fields.  If I enter in the data for the interview and officer and come back to the record it then allows me to enter in the Subject field data, but not as an initial entry.  Do I need to enter in the Officer info and then have the form route me to another form to record the subject data?  I would like to avoid switching forms as this seems to make the data entering process more time consuming.  Thanks for you help
relationship3.PNG
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40372370
To start with, to my current understanding, you need 3 tables:officers, subjects, and interviews. Table primary keys are shown underlined. Not sure about the other FID table.  A last statement is added about it.

officers (oid, olname, ofname,...) - to hold unique officer record (1 link)
subjects (sid, slname, sfname, ...) - to hold unique subject record (1 link)
interviews (ioid, isid, idate, ....) - to hold multiple officer-subject records (M:M) link.

Relations:
oid ---(1:M)--> ioid , isid <---(M:1)--- sid

I guess FID (fid, ...), and interviews(ioid, isid, ifid, ....)
Another relation can be drawn: fid ----(1:M)---> ifid
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40373000
Three of your relationships are incorrect.
If an interview can be for one subject, then the SubjectID should be in the interview table and the interview ID should be removed from the subject table.  If one interview can be for multiple subjects, you need what we call a junction table between interview and subject and that will allow you to assign multiple subjects to an interview.
The second relationship error is that you have FID_Num in the interview table but you should have FidID.  Relationships ALWAYS include the primary KEY from the 1-side table, NEVER a data field.
The third problem is that SubjectID does not belong in tblDUSM.

Observation,
The original names for the officer and case tables were understandable.  The new names are not.  They require specific system knowledge to be decipherable.  

As Jim already mentioned, the interview table is the center of this application's universe.  The other tables are lookup/reference tables.  The "lookup" fields should be defined as combos - OfficerID, SubjectID, CaseID.  These combos will store the Foreign key  (DSUMID, SubjectID, FidID) and show some meaningful text field.  You can have the form show additional fields from the lookup tables by including those tables in the query that is used for the interview form.  But, when you include fields from lookup tables, you should usually lock them.  You don't want people to accidentally change them.  So, when they are editing the interview record, you don't want them to accidentally change the subject last name from Smith to Jones because they think they are choosing a different subject.
0
 

Author Comment

by:K K
ID: 40374221
Ok Attached I have my edited relationships that seem to work and display the information correctly.  Now my question about using one form for my data entry with multiple table fields.  I keep getting a message for some of the fields that says "cannot add record; join key of table 'tblDUSM' not in recordset.  I also get this for the tblFieldInterview fields.  When I run my data thru a query it seems to come up fine.  I noticed that when I updated the fields that were allowed (tblsubject and tblfid fields), I could then go back to the fields in tblDUSM and tblFieldInterview and add data to those fields successfully.  Is there code that I could build on the "afterupdate" tab in field properties that would tell access to go to the previous record so data could be entered rather than it registering as an all new record thus giving the error message?

For clarification in one form I entered in fields from all four of my tables in the attachment.  The form allows me to enter data in all Fields in tblSubjects and tblFID, however I get the error message when trying to enter in data into the fields pertaining to tblDUSM and tblFieldInterview.  If I save a record with info typed into the subjects and FID fields, I can then go back to the previous record and it now allows me to enter data in the tblDUSM and tblFieldInterview.  Suggestions?

I tried adding in subforms for the separte fields, but it came out with the same results and is not as user friendly.  

The DUSM is now the officer field and FID is the case number field.
Seems-to-work.PNG
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40374267
In your application, the interview table is the center of the universe.  All the other tables have 1-many relationships with it.  That means that you have the relationship between interviews and officers backwards.  Remove the interview ID from officers.  This relationship goes the other way.

Remove DSUMID from tblSubject.  You infer DSUMID by linking through the interview table.

When deciding which table to add a foreign key to, think about "how many of these things to I have?".  If the answer is more than one, then the relationship goes the other way.  So how many field interviews does an officer have?  A lot - that should tell you that you can't store them all in a single column so the relationship must go the other way.

We have mentioned several times the possibility that you actually might have a many-many relationship.  If you do, you need an additional table which is frequently called a junction table that will be used to manage the relationships.

For clarification in one form I entered in fields from all four of my tables in the attachment
 You really, really don't want to do that.  It is an accident waiting to happen.  Always lock ALL lookup fields (at least until you have some clue how this all works).  You use combos to select the IDs but you can show additional columns for clarification.  Just don't allow them to be updated from this form.  Create three additional forms.  One for each of the lookup tables and use those to manage adding/updating that data.  If you think about it, it really doesn't make sense to open an interview form to add a new officer.  Nor does it make sense to open an interview form to change an officer's name.  People get married.  They change their names.  However, if you want to change the officer assigned to the interview, you would use the combo to select a different officer.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40374429
I think the relations ought to be like this.
relations
0
 
LVL 57
ID: 40377036
A suggestion:

  Since you seem to be struggling with the design, relationships, queries, and resulting forms as whole, take a step back and do one piece first, then add in additional tables.

  In other words, create a new DB.   Start with only the interview and offices table.   Add a one to many relationship (no outer join).  Enforce RI on the relationship, cascade updates but do not cascade deletes.

 Now create:

1. A form for maintaining the officers record.  Base this form directly on the offices table for now.

2. A form for maintaining the interview record.   Base this form directly on the interview table for now.    Use a combo control to look up the officer.

 You'll only have a few fields for the interview (i.e. interview date and notes taken for example).

3. Then try doing a report for officers and their interviews.

   That will lead you through all the steps you need, but keep everything simple and straight forward and make it easy to ask a question here if you need to.   Once done with that, then go back and add the additional tables needed one at a time.

  Through out the process, keep asking questions here as you get stuck.

  By focusing on one piece at a time, you'll find it easier both in dealing with it and in asking questions here and I think in the long run, the whole thing will move along much faster.

Jim.
0
 

Author Comment

by:K K
ID: 40377968
Jim,
I have created a form for Officers and a form for Interviews, but for example how do I create a form for data entry to show that Officer 1 had an interview on 10/12/2014?  If they are in 2 separate forms it wont know which data is related to each other.   I can't add them both to one form because I will get the same error message as I posted before.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:PatHartman
ID: 40378142
You need to follow Jim's directions EXACTLY.  Create two separate forms.  One bound to each table.

Officers should be entered with the Officer form and you can do that immediately since you have a known set of Officers.  The officers come and go so I would include an Inactive flag so you can leave Officers in the Officer table and yet be able to apply logic in the interview form to not allow inactive Officers to be selected for new interviews but still leave them attached to past interviews.  You ALWAYS have to add a new officier to this table FIRST before you can assign him to any interviews.

Once you have populated the Officer table, you can add an interview.  Choose the officer from the OfficerID combo.  This links the officer to the interview.  Enter the rest of the interview data.

Once you understand how that works we can move on.

Also, you might consider loading the Northwind sample that comes with Access and poking around.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40378227
<<If they are in 2 separate forms it wont know which data is related to each other. >>

 Yes and no.   As part of the interview table, you will have the foreign key field "OfficerID".  So the interview table (for now) will simply look like this:

tblInterviews - One record per interview
InterviewID - Autonumber - PK (Primary Key)
InterviewDate - D/T (Date/Time)
OfficerID - Long - FK (Foreign Key) to tblOfficers

The OfficerID field points to a specific record in tblOfficers by containing a copy of one of the primary keys in that table.  In other words, it serves as a pointer.  

Now how do we fill it; by using a combo control.   A combo control is like a text control in that you are working with a single value, but in its drop down, it can display a list of data from else where.

In this case, we want to display data from the officers table, let the user choose one, and then save the value of the primary key for what they choose in the record here.

So first, let's take care of what it's going to display first.  Set the following:

Row Source Type:  Table/Query
Row Source:  SELECT OffierID, OfficerName, BadgeNumber FROM tblOfficers;
Column Count: 3
Column Heads: Yes
Column Widths:  .75"; 2.5";.75"
Bound Column: 1

Now if you try that (and please correct the fields names in the row source if I don't have them right), you'll see you get a list of Officers in the drop down.   In the above, we let the officerID show, but normally you don't and we'll change that in a minute.

So now your user can choose an officer, but how does the OfficerID end up getting saved?   We're going to set one more property:

Control Source:  OfficerID

This along with the Bound Column tells the control which value to save from the Row Source and where to save it.

Since our row source has the OfficerID first, that's the value it will grab and it will get saved in the OfficerID field of our interview record, which is what were currently working with.

 Now note on the above that Rowsource can be a table or a query.   I gave you a SQL Statement (which is what a query really is - a saved SQL statement along with some other properties), but you can simply use a query name if you want.

In fact if you click there, then off to the right on the builder button ("..."), you'll see you get brought to the query designer.  If you save there, the Row Source will get updated to that name.

 So let's make one more change.  Go ahead and do that (click the builder button) and add a sort on the Officer's name.

 Now in your combo control, change the column widths to:

Column Widths:  0"; 2.5";.75"

what we've done is now make it easy for the user.  The OfficerID is hidden from view, and the officers are sorted alphabetically.  That along with Auto Expand set to yes, will make it easy for them to choose an officer.

 I'm going stop here, let you chew through that and see if that clears things up for you a bit.

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40378262
BTW, Pat's suggestion to poke around in the sample DB's such as Northwind is an excellent one as it's a great way to see how things can be "hooked up" to one another and give you a general indea on how to setup certain interfaces.

In this case, we are doing a straight lookup, as will the subject and case will most likely be as well.

Keep in mind that we can display data from other tables with combo's, list, and subforms, but what we want to do is only store a pointer to those other tables.

  Then when we need the data, say for a report, we can write a query that will join the tables together on those pointers so we have access to all the data we need.

Jim.
0
 

Author Comment

by:K K
ID: 40378565
>>So first, let's take care of what it's going to display first.  Set the following:

Row Source Type:  Table/Query
Row Source:  SELECT OffierID, OfficerName, BadgeNumber FROM tblOfficers;
Column Count: 3
Column Heads: Yes
Column Widths:  .75"; 2.5";.75"
Bound Column: 1<<

Am I making these changes in the Officer table or in form properties?  Do I need to add names to the fields in the Officers table so the combo box knows what names to display?  Just a little confused as where these steps are taking me.
0
 
LVL 57
ID: 40378615
<<Am I making these changes in the Officer table or in form properties? >>

 This would be on the interview form, combo control for choosing an Officer.

 For the row source, you need to match the field names that are currently in your officer table.

 I can do up a sample database in the morning if you need it.

Jim.
0
 

Author Comment

by:K K
ID: 40389437
>>So now your user can choose an officer, but how does the OfficerID end up getting saved?   We're going to set one more property:

Control Source:  OfficerID<<

Jim,
When I change the control source of the Combo box to OfficerID it will not allow me to make an entry in the OfficerName field because it is linked to an auto-number (OfficerID).  When I change the control source to Officer name it then allows me to make an entry, but only if a name is already in the drop down.  Now, when I add the interview date field, the property sheet doesn't have the option to row source so I just added the InterviewID and made it not visible.  (seems to work).   Couldn't I just do the same thing with OfficerID, add that field and under properties make it not visible?  
 
I understand the process of needing the ID fields to link all the data together, how how do I incorporate the 2 other tables since they are not supposed to be on the same form?  Do I create an after-update code that pops up another form with the rest of the fields for subject entry?

I know I am not supposed to add all fields to one form, but hear me out.  I created a form with all fields from the 4 tables.  I added all the ID's to link the data and the DB seems to be recording the data just how I want it.  Why would I have issues if it is recording correctly.
0
 
LVL 57
ID: 40391770
<<When I change the control source of the Combo box to OfficerID it will not allow me to make an entry in the OfficerName field because it is linked to an auto-number (OfficerID). >>

  It should be the officer ID in the interview table; that is the record your working with, and that field is a long, so it is updateable.

<<I understand the process of needing the ID fields to link all the data together, how how do I incorporate the 2 other tables since they are not supposed to be on the same form?  Do I create an after-update code that pops up another form with the rest of the fields for subject entry?>>

 You would have other forms.  As I outlined, your officer combo can have a "Not in list" event, which would pop up an add form for the officer record.   You would enter the office info in that form, which would only deal with officer information.  When you were done with that, you would come back to the interview form.  The officer you chose would now be there and you could continue on with the rest of the form.

<<I know I am not supposed to add all fields to one form, but hear me out.  I created a form with all fields from the 4 tables.  I added all the ID's to link the data and the DB seems to be recording the data just how I want it.  Why would I have issues if it is recording correctly. >>

 Simply, it won't work.  The records will not get properly added.  When doing a form for CRUD (Create, Read, Update, and Delete), you want to be working with one table basically.   The only time you use a query to combine more then one table is if you are doing some type of inquiry display.

What you beginning to see is what most don't understand; it takes a lot of work to put an application together even with something like Access where a lot of the work is done for you.

 You need to think in very small, discrete steps and do one step at a time. i.e.

1. A form to maintain officers
2. A form to maintain subjects
3. A form to maintain interviews

4. Now how can I add an officer during an interview?  To I put in the work to create a not in list event, pop up a form, collect the new officer data, save, and then return (best user experience) or do I simply force the user to stop with the interview and go create the officer using #1 from a menu. Then come back here

 and so on.   There's no shortcuts here...it takes a lot of work to put a good well working application together.

Jim.
0
 

Author Comment

by:K K
ID: 40396211
I was able to add the combo box control for my Interview form and it successfully adds and updates my Officer Name.  I have an open form control that brings up my Subject form, however what am I supposed to link the subject info to?  How do I avoid having to enter in redundant data to ensure that all the information is correctly linked up?  What ID needs to be attached to my subject form?  I have tried many different ID's, but nothing seems to work.  Sorry for the hassle on this issue, but this is getting frustrating.   I know this shouldn't be a difficult DB to design especially since I'm sure an excel spreadsheet could capture the info fine.
0
 
LVL 57
ID: 40396888
<< I have an open form control that brings up my Subject form, however what am I supposed to link the subject info to?  >>

 From what you've described, just like the officer, a copy of the SubJect's primary key should be being stored in the interview table.  

<<How do I avoid having to enter in redundant data to ensure that all the information is correctly linked up?  >>

  Once a subject record is created, you simply point to it.

<<What ID needs to be attached to my subject form?  I have tried many different ID's, but nothing seems to work. >>

 The subject records primary key, which will be:

SubjectID - Auto number


 Your interview form should have a combo control which displays the list of subjects from the subject table, and when you choose one, saves a copy of the primary key (SubjectID) in the interview table.   It's no different than the officer.

Just like OfficerID, SubJectID should appear in the interview table.

<<  I know this shouldn't be a difficult DB to design especially since I'm sure an excel spreadsheet could capture the info fine. >>

 I don't see how a spread sheet (the "flat file" approach) could capture it fine.  That's why relational DB's exist; because flat file approaches don't do the job.  Data needs to be stored redundantly.   For example, if you listed one interview after another in each row of the spreadsheet, you would see a subjects name and other attributes repeated over and over throughout the sheet.

 Now let's say the phone number changed; you'd now need to update it on every row.  Not so with a relational design.

 But I have to say, that's why you continue to struggle with this; you keep thinking in flat file concepts.  That was the approach you took to your first form effort in trying to deal with all the fields at once.

 It just doesn't work that way in a relational database and that's not just Access, but any relational DB.  It's a fundamentally different way of doing things than creating a list of data like you do in a spreadsheet.

 Maybe this will help you visualize it:

 Imagine a spreadsheet with multiple sheets.   Sheet 1 is a list officers (name, badge number, etc), Sheet 2 is all the subjects (name, address, phone number, etc), and Sheet 3 are the interviews (date of interview, location, etc).

Now on sheet 3, in the process of entering the interview, you want to indicate the officer.   You switch to sheet 1, look up the officer (if they are not there, you add a new row).   Now you switch back to the interview and construct a reference that row on the first sheet (Sheet 1, row 33 for example). Now with vlookup, you can get the name, badge number or anything else from sheet 1 to display on the interview sheet, without  actually storing the data there.

In a relational design, each of the sheets is one of your tables,  the row number is the "Primary key" for each of the records, and when you create a reference to a record in another tab, that is a "Foreign Key" (a pointer to a record on another sheet).

HTH,
Jim.
0
 

Author Comment

by:K K
ID: 40397544
>> Your interview form should have a combo control which displays the list of subjects from the subject table, and when you choose one, saves a copy of the primary key (SubjectID) in the interview table.   It's no different than the officer.<<

Let me know if this would work.  I have a combo control for officer name that works.  By adding a subject name (combo control) to the interview form, this would generate a SubjectID.  Then, could I have an open form command to fill in the rest of the subjects Info? Doing it this ways seem that I would have to enter in either a subject ID, interviewID, or name so it knows where to reference the rest of the data.  I also see issues with the combo box, such as Subjects with the same name, but different info.  Are the names going to be locked down to an ID?  

My other thought is that I already have a combo box for my officer names in the interview form.  If I add a combo box for the subjects as well then I am just adding all of the data onto one form, which I thought I was advised against doing.  I already have the subjectID as a foreign key in my interview table.  So to be clear are you advising to have both officer name (as combo control)  and Subject name (as combo control) on the same interview form?

What I have so far is a combo control for Officers on the interview form.  Then an open button that pulls up a subject info form.  I have the subjectID that generates an autonumber, but if I want that data to save then I would have to go back into my interview form and manually enter the autonumber that was generated into the SubjectID field in the Interview table.  That's what I meant by redundant data entry.
0
 
LVL 57
ID: 40397837
<< I have a combo control for officer name that works.  By adding a subject name (combo control) to the interview form, this would generate a SubjectID. >>

  yes, it would allow you to store a copy of the SubJectID in the interview table.

<<Then, could I have an open form command to fill in the rest of the subjects Info? >>

 OK as I've said before, the combo would be showing a list of names and other info in it's drop down.  You type a name that is not in the list.   That triggers the Not In List event.   You have two choices:

1. Message the user that they must first enter the subject record.

2. Your really nice about it, pop up a Add Subject form, let them enter all the info, close the form, and they are back to the subject combo, which has the name selected and they can now tab off of, at which point the subjectID of the newly add subject record is stored as part of the interview record their adding.

<< I also see issues with the combo box, such as Subjects with the same name, but different info.  >>

 You can display anything you want...phone number, city, state, "street name" or whatever.   You could have a button there for a search form, maybe allow them to filter or search in a number of different ways if you wanted to keep the combo simple.  There are numerous ways to handle this.

<<Are the names going to be locked down to an ID?  >>

 Well sure, that's the whole point; when you have a value and look it up in the subjects table, it points to one and only one record, and hence only one name.

<<My other thought is that I already have a combo box for my officer names in the interview form.  If I add a combo box for the subjects as well then I am just adding all of the data onto one form, which I thought I was advised against doing.>>

 No, your not.  Even though you may be *displaying* things like the officer's name, you are not storing that as part of the interview record.  The only thing your storing as part of the interview record is the pointer to the officer data.

<<So to be clear are you advising to have both officer name (as combo control)  and Subject name (as combo control) on the same interview form?>>

 Yes.   Once again, as Pat suggested, poke around in some of the sample databases to see how things like this are done.

<<but if I want that data to save then I would have to go back into my interview form and manually enter the autonumber that was generated into the SubjectID field in the Interview table.  That's what I meant by redundant data entry. >>

 No.  A combo control would do that for you.  Your not typing an ID in anywhere.  In fact, autonumber PK's are almost always hidden.  They mean nothing to the end user in most cases.

Jim.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now