• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Adding a combo field to an existing database

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
NP111
Asked:
NP111
  • 7
  • 6
1 Solution
 
mbizupCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
NP111Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mbizupCommented:
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
 
mbizupCommented:
<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
 
NP111Author Commented:
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
 
NP111Author Commented:
Can anybody help?
0
 
mbizupCommented:
Sorry - I missed your last comment.

Can you post a sample of your database with any sensitive data masked or removed?
0
 
NP111Author Commented:
OK I have removed the data and attached the database.
Charity-Contacts--test-.accdb
0
 
mbizupCommented:
Okay - can you breifly explain what I need to do/look for after opening the database (ie: what are the relevant forms,controls...)
0
 
NP111Author Commented:
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
 
mbizupCommented:
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
 
NP111Author Commented:
Many thanks for your patience and assistance with this.
0
 
mbizupCommented:
Glad to  help out :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now