Form Design

[Bob87476]

I have two related tables

1 Donor and
2 Donations with fields date, donor, fund and amount

I want a form to display donation details in datasheet view of fund and amount for a selected date and donor

Thanks

 

[jdraw]

What is the question?
How are the tables related?

 

[Bob87476]

The tables are related by DonorID
At the top of the form I want two combo boxes where I select Donor and date and would then see in a datasheet all donations for that donor on that date

I assume it is based on a query

Thanks

 

[Mihail]

Use a form based on table Donor with a subform based on table Donations.

 

[Bob87476]

Use a form based on table Donor with a subform based on table Donations.

Thanks Mihail
It worked for me to be able to select the donor but not the Batch Date so I played around with it and have uploaded my tests.

I have also added another challenge. I am trying to select the Donor either by Last Name or Env Number.

 

[Bob87476]

Sorry
Here is the upload

 

[Mihail]

I'm unable to open it. I use Access 2007 so convert your DB in this format or, better, in 2003.

 

[Bob87476]

I'm unable to open it. I use Access 2007 so convert your DB in this format or, better, in 2003.

I am using Access2013 and when I go to Save AS it says that it is saving it in 2007-2013 format.

If I save it as 2003 format all it does is save the tables and not the forms.

So I am uploading that and see what you get.

While you are looking at that I will compose a post explaining what I have done and the probs

 

[Bob87476]

So I did as you suggested
I Created a form based on Donor with a subform based on Donations.
But I also want to be able to specify the Batch Date.

When I tried to put a combo box on the form to chose the Date it did not give me the option to chose the table Date.

I was using the wizard which you won't have access to but should I be able to link my combo box date to the dateID in the donation subform.


Hope that makes sense.

 

[Mihail]

If I save it as 2003 format all it does is save the tables and not the forms.

All the objects you created are in place. Just you don't know where to look . In the Navigation Pane select All Access Objects.
What is stored in your BatchDate table ? Is here the dates when you receive a donation ?

 

[Bob87476]

Yes the dates are stored in the Batch Date table.
Donations are counted and deposited in Batchs so each donation must have a unique Batch Date.

 

[Mihail]

Hope that this will help you to start.
Feel free to ask anything.

 

[Bob87476]

Hope that this will help you to start.
Feel free to ask anything.

Thanks again it gave me a lot of help.

I realize I was going down the wrong path and had not really explained clearly what I wanted.

So I am uploading another example.

If you open the Form frmQryDonationsByBatchDate

You will see I have a qry as a subform and on the Main Form I have a combo box for the Batch Date.

I want to be able to select a batch Date from the Combo Box and show only those donations with that Batch Date in the subForm.

It seems like I may need some VBS code to enter that Batch Date as a Parameter criteria for the query.



Thanks again.

 

[Mihail]

I showed you how your tables should look.
If you think that your approach is better than my.... inform me, please, when you will start to have troubles with this DB. Only INFORM me. Not ask for help.

 

[Bob87476]

I am sorry that I offended you by posting in a different forum.
I won't do it again.

This is exactly what I asked for and you have kept the solution very simple.

 

[Mihail]

I am sorry that I offended you by posting in a different forum.

It is why I love AWF (this forum). Because they don't remove threads because what a poster say is not agreed by a moderator. Because I can provide links to other forums. Because I can ask on other forum without someone to feel offended himself.

So, if you have a better solution from someone else is OK. And if you share with us we'll thank you.

I said what I said only because I am sure that you will fall in troubles if you will use actual setup for your DB (I refer to the unnecessary table where you store dates). And I anticipate the time when either me, either others, can't help you anymore.

So feel free to ask me what you wish. On this forum (as well as in other forums) there are posters that are a lot more skilled than me. A lot more skilled than I can be. So... where is the problem to ask twice if a solution not fit (or seems to not fit) your needs ?!

 

[Bob87476]

I said what I said only because I am sure that you will fall in troubles if you will use actual setup for your DB (I refer to the unnecessary table where you store dates). And I anticipate the time when either me, either others, can't help you anymore.

Yours is the best solution that I have received.

The reason I have chosen to have a separate table for Dates is because all users will not necessarily have the authority to add a new Batch Date.
My plan is to limit the "Add Batch Date" process with a password.

I do have a few questions about the "Child" on the form.

First. What control did you use to add the Child to the form.

Second. How do I edit the properties of the Child so that existing records can't be changed or new ones added thru this form

 

[Mihail]

What control did you use to add the Child to the form

Subform/Subreport (Design tab - Controls)

Donations are counted and deposited in Batchs so each donation must have a unique Batch Date
(Post #11 - yours)

The reason I have chosen to have a separate table for Dates is because all users will not necessarily have the authority to add a new Batch Date
(Post #17 - yours too)

???????
The user is (as I understand) the operator that enter data in your DB.
When the operator enter a new donation he/she must enter the date for that donation.
So, if he/she has not rights to enter the BatchDate should ask another person to do this (to create a new BatchDate).
Do not sound OK for me.

 

[Bob87476]

Subform/Subreport (Design tab - Controls)

???????
The user is (as I understand) the operator that enter data in your DB.
When the operator enter a new donation he/she must enter the date for that donation.
So, if he/she has not rights to enter the BatchDate should ask another person to do this (to create a new BatchDate).
Do not sound OK for me.

The process is first to create a Batch Date (if not already created)
then set a Batch Date for the "Batch" of entries
Then enter a series of donations with that same date.
Then balance the batch (all entries in that session) against the Bank Deposit.
Finally after the bank has been reconciled close/lock the batch Date so it can't be used again. There must never be two Batches/Deposits with the same date.
I hope that explains why I want to have a separate table of Batch Dates.


Thanks again.

 

[Bob87476]

Yes the dates are stored in the Batch Date table.
Donations are counted and deposited in Batchs so each donation must have a unique Batch Date.

Sorry. Just realized that I should have said that each "Batch of Donations" must have a unique Batch Date.

Now it makes sense why you were confused.