X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Form Interface Example, Access 2016    
post Nov 9 2019, 01:24 PM

Posts: 1
Joined: 9-November 19

I am trying to work out how to build the form that is in the database (From scratch) The form I am looking at is FormAddDonation_Macro_example_1

The database is sent to us by the awarding body as an example of what we have to do. I tried building the form from scratch and added a drop down box attached to a query. When I select donor it is supposed to fill in status field and current donation field but that doesn't happen. I have tried to work it out from the example form but I am stuck. If I can get past this I can then work on the rest of the form. (Or at least I hope I can) I have tried starting with a blank form and using the wizard but I cant emulate the example form

Appreciate any help and advice


Attached File(s)
Attached File  Steve_to_examine.zip ( 393.36K )Number of downloads: 125
Go to the top of the page
post Nov 9 2019, 01:43 PM

UA Admin
Posts: 37,301
Joined: 20-June 02
From: Newcastle, WA

Welcome to UtterAccess.

All Relational Database Applications, such as those built with Access, start with the tables.

Did you create the tables here? Do you understand how they are related? Is this real data about real people or sample data that is made up to illustrate something.

Why do you need to rebuild the form? What's wrong with it? Why can't you use it as is? What changes would make it appropriate?

"The database is sent to us by the awarding body as an example of what we have to do."

What is an awarding body? Why are they asking you to do this? What will be the resulting product?
This post has been edited by GroverParkGeorge: Nov 9 2019, 01:51 PM

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Nov 15 2019, 10:55 AM

Posts: 270
Joined: 27-September 01

There are various problems here, hard to address in one reply. I'll touch on a few.

"Status" is evidently a calculated value, and won't be a field in a normalized set of tables. There are reasons to denormalize data, but I don't know if any of them apply here.

You'll probably want a function that calculates Status, for use anywhere you need to see that value. It might look like this:

Public Function DonorStatus(curTotalDonations As Currency) As String
If curTotalDonations < 26 Then
DonorStatus = "Bronze"
ElseIf curTotalDonations < 51 Then
DonorStatus = "Silver"
ElseIf curTotalDonations < 76 Then
DonorStatus = "Gold"
Else: DonorStatus = "Platinum"
End If

End Function

The above function can be used in a query like this one, which shows the total and status for each donor:

SELECT tblDonation.DonorID, tblDonor.DonorSurname, Sum(tblDonation.DonationAmount) AS SumOfDonationAmount, DonorStatus(Sum([DonationAmount])) AS StatusOfDonor
FROM tblDonor INNER JOIN tblDonation ON tblDonor.DonorID = tblDonation.DonorID
GROUP BY tblDonation.DonorID, tblDonor.DonorSurname;

The above query can be a rowsource for a dropdown list that shows donors. Any column from the dropdown list can be displayed elsewhere on the form, if desired.

Although Status should be a calculated value in general, it does somewhat complicate the question of how you display "old" and "updated" Status values or donation totals on one form. The calculation will be based on whatever is in the table now, which can be considered "old" or "updated" as you choose. "Updated" might include a new donation that isn't saved yet. Or "Old" might exclude the most recently saved donation. You'll have to decide which. Figuring out how to display "old" and "updated" status or total on one form is a task that deserves its own post. A query that shows the "old" total and status might take Sum(DonationAmount) - Last(DonationAmount) from another query that has ORDER BY donationdate.

You may need to learn some VBA syntax. Code in a form that puts a value in a control might say Me!somecontrol.Value = "somethingorother". There are lots of rules like that. Access needs to know whether a given name refers to a control on the form, or a field in a table, or is just a variable.

Do you want to use the DonorID as a visible identifier for donors, sort of like a CustomerID? When generating things like Customer Numbers, people often create their own systems, rather than using an autonumber field. Some of those systems might be in the UtterAccess code library. That big subject has been discussed here before, and deserves its own post.

How many donors will there be? Right now, your table has just over 50 names. When the number goes well past a hundred, the drop-down list will become cumbersome. Then you'll need to design a dialog box for selecting names. The look of that dialog box will depend on the number of names and on any way they might be organized. That subject also deserves its own post.

Databases with names of people can get complicated, especially as the number of names grows, because people are complicated. Different people may have the same name. People tables are also especially prone to have duplicates in them. When deleting duplicate values, it's easy to accidentally delete some of the donation records, or other related child data. Systems can be coded that step in whenever a parent record is about to be deleted, and re-associate child records with a different parent, selected by the user. That subject also deserves its own post.

Commercial software, such as Raiser's Edge, manages donors and gifts. For organizations of a certain size, that's usually a better choice.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    5th June 2020 - 08:15 PM