Help with subform datasheet view looks good(the way I designed); but the form view in my main form looks like a datasheet. (1 Viewer)

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
I'm designing an employee database. I'm fairly new to Access; even though I created a very good database approximately 20 yrs ago. I've found working within a database is not the same as designing one. Now that my old one is no longer needed due to new job; I have created another one. I have 8 tables. I have created a main staff table with 7 tabbed subforms within my main form.

the problem I have right now is that out of 7 subforms, one of them will not work correctly as all the others do. I designed my subform the way I want it to appear in form view. Within the main form (design view) this 1 subform looks exactly the way it should. However, when i get into form view (where I want to enter my data) it looks like datasheet view. I looks like this subform is in reverse of all my other subforms.

Can someone please help me? It seems all subforms match all formatting - I've clicked each one of my subforms and all properties seem to match including the one that looks like data sheet in form view.

Sorry I posted this in my introduction before I read first what I was supposed to do.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
What is the value of "Default View" property?
What is the value of "Allow Datasheet View"?
What is the value of "Allow Form View"?
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
What is the value of "Default View" property?
What is the value of "Allow Datasheet View"?
What is the value of "Allow Form View"?
Default view is datasheet
Allow data sheet view - No
allow Form view - yes
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
What is the value of "Default View" property?
What is the value of "Allow Datasheet View"?
What is the value of "Allow Form View"?
OMG!!!! It worked....Thank You!!!! If I would've known about this forum, i would've joined along time ago. I spent so many hours trying to figure this out. and it was so simple. You will see usually this my dilemma - the small things I just can't get most of the time. 😊
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
Ok that work...Now there 1 more issue. all other subforms have 1 record of 1, except for this one subfomr again it has 1 to 136 (total number of employees). so it doesn't match the main form data like all other subforms.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
If the subform is not linked to the main form then the subform will not filter the records. Normally the main form shows the Parent records and the subform the child records related to the Parent records. This is done in the properties Link Master Fields and Link Child Fields. So if the parent form is employees and the subform is Orders and they are linked by employingID, then the subform will show all the orders assigned to that employee. When you create a new order it will link it to that employee. Is that what you mean? What is the relation of the subforms to the main form?
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
AGAIN!!!! Thank you so much!!! I had trouble finding where to link in the properties but I finally found it and that was the issue. I thought the links would've automatically linked together when I add a new page to the main form. 👏:)
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
Ok ...all looked good until I tried to add a new employee. Before starting I made sure in all my tables this new employee did not exist. It did let me add her employee # (Primary key) and her name (this is what consists my Main form. Within this main form are all my subforms that pertain to all requirements, trainings and other information (7 subfomrs).

However, when I went into my first subforms: (required trainings) I got this error message: "You can not add or change a record because a related record is required in table "Staff Waivered Sites". Now this is just the first subform I tried to enter a new staff. Will this happen in all subforms? this is the first time after creating my full database I tried to add a new staff. So I need to fix this problem so I can add all the staff I need to.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
It is probably the same issue. If your subform is properly linked, not only does it filter but it also adds a foreign key to your child table when you create a new record.
Example. Your first subform is training and that table should have a foreign key relating to an employee. Lets says that field is EMP_ID_FK and in the employee table it is EMP_ID. If you have established relational integrity then you cannot create a child record in the training table unless you relate the EMP_ID_FK to an EMP_ID. Assume you are showing employee 7 in the main table it will create a 7 in the EMP_ID_FK in the child table. But if the linking is not there this will not automatically happen and no value goes in the emp_ID_FK and thus an error message.
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
Ok... How do I fix this? I have the employee Number as primary key on all tables. I've set all tables relationships to match the employee number (primary key). Should I have set it up differently? Please help me...I'm getting really stressed just add new employees into all my subforms (as needed) I am not off to a very good start at all.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
Emplooyee_Number (or some other name) should be the PK in the Employee table. I am assuming your other tables are child 1 to many tables. Example an Employee has many training events.
In the Training table the PK should be TrainingID and likely an Autonumber. It should then also have a foreign key which is not a PK (no key symbol) to relate it to another table. For me I like to suffix my foreign keys with _FK. So in the training table it would be EmployeeNumber_FK. Then in the relationship window you can relate a training even to a person.
I would not stress, it may not require much of a fix except adding a foreign key.

Can you tell us a little about your tables and what they contain? What are the PKs and FKs in them?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
FYI. If you can share your DB (zip it and post it here) using the attach feature, we can answer questions much faster and give more suggestions.
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
Emplooyee_Number (or some other name) should be the PK in the Employee table. I am assuming your other tables are child 1 to many tables. Example an Employee has many training events.
In the Training table the PK should be TrainingID and likely an Autonumber. It should then also have a foreign key which is not a PK (no key symbol) to relate it to another table. For me I like to suffix my foreign keys with _FK. So in the training table it would be EmployeeNumber_FK. Then in the relationship window you can relate a training even to a person.
I would not stress, it may not require much of a fix except adding a foreign key.

Can you tell us a little about your tables and what they contain? What are the PKs and FKs in them?
I think that may be my issue..... No matter how hard I tried I do not understand relationships. All my tables have employee numbers. So I assigned Employee number as the primary key on all tables. Since employee numbers cannot be duplicated I thought that was best. Can I attach my data base for you to look at it or is that too much to ask of you? You have been so helpful I hate to ask you to do too much. You've been such great help so far I don't want you to get sick & tired of me already.
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
I think that may be my issue..... No matter how hard I tried I do not understand relationships. All my tables have employee numbers. So I assigned Employee number as the primary key on all tables. Since employee numbers cannot be duplicated I thought that was best. Can I attach my data base for you to look at it or is that too much to ask of you? You have been so helpful I hate to ask you to do too much. You've been such great help so far I don't want you to get sick & tired of me already.
I thought I would at least send you my relationships of all my tables. I'm getting ready to go home now. However, I will be back on from home to try to get this all figured out tonight. Please let me know what is wrong.
 

Attachments

  • Table relationships for Staff Database - 8.24.20.pdf
    205.6 KB · Views: 220

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
A quick look at this and the issue is that you made this more like a spreadsheet than a database. The problem is more in your table design vice relationships. Without proper table design cannot get proper relationship.

For example imagine you are tracking the actual children for an employee. A flat structure would be.
Code:
EMPID   (if this is a PK only 1 record can be added)
Child1Name
child1BirthDay
child1Gender
Child2Name
child2BirthDay
child2Gender
Child3Name
child3Birthday
child3Gender
....
You do not know how many children so you make 10 groups of fields hoping noone has more than 10. This is flat, there is one record per employee in the child table. This would be hard to build, maintain, and use. However, that is kind of what you are doing.

It should be
Code:
tblChildren
  ChildID
  ParentID_FK
  ChildName
  ChildBirthday
  ChildGender
If employee 7 has 10 kids there are ten records each with a ParentID_FK = 7. If they have 3 kids 3 records and 20 kids 20 records.

Now you have a driver license table. If you are tracking multiple licenses for an employee then you need a seperate table. If an employee just has a single license you can just put that in the employee table. You are not gaining anything by having its own table.

Now what is going to make this a little trickier is that you have many to many relationships. For example an employee can take many training courses, and many employees can take the same course. This is done using an additional table called a junction table. It holds 2 or more foreign keys.

Assume tblEmployees (empID, empName)
1 John Smith
2 Mike Brown
3 Susie Derkins

tblTraining (trainingID, TrainingName)
1 Mandated Reporter
2 Obesity Prevention
3 SIDS

tblEmp_Training
--EmpID_FK
--TrainingID_FK
--TrainingDate
--Certificate (not sure if this is yes/no or an actual attachment)

Although your form uses subforms and nice combo boxes the data is stored like
1 2 1/1/2020 True
1 3 2/2/2020 False
2 1 6/6/2020 True

This shows that John Smith took Mandated Reporter on 1/1 and got a cert, Obes Prev on 2/2 and did not get a cert.
Mike Brown to Mand Rep on 6/6 and got a cert
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
A quick look at this and the issue is that you made this more like a spreadsheet than a database. The problem is more in your table design vice relationships. Without proper table design cannot get proper relationship.

For example imagine you are tracking the actual children for an employee. A flat structure would be.
Code:
EMPID   (if this is a PK only 1 record can be added)
Child1Name
child1BirthDay
child1Gender
Child2Name
child2BirthDay
child2Gender
Child3Name
child3Birthday
child3Gender
....
You do not know how many children so you make 10 groups of fields hoping noone has more than 10. This is flat, there is one record per employee in the child table. This would be hard to build, maintain, and use. However, that is kind of what you are doing.

It should be
Code:
tblChildren
  ChildID
  ParentID_FK
  ChildName
  ChildBirthday
  ChildGender
If employee 7 has 10 kids there are ten records each with a ParentID_FK = 7. If they have 3 kids 3 records and 20 kids 20 records.

Now you have a driver license table. If you are tracking multiple licenses for an employee then you need a seperate table. If an employee just has a single license you can just put that in the employee table. You are not gaining anything by having its own table.

Now what is going to make this a little trickier is that you have many to many relationships. For example an employee can take many training courses, and many employees can take the same course. This is done using an additional table called a junction table. It holds 2 or more foreign keys.

Assume tblEmployees (empID, empName)
1 John Smith
2 Mike Brown
3 Susie Derkins

tblTraining (trainingID, TrainingName)
1 Mandated Reporter
2 Obesity Prevention
3 SIDS

tblEmp_Training
--EmpID_FK
--TrainingID_FK
--TrainingDate
--Certificate (not sure if this is yes/no or an actual attachment)

Although your form uses subforms and nice combo boxes the data is stored like
1 2 1/1/2020 True
1 3 2/2/2020 False
2 1 6/6/2020 True

This shows that John Smith took Mandated Reporter on 1/1 and got a cert, Obes Prev on 2/2 and did not get a cert.
Mike Brown to Mand Rep on 6/6 and got a cert
A quick look at this and the issue is that you made this more like a spreadsheet than a database. The problem is more in your table design vice relationships. Without proper table design cannot get proper relationship.

For example imagine you are tracking the actual children for an employee. A flat structure would be.
Code:
EMPID   (if this is a PK only 1 record can be added)
Child1Name
child1BirthDay
child1Gender
Child2Name
child2BirthDay
child2Gender
Child3Name
child3Birthday
child3Gender
....
You do not know how many children so you make 10 groups of fields hoping noone has more than 10. This is flat, there is one record per employee in the child table. This would be hard to build, maintain, and use. However, that is kind of what you are doing.

It should be
Code:
tblChildren
  ChildID
  ParentID_FK
  ChildName
  ChildBirthday
  ChildGender
If employee 7 has 10 kids there are ten records each with a ParentID_FK = 7. If they have 3 kids 3 records and 20 kids 20 records.

Now you have a driver license table. If you are tracking multiple licenses for an employee then you need a seperate table. If an employee just has a single license you can just put that in the employee table. You are not gaining anything by having its own table.

Now what is going to make this a little trickier is that you have many to many relationships. For example an employee can take many training courses, and many employees can take the same course. This is done using an additional table called a junction table. It holds 2 or more foreign keys.

Assume tblEmployees (empID, empName)
1 John Smith
2 Mike Brown
3 Susie Derkins

tblTraining (trainingID, TrainingName)
1 Mandated Reporter
2 Obesity Prevention
3 SIDS

tblEmp_Training
--EmpID_FK
--TrainingID_FK
--TrainingDate
--Certificate (not sure if this is yes/no or an actual attachment)

Although your form uses subforms and nice combo boxes the data is stored like
1 2 1/1/2020 True
1 3 2/2/2020 False
2 1 6/6/2020 True

This shows that John Smith took Mandated Reporter on 1/1 and got a cert, Obes Prev on 2/2 and did not get a cert.
Mike Brown to Mand Rep on 6/6 and got a cert
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
@sribblett
Not sure if you meant to post a question, but all I see is two quotes of the previous thread.
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
so sorry I didn't get to get back to yu from home last night. I had a family emergency it last past 9pm. I was NOT going to bother youthen. would it be possible for you to write down what you're trying to explain on the table relationship I sent you? I think I understand what you're saying however I'm not so sure how to go about fixing it. A PK needs to have a number in it - no blanks. How about a FK - there doesn't need to be data in that field at all times? I do not have all employees drivers license - this is a new thing that we are doing and it will take some time to get everybody's licenses and everyone does not have one. I don't have to have the PK (Employee #) on all tables? If you could write down on my table relationships and draw lines to each table for relationship for me (I'm a very visual type of person) and at times I have difficulty understanding what I read. I'm not sure what the second row of tables are...I didn't create them. Maybe queries or forms created them? Ok...1st of all I will put License into my Employee main table then work on the others. One thing at a time - i guess. And I thought I was doing such a good job at this on my own...NOT!!!
 

sribblett

Member
Local time
Today, 13:44
Joined
Aug 24, 2020
Messages
89
No I didn't mean to send those other posts. I have completed putting the driver's license table into the Main Staff Table. I have deleted the driver lic. table. So now in relationships for all my tables, how should I do it. I'm still a little confused.
 

Users who are viewing this thread

Top Bottom