Input Form Design Help

[Alan]

I am setting up a new database with Access 2002 (XP) containing the
following tables:

1. Donors
2. Donations

Each table will contain the following main fields:

Donors:
1. DonorNo - primary key
2. FirstName
3. LastName
4. Address
5. Phone

Donations:
1. DonorNo
2. DateDeposited
3. CheckNo
4. Amount
5. GLAccount

The primary key for Donations will be a combination of the first three
fields listed above.

I need help creating an input form that will allow an inexperienced
user to input new donations. I want the user to be able to type the
first three characters of the DonorNo in order to get a list of
existing Donors from which to choose for the new donation record to be
input. If the Donor already exists, the form should display all fields
for the Donor selected and then allow the user to input the Donation
info for the new record and add the record to the Donations table. If
the Donor is new, the form should allow the user to add the new donor
to the Donors table and then allow the user to input the Donation
information for that new donor and add it to the Donations table.
Having one input form adding records to potentially two different
tables is what I find confusing.

I have several years experience working with Access as a reporting
tool (i.e., extensive use of queries and reports) but very little
experience with input forms, code modules or database application
design. I would appreciate any assistance in explaining how to design
an input form to accomplish the above as well as any specific code
that may be required. Thanks for your help.

Alan

 

[Dale Fye]

Alan,

if your DonorNo is the PK in the Donors table, is it an autonumber field?

I'm not sure why you would want people to enter the first three numbers of
the DonorNo, how will the data entry people know that ahead of time?
Wouldn't it be easier to have a textbox to enter as many characters of the
individuals last name as the data entry person wants? Then, when the user
exits that textbox open another form (popup) that displays a list of donors
whose last name matches (is like) the characters entered. On this form, the
data entry people would have the ability to select a donor from a list, or
add a new donor (separate form). Once they have selected a donor (or added a
new one), you would return to the Donations form to enter the donation data,
having populated the DonorNo field with the selection from the other form.

HTH
Dale

 

[Alan]

Alan,

if your DonorNo is the PK in the Donors table, is it an autonumber field?

I'm not sure why you would want people to enter the first three numbers of
the DonorNo, how will the data entry people know that ahead of time?
Wouldn't it be easier to have a textbox to enter as many characters of the
individuals last name as the data entry person wants? Then, when the user
exits that textbox open another form (popup) that displays a list of donors
whose last name matches (is like) the characters entered. On this form, the
data entry people would have the ability to select a donor from a list, or
add a new donor (separate form). Once they have selected a donor (or added a
new one), you would return to the Donations form to enter the donation data,
having populated the DonorNo field with the selection from the other form.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Sorry I wasn't clearer and thanks for the reply, Dave. My DonorNo has
already been established and I will be importing the initial data in
the Donors table from an Excel spreadsheet. The DonorNo is an 8-digit
field consisting of first 3 characters of last name, first 3
characters of first name and a 2-digit numeric "tie breaker". This
approach makes it relatively easy to assign DonorNos without much
effort. I was thinking that when the user types the first 3 characters
of the DonorNo (i.e., the first 3 characters of the last name), Access
could provide a shorter list from which to identify whether the
current donor already exists in the table. This is not an absolute
requirement.

I still need to know how to set up the form so that an existing
donor's DonorNo can be selected from the Donors table or added to that
table if new while also adding a new record to the Donations table.
I'm thinking the NotInList event may be part of the solution but I'm
stuck on what specifics will let me add a new Donor record to the
Donors table if needed as part of the input form I'm designing to
input new donations.

Alan