Forms with Dropdown Boxes based on concatenations.

[AJP123]

Hi everyone,



I need to create a database as part of my internship with my church. We want to create a database of church contacts and what congregations they serve at. It has three tables:

• Church details (address, etc.)
• Contact details (address, etc.)
• Contact-Congregation relations

The first two tables have churchID and contactID fields respectively (the key fields). The third table has a relationID field, then churchID and contactID fields linked to the first two tables, along with the roles the contact plays at the particular church.

I've created two forms to add new Church details or Contact details respectively, so I abide by the golden rule to never edit the table directly. However, I'd also like to create a third form, to add entries to the Relations table, with the following properties:

• Instead of entering the ChurchID into the Relations form, I'd like to have a dropdown box containing all existing churches. The entries in the dropdown box should be sorted alphabetically, with the following string: "[Church_Name], [Church_Suburb]". Such a string would uniquely identify the particular Church.
• Instead of entering the ContactID into the Relations form, I'd like to have a dropdown box containing all existing contacts. The entries in the dropdown box should be sorted alphabetically, with the following string: "[Title] [First_Name] [Middle_Name] [Last_Name]". Such a string shoul uniquely identify the particular Contact.
• Entries added on the Relations form can only add entries to the Relations table, not the Contacts or Churches table.

So far, I have created a Relations query which gives me the contact name, the church name, and the roles. I have also created a Relations form which allows me to add new entries to the Relations table, but it still has the ability to add new entries to the Contacts or Churches table, which I do not want. I also don't know how to create the dropdown boxes I specified above.

Any help would be greatly appreciated.

Thankyou!
God bless,
Andy.

[jzwp11]

Welcome to the forum!

Instead of entering the ChurchID into the Relations form, I'd like to have a dropdown box containing all existing churches. The entries in the dropdown box should be sorted alphabetically, with the following string: "[Church_Name], [Church_Suburb]". Such a string would uniquely identify the particular Church.

You would create a query as follows

SELECT ChurchID, [Chuch_name] & ", " & [Church_Suburb] as Church
FROM tblChurch
ORDER BY [Chuch_name] & ", " & [Church_Suburb]

You would then base your dropdown box on the above query. You would bind the combo box to the Church_ID field in the forms' underlying record source. To show the text rather than the ID value, you have to adjust the column widths property of the combo box. The first field with a non-zero width is the one the user will see when they migrate away from combo box after they make a selection. So given the above query, the column widths property should look like this: 0",1" (or some other value that is not zero for the second field). BTW, the ChurchID field uniquely identifies the record since it is the key value.

Instead of entering the ContactID into the Relations form, I'd like to have a dropdown box containing all existing contacts. The entries in the dropdown box should be sorted alphabetically, with the following string: "[Title] [First_Name] [Middle_Name] [Last_Name]". Such a string shoul uniquely identify the particular Contact.

You would use the same approach here as above. Create a query:

SELECT contactID, [Title] & " " & [First_Name] & " " & [Middle_Name] & " " & [Last_Name] as Contact
FROM tblContactDetails
ORDER By [Last_Name], [Middle_Name], [First_Name]

The ORDER BY clause assumes you want to list the people alphabetically by last name rather than first name, if that is not what you need, just adjust the ORDER BY clause to what you need.

Entries added on the Relations form can only add entries to the Relations table, not the Contacts or Churches table.

I believe that you have to set both combo boxes' limit to list property to Yes in order to accomplish this.

I would typically use a form/subform design with the main form based on either the church table or the contact table with a subform based on the Contact-Congregation relations table.

...along with the roles the contact plays at the particular church.

Based on what you say above, a person can play many roles, so that describes a one-to-many relationship. Further, I assume that a role can apply to many people, so that would be another one-to-many relationship. To capture this you would need 2 more tables.

A table to hold all possible roles:

tblRoles
-pkRoleID primary key, autonumber
-txtRole

tblContactCongregationRoles
-pkContactCongRoleID primary key, autonumber
-fkrelationID foreign key to Contact-Congregation relations table
-fkRoleID foreign key to tblRoles

[AJP123]

Welcome to the forum!

You would create a query as follows

SELECT ChurchID, [Chuch_name] & ", " & [Church_Suburb] as Church
FROM tblChurch
ORDER BY [Chuch_name] & ", " & [Church_Suburb]

Done. SQL ftw! Thankyou for all your advice, by the way. Sorry I haven't replied to this sooner, I've been crazy busy with uni and work.

You would then base your dropdown box on the above query. You would bind the combo box to the Church_ID field in the forms' underlying record source. To show the text rather than the ID value, you have to adjust the column widths property of the combo box. The first field with a non-zero width is the one the user will see when they migrate away from combo box after they make a selection. So given the above query, the column widths property should look like this: 0",1" (or some other value that is not zero for the second field). BTW, the ChurchID field uniquely identifies the record since it is the key value.

Not sure how to do this - could you walk me through it? I'm very much an Access newbie

You would use the same approach here as above. Create a query:

SELECT contactID, [Title] & " " & [First_Name] & " " & [Middle_Name] & " " & [Last_Name] as Contact
FROM tblContactDetails
ORDER By [Last_Name], [Middle_Name], [First_Name]

The ORDER BY clause assumes you want to list the people alphabetically by last name rather than first name, if that is not what you need, just adjust the ORDER BY clause to what you need.

That was easy - thankyou.

I believe that you have to set both combo boxes' limit to list property to Yes in order to accomplish this.

I would typically use a form/subform design with the main form based on either the church table or the contact table with a subform based on the Contact-Congregation relations table.

Based on what you say above, a person can play many roles, so that describes a one-to-many relationship. Further, I assume that a role can apply to many people, so that would be another one-to-many relationship. To capture this you would need 2 more tables.

A table to hold all possible roles:

tblRoles
-pkRoleID primary key, autonumber
-txtRole

tblContactCongregationRoles
-pkContactCongRoleID primary key, autonumber
-fkrelationID foreign key to Contact-Congregation relations table
-fkRoleID foreign key to tblRoles

Could you elaborate on this, if that's okay? I've never had any experience with subforms, and I can't really see the point of two more tables. A contact will have particular roles at their given church, so the contact-congregation relation table contains the autoID of the relation, the autoID of the contact, the autoID of the congregation, and checkboxes for whether or not the contact plays a particular role at a particular congregation.

Thankyou heaps =)

[jzwp11]

Not sure how to do this - could you walk me through it? I'm very much an Access newbie

I generally use the combo box wizard; it walks you through step by step to get everything set up as I described. Please give it a try and if you still have issues please post back. If you alread had a combo box set up, just delete it and set it up again using the wizard.

Could you elaborate on this, if that's okay? I've never had any experience with subforms, and I can't really see the point of two more tables. A contact will have particular roles at their given church, so the contact-congregation relation table contains the autoID of the relation, the autoID of the contact, the autoID of the congregation, and checkboxes for whether or not the contact plays a particular role at a particular congregation.

Since you say that a contact can have particular roles at a given church that is a one-to-many relationship; if a person only played 1 role and only 1 role then you would not need the tblContactCongregationRoles I suggested. As to tblRoles, it will help you define the roles that are pertinent to your application and can grow as your database expands (just add a new record for a new role). The problem with checkboxes of various roles in a table is what happens if & when you need to add a new role? You would have to redesign your table and all associated forms, queries and reports! That is not something I would like to do once I deploy the database.

[AJP123]

I generally use the combo box wizard; it walks you through step by step to get everything set up as I described. Please give it a try and if you still have issues please post back. If you alread had a combo box set up, just delete it and set it up again using the wizard.

That worked, thankyou =). However, when I view my relations records using the form, I don't see the church name or congregation name fields for the record in the dropdown box. Any suggestions as to why this is the case?

Since you say that a contact can have particular roles at a given church that is a one-to-many relationship; if a person only played 1 role and only 1 role then you would not need the tblContactCongregationRoles I suggested. As to tblRoles, it will help you define the roles that are pertinent to your application and can grow as your database expands (just add a new record for a new role). The problem with checkboxes of various roles in a table is what happens if & when you need to add a new role? You would have to redesign your table and all associated forms, queries and reports! That is not something I would like to do once I deploy the database.

That makes sense, thankyou =)

[jzwp11]

That worked, thankyou =). However, when I view my relations records using the form, I don't see the church name or congregation name fields for the record in the dropdown box. Any suggestions as to why this is the case?

Do you mean that you do not see the information in the dropdown itself or after you make a select from the drop down?

If you mean that you do not see the information after you make a selection, the best way to handle that is to add a couple textbox controls to your form and set the control source to the column of the combo box that has the information you want using an expression.

You will need to refer to the Row Source property of the combo box. You should see something that starts with the word SELECT followed by several of your field names.
Now just to add a little twist, Access starts counting those fields beginning at zero not 1.

For example, let's say that the information you want to see in the new textbox I suggested is the third field in the SELECT clause, your expression for the control source of the textbox would look like this:

=comboboxname.column(2)

(Note: you will have to supply your actual combo box name in the expression)

[AJP123]

Do you mean that you do not see the information in the dropdown itself or after you make a select from the drop down?

If you mean that you do not see the information after you make a selection, the best way to handle that is to add a couple textbox controls to your form and set the control source to the column of the combo box that has the information you want using an expression.

You will need to refer to the Row Source property of the combo box. You should see something that starts with the word SELECT followed by several of your field names.
Now just to add a little twist, Access starts counting those fields beginning at zero not 1.

For example, let's say that the information you want to see in the new textbox I suggested is the third field in the SELECT clause, your expression for the control source of the textbox would look like this:

=comboboxname.column(2)

(Note: you will have to supply your actual combo box name in the expression)

I mean that whenever I view a record using the arrows at the bottom of the form in Form View, the name of the church and the name of the contact (in the "church" and "contact" combo boxes [dropdown boxes] respectively) don't appear.

Also, using the form, I'd like to be able to select a church or contact in the dropdown/combo box, and view the first Relations record with that church or contact in it. However, when I click on an entry in the dropdown/combo box, I get the following error message: "Field 'church'/'contact' is based on an expression and cannot be edited". How do I get around this?

Finally, given your proposed table structure change to my database, is it possible to maintain the checkbox look of the associated form, where adding the roles a person has at a given church is as simple as checking or unchecking checkboxes on their relation form? And where adding a new role to the roles table will automatically add a new field to the relation form? Or what would be a better way to design such a form, so people can easily add new relation entries to the database?

Thankyou heaps for your time,
Andrew.

[jzwp11]

It sounds like your form is based on a query that uses more than one table. Would it be possible for you to zip and post a copy of your database with any sensitive data removed and then we can see what your form is doing.

And where adding a new role to the roles table will automatically add a new field to the relation form?

Automatically adding a field/control to a form each time a new role is added requires a redesign to the form each time. This might be able to be done via code, but I have never attempted it. You typically do not want to do this once an application is deployed, that is why it is better to treat new roles as records not fields.

[AJP123]

It sounds like your form is based on a query that uses more than one table. Would it be possible for you to zip and post a copy of your database with any sensitive data removed and then we can see what your form is doing.

Here we go: Blueprint Ministry Contacts Database Template.zip

Automatically adding a field/control to a form each time a new role is added requires a redesign to the form each time. This might be able to be done via code, but I have never attempted it. You typically do not want to do this once an application is deployed, that is why it is better to treat new roles as records not fields.

Fair enough - I'm still learning the limitations of databases. How would you go about creating a form for easily creating new records in the ContactCongregationRoles and Roles tables?

[jzwp11]

OK, from looking at your database and specifically the tblRelations table, we are not on the same page. Your tblRelations has a series of yes/no fields. Could you explain what these are and how they relate to a contact? Are you trying to define for what program a contact is responsible at a particular church? Why are some fields worded as questions? Are you also trying to define what programs are available at a church not just the contact who is responsible for that program?

[AJP123]

OK, from looking at your database and specifically the tblRelations table, we are not on the same page. Your tblRelations has a series of yes/no fields. Could you explain what these are and how they relate to a contact? Are you trying to define for what program a contact is responsible at a particular church? Why are some fields worded as questions? Are you also trying to define what programs are available at a church not just the contact who is responsible for that program?

To answer your questions in order:

• The yes/no fields on tblRelations indicates whether or not a particular contact has the given role at a particular church.
• The fields are worded as questions because I've been sloppy with my field naming - I assume this is bad practice in general?
• In general, not all programs will be available at a given church.

To try and summarise how I should design the database:

Tables:
tblContacts
tblChurches
tblRoles
tblRelations

Queries:
queryChurchName
queryContactName

Forms:
frmContacts
frmChurches
frmRoles

What else should I include? Having a Roles table means that I don't need to add more fields whenever new programs become available for churches - that makes sense. How should I design a form so the roles a contact has at a particular church can be edited (which are stored in tblRelations)?

Thanks for your time, and my apologies for taking a while in getting back to you each time.

[jzwp11]

Setting up the tables is the most important part of a relational database, the forms and queries can wait. You need define the programs as records not fields. Based on your current table, a church can have many programs (one-to-many relationship). Now can a program with the same name occur an more than 1 church?