Skip Sleeper
asked on
MS Access Subform Unique Records Issue
Hi Experts,
I'm trying to create a subform that will list the results of a query with checkboxs that the user can identify certain action items. I've been able to get the subform to create the list but they are not behaving as unique records. If you check a box on record one in the main form, that same box is checked in record two. Attached is the db. Thank you for your assistance!
CiMS-v1.accdb
I'm trying to create a subform that will list the results of a query with checkboxs that the user can identify certain action items. I've been able to get the subform to create the list but they are not behaving as unique records. If you check a box on record one in the main form, that same box is checked in record two. Attached is the db. Thank you for your assistance!
CiMS-v1.accdb
ASKER
Hi Pat,
If I used a combo box, for example, with selections that would eliminate the checkboxes, would that then create unique records in the continuous subform? My idea was that the subform selections would remain bound to the main form.
Thanks!
If I used a combo box, for example, with selections that would eliminate the checkboxes, would that then create unique records in the continuous subform? My idea was that the subform selections would remain bound to the main form.
Thanks!
The control you use is immaterial. What is important is that the control is bound. Bound controls have a ControlSource value that is the name of a field in the form/subform's RecordSource.
ASKER
Hi,
Is there a way to make the checkbox control bound?
Thanks!
Is there a way to make the checkbox control bound?
Thanks!
ASKER
Hi,
I'm trying to figure out why the checkbox control isn't bound. It was created in the table as Yes/No type and its ControlSource value appears set correctly. If I'm understanding you, wouldn't this bind the checkbox to the table/field?
Thanks!
I'm trying to figure out why the checkbox control isn't bound. It was created in the table as Yes/No type and its ControlSource value appears set correctly. If I'm understanding you, wouldn't this bind the checkbox to the table/field?
Thanks!
I wasn't able to look at the db when I answered the question yesterday. I am looking at it now and the checkbox seems to work correctly.
Observations.
1. Labels should contain names that are more user friendly. tbl_AdministrationNotice should be Administration Notice or Admin Notice if you are short on space.
2. Date controls should not use Input Masks. Input Masks don't actually validate data. They just make data entry more cumbersome and they prevent the use of the date picker control which can be very handy.
3. Recreate your DS subform. Use the wizard. A form in DS view uses the associated label's caption as its column name but your forms don't have associated labels so Access has to use the actual column name. See #1
4. I don't understand why a column name is prefixed with "tbl_".
Observations.
1. Labels should contain names that are more user friendly. tbl_AdministrationNotice should be Administration Notice or Admin Notice if you are short on space.
2. Date controls should not use Input Masks. Input Masks don't actually validate data. They just make data entry more cumbersome and they prevent the use of the date picker control which can be very handy.
3. Recreate your DS subform. Use the wizard. A form in DS view uses the associated label's caption as its column name but your forms don't have associated labels so Access has to use the actual column name. See #1
4. I don't understand why a column name is prefixed with "tbl_".
ASKER
When I try it, the checkboxes still appear to be unbound (they stay the same for each main form record). Am I doing something wrong?
1. I normally clean up the form and the labels after I have it working. I've adopted prefixing some fields because I got really messed up once with a junction table.
2. Didn't know about input masks and date picker--thanks!
3. I'm confused about this. Are you referring to the form name or the control names/captions?
Thanks!
1. I normally clean up the form and the labels after I have it working. I've adopted prefixing some fields because I got really messed up once with a junction table.
2. Didn't know about input masks and date picker--thanks!
3. I'm confused about this. Are you referring to the form name or the control names/captions?
Thanks!
I get what you are saying now. I thought you were saying that all visible instances have the same value but you are saying that rec1 has the same value regardless of the parent record.
The problem is that the master/child links are not being set correctly. But as I look at the schema, there are bigger problems. It looks like RoomChecks is the standard list of checks you do for each admission. What you are missing, is a table to hold those checks for a specific admission. So, when you create a new Admission record (Form's AfterInsert event), you run an append query that copies the records in the RoomChecks table and appends them to the new relation table. This append query needs to include the PK of the newly created admission record.
The tblAdmissionRoomChecks table has all the same fields as the RoomChecks Except:
1. RmChecksID is a FK in this table and is defined as integer rather than autonumber
2. AdminRmCheckID is a new field defined as autonumber and becomes the PK in this table
3. Add AdmissionNoticeID as a FK to tbl_AdmissionNotice.
The append query must populate AdmissionNoticeID with the value of the autonumber for the new Admission record.
Once you make that change, you can set the master/child links and then Access will keep the form/subform sychronized.
Remove tbl_RoomChecksID_FK from the Admission table. This relationship is 1-M and so the pointer goes in the child table rather than in the parent table.
If you finish your relationship diagram, I'll look at the rest of the schema.
The only objection I have to your names is the tbl_ prefix. It has no meaning and just makes for more typing. Also, be careful when using simple unqualified words as column names. I don't see any problems in the tables I can see in the diagram (except perhaps First and Last since those are SQL Functions) but it is very easy unless you have memorized the list of thousands of words reserved by VBA, Access, and SQL, to use a column name that will conflict with something. Common problems are words like Year, Month, Description, First. Access allows you to use most reserved words as column names, although it does give you warnings about some of them. This issue then moves to using them in VBA and SQL where you can run into conflicts. Since memorization was never my strong suit, I long ago adopted the self defensive mechanism of always making compound words. I've never run into a reserved word that was compound. Doesn't mean they don't exist though. I use FirstName, SaleDate, VisitDesc, for example.
The problem is that the master/child links are not being set correctly. But as I look at the schema, there are bigger problems. It looks like RoomChecks is the standard list of checks you do for each admission. What you are missing, is a table to hold those checks for a specific admission. So, when you create a new Admission record (Form's AfterInsert event), you run an append query that copies the records in the RoomChecks table and appends them to the new relation table. This append query needs to include the PK of the newly created admission record.
The tblAdmissionRoomChecks table has all the same fields as the RoomChecks Except:
1. RmChecksID is a FK in this table and is defined as integer rather than autonumber
2. AdminRmCheckID is a new field defined as autonumber and becomes the PK in this table
3. Add AdmissionNoticeID as a FK to tbl_AdmissionNotice.
The append query must populate AdmissionNoticeID with the value of the autonumber for the new Admission record.
Once you make that change, you can set the master/child links and then Access will keep the form/subform sychronized.
Remove tbl_RoomChecksID_FK from the Admission table. This relationship is 1-M and so the pointer goes in the child table rather than in the parent table.
If you finish your relationship diagram, I'll look at the rest of the schema.
The only objection I have to your names is the tbl_ prefix. It has no meaning and just makes for more typing. Also, be careful when using simple unqualified words as column names. I don't see any problems in the tables I can see in the diagram (except perhaps First and Last since those are SQL Functions) but it is very easy unless you have memorized the list of thousands of words reserved by VBA, Access, and SQL, to use a column name that will conflict with something. Common problems are words like Year, Month, Description, First. Access allows you to use most reserved words as column names, although it does give you warnings about some of them. This issue then moves to using them in VBA and SQL where you can run into conflicts. Since memorization was never my strong suit, I long ago adopted the self defensive mechanism of always making compound words. I've never run into a reserved word that was compound. Doesn't mean they don't exist though. I use FirstName, SaleDate, VisitDesc, for example.
ASKER
Hi Pat,
I really appreciate all the work you're doing to help and educate me! I'll work on the new relationships tomorrow and let you know when its done. Thanks!
I really appreciate all the work you're doing to help and educate me! I'll work on the new relationships tomorrow and let you know when its done. Thanks!
ASKER
Hi Pat,
This is very cool-I've been trying to figure this type of form out for a long time-THANKS! I can't figure out why the append query is adding all the room check records for all the admission form records each time a new admission form record is created. Do I need an update query in there somewhere?
Thanks!
This is very cool-I've been trying to figure this type of form out for a long time-THANKS! I can't figure out why the append query is adding all the room check records for all the admission form records each time a new admission form record is created. Do I need an update query in there somewhere?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
It looks to me like the correct tables are selected. Here's the SQL:
INSERT INTO tbl_AdmissionRoomChecks1 ( CheckBoxForIssue, RoomCheckItem, Comments, AdmissionNoticeID_FK )
SELECT tbl_RoomChecks.CheckBoxFor Issue, tbl_RoomChecks.RoomCheckIt em, tbl_RoomChecks.Comments, tbl_AdmissionNotice.tbl_Ad missionNot iceID
FROM tbl_AdmissionNotice, tbl_Departments INNER JOIN tbl_RoomChecks ON tbl_Departments.tblDeptID = tbl_RoomChecks.tbl_DeptID_ FK;
Thanks!
It looks to me like the correct tables are selected. Here's the SQL:
INSERT INTO tbl_AdmissionRoomChecks1 ( CheckBoxForIssue, RoomCheckItem, Comments, AdmissionNoticeID_FK )
SELECT tbl_RoomChecks.CheckBoxFor
FROM tbl_AdmissionNotice, tbl_Departments INNER JOIN tbl_RoomChecks ON tbl_Departments.tblDeptID = tbl_RoomChecks.tbl_DeptID_
Thanks!
ASKER
Hi Pat,
I just figured it out--I changed the append query to return the highest value in the ID field before appending. This limits the append to just the rows I wanted.
Thanks for helping me out!
I just figured it out--I changed the append query to return the highest value in the ID field before appending. This limits the append to just the rows I wanted.
Thanks for helping me out!
highest value in the IDIt would be better to get the ID from the form. That way there won't be a problem if multiple people are adding records at the same time.
Hi Pat Hartman,
I have a similar situation, and I created a bounded checkbox to the table. But I am experiencing problems where two people are working on the form at the same time the checked and unchecked records are being mixed
I have a similar situation, and I created a bounded checkbox to the table. But I am experiencing problems where two people are working on the form at the same time the checked and unchecked records are being mixed
Please post this as a new question and delete it from this thread and we'll be happy to help you.
When personA updates a record, the record is updated for all people so it makes sense that there would be confusion unless you do this differently. We're going to have additional questions such as how the users are selecting records. Are they doing it one at a time? Please include the answer in the new thread.
When personA updates a record, the record is updated for all people so it makes sense that there would be confusion unless you do this differently. We're going to have additional questions such as how the users are selecting records. Are they doing it one at a time? Please include the answer in the new thread.
Even though it looks like separate instances of a form, each record in a continuous subform (or DS subform) is actually sharing the same set of properties. Since your checkbox is unbound, it can have only a single .value property. When you check one row, all rows show checked. When you uncheck it, all rows show unchecked.