How can I make it

[david.paton]

I have a database made that has a form which shows the type of care that carers can provide. At the moment I have some checkboxes but I had it recommended to me that I should make it combo boxes so it allowed the addition of extra types of care but how would I do this?

I thought I could have a continuous combo box that allowed me to select a type of care from a drop down list, but to have it in table form so it allows various types of care to be selected. So, in a table format, but with a drop down box for additional types of care.

I know that this is bad table design, using lookup fields in a table, but I wasn't sure how else to do it. Could I have some ideas please?

I have attached my db. The checkboxes are located halfway down in frmCarers.

Thanks

 

[Galaxiom]

Carer to CareType is a many-to-many relationship. It is implemented with a junction table.

The data is managed through a subform.

Have a look at the example database in post 3 of this thread. The code stuff in is more than you need but it does include a technique that shows how to get virtual records to appear in the subform.

Filling in any of the fields in the subform creates a record. You could use a checkbox. Note however the record won't be deleted by unchecking. That would required either directly deleting it or using code.

 

[david.paton]

I can't see how that would allow multiple selection of items from a list.

 

[Galaxiom]

The list is the subform. The records you can see in there come from the another table (Subjects if I remember correctly) but are not in the junction table until a value is entered in one of the fields.

Look closely at the RecordSource of the subform and you be able to see how it is done.

 

[david.paton]

I still don't see how I could apply what is in the example subform to what I want. All the record source says on the subform is StudentGrades, which is the name of the subform, so that has nothing to exactly study.

 

[Galaxiom]

StudentGrades is the subformcontrol's SourceObject. Click on the form inside the control and you will see its RecordSource which is a query.

That query uses an outer join which is the key to getting the virtual records to appear.

 

[david.paton]

I see the record source is a query and I opened it and tried to make sense of the sql. I don't really know much about coding or sql so I didn't understand it.

SELECT Students.StudentID, Grades.InstructorID, Grades.ItemID, Grades.Quarter, Grades.Grade, Grades.Notes
FROM Students LEFT JOIN Grades ON Students.[StudentID] = Grades.StudentID
WHERE (((Students.Active)=True))
ORDER BY Students.StudentName;

I understand in sql, you can select a field from a table by using select and From but my understanding is very limited and it pretty much stops there.

Sorry to sound really dumb but I have no idea what the above code is doing so if someone could explain this to me, I would really appreciate it!