Solved

Adding a combo field to an existing database

Posted on 2011-09-26
14
191 Views
Last Modified: 2012-05-12
I recently downloaded a Charitable Contributions database (not web version) which seems ideal to support a small charity. All has gone well and I now want to add in a new combo box to a form so that I can record whether a contributor has allowed their donation to receive 'Gift Aid' or not. I ideally want to add a combo box with "Yes, No, or Don't Know" as the only options although even a Yes/No box would do.
The problem I am finding is that this information needs to be linked to one or more tables (one would do). For example my 'Contributors Table'. This appears to be linked to a Query called 'Contributors Extended'. Two forms hold individual details: 'Contributor Details' and a list of all contributors, 'Contributor List subform'.
So my problem is where to start?  What I have tried so far is to create a new lookup field called 'GiftAid' in  'Contributors Table' and alter the query  'Contributors Extended' so that it displays this field. I then create a combo box using the wizard in both forms but when I return to Form View the data is never displayed correctly or no data displays or I get a lengthy drop down box with either no data or data from the FirstnameLastname field. Please help! Many thanks.
0
Comment
Question by:NP111
  • 7
  • 6
14 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36598026
Are you simply having trouble populating the drop-down list options?

If it is limited to those three values:

- Open your form in design view
- Right-click on the cobo box and select "Properties"
- Under the Data Tab, select "Value List" for the rowsource type
- For the rowsource property, enter the following text (exactly as shown):

"Yes";"No";"Do not know"
0
 
LVL 84
ID: 36598246
You should first start with: Where should I store this data?

Is the concept of a "Gift Aid" related to the Gift, to the Contributor, or to the Recepient (or perhaps to something else)? This will dictate where you should store the data, and will also dictate exactly how you get that data back to the form level for user interaction.

Miriam has shown you the mechanics of working with a combobox, of course, so you should be clear on that.
0
 

Author Comment

by:NP111
ID: 36598423
If I store this simply as a value list in the form will I be able to create a report easily which lets me know those donors who have gift aided and those who haven't? I thought I needed to link the form to a table somehow?

The concept of Gift Aid is linked to a Contributor not to the donation itself nor the recipient.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36598468
Yes -

The RowSource (the lvalue list in this case) simply defines the options the user has in the drop-down list.  The rowsource can be a list of values like we are discussing here, or it can be data looked up from a table or query.  The RowSource is not bound in any way to the field in your table that holds the actual responses, and can be looked up from tables or queries that are unrelated to your form's recordsource.

The ControlSource is the field in your underlying table that holds the actual data selected by the users.  Sinc this data is stored, it is available for you to use in reports as needed.

(It's worth playing around with this a bit to make the distinction clear)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36598492
<I thought I needed to link the form to a table somehow? >

The form is linked to a table or query through it's RecordSource property  The ControlSource of your combobox, which stores the data that the user selects, will be a field in your form's recordsource.

The combo's RowSource (it's list of options) does not need to be related to your form's underlying data.
0
 

Author Comment

by:NP111
ID: 36712081
I have made the Combo Box as you describe. The forms Record Source is a table called Contributors. Of course the Gift Aid data does not appear in that table as it is Unbound. So do you suggest I do link this Combo Box to the table in someway? I will need to create a Report showing whether Gift Aid is Yes, No or Don't know. How would you suggest I do this?
0
 

Author Comment

by:NP111
ID: 36980952
Can anybody help?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 61

Expert Comment

by:mbizup
ID: 36981293
Sorry - I missed your last comment.

Can you post a sample of your database with any sensitive data masked or removed?
0
 

Author Comment

by:NP111
ID: 36981547
OK I have removed the data and attached the database.
Charity-Contacts--test-.accdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36982419
Okay - can you breifly explain what I need to do/look for after opening the database (ie: what are the relevant forms,controls...)
0
 

Author Comment

by:NP111
ID: 36984870
Open database, choose contacts, new contact and you will see 2 of the combo fields are "Gift Aid" and "Gift Aid acknowledged". Once we have data in these fields how can we produce a report to say who has: Gift Aid = 'Don't Know' or 'Yes' etc. Same for Gift Aid acknowledged.
To do this do I need to ensure when Gift Aid is selected it updates either or both table "Contirbutors" or query "Contributors Extended"?
Once this is done how do I create the report to find for example those people who have said "No" to Gift Aid.
Many thanks.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36985043
It looks like you already have reports for contributors, so you can simply modify those.

The way you have your data set up, the Gift Aid selections are being stored in your contributors table, and is alreay included in the Contributors Extended query that your report is based on.  

To get the gift aid choice showing up in your report, simply copy/paste the combo box from the form to the report.  Even though it is a combo, in report view, it will display as a text box with the chosen text showing.

Once you do that, if grouping records according to Gift Aid is adequate, you can use the reports sorting and grouping feature in design view to organize the data according to Gift Aid.

If you actually need to limit the data (not just group it),  you would have to filter the report.

You could do so with a command button on your form to open the report using criteria for Gift Aid.

The following VBA code would do the trick:

Docmd.OpenReport "All Contributors", acviewpreview,, "GiftAidChoice = 3"

Open in new window


Note that you need to use the value 3, not the text "No" for the criteria, because it is the number that is stored in your contributor's table, not the text.

If you wanted to go a step further, you could create a "Filter Form" for running your report to allow the user to specify which "Gift Aid Choice" gets displayed on the report.

Post back if you want details on that approach.
0
 

Author Closing Comment

by:NP111
ID: 37023371
Many thanks for your patience and assistance with this.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37023387
Glad to  help out :)
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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

14 Experts available now in Live!

Get 1:1 Help Now