CAN ANYONE HELP!

  • Thread starter Thread starter SIRSTEVE
  • Start date Start date
S

SIRSTEVE

I have a database that I created that contains the following fields.

Date, Envelope Number, Name, Offering Type, & Amount.

Here's what I would like to be able to do?

I would like to enter the contributor's envelope number and have the
database automatically insert the name of the person who is associated with
this number into the NAME FIELD. Can this be done? If so, could anyone tell
me how?

Thanks for your help!
 
You might want to take a look at the [Orders Subform] in the Northwind sample
database. This code is used to stick the default price into the UnitPrice
text box in the subform.

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub
 
It can be done, but it shouldn't be if it is already stored elsewhere.
There should be a table for People (or Members, or something like that). Is
an envelope number always the same for a particular person? If so,
EnvelopeNumber can serve as the table's Primary Key, similar to the way an
employee number remains the same in a company. The Members table would also
contain other information such as Address, etc.
A separate table would be used to store Contribution information.
EnvelopeNumber (or MemberID, or whatever you choose to call it) would be the
linking field for establishing a relationship with the Members table.
There is a Membership database at the Microsoft web site that you may be
able to use as a starting point:
http://office.microsoft.com/en-us/templates/TC010185841033.aspx?pid=CT102144001033&AxInstalled=1&c=0

Watch for word wrapping in your news reader. That link should be on one
line.

The general point I am making is that you only need to store the member name
once. For related records (such as contributions) you would store the
primary key. If a member's name changes, it will be reflected in all
records, but if you store the name for each contribution you will need to
update many records.
 
On Thu, 31 Jan 2008 08:17:01 -0800, SIRSTEVE

Please don't SHOUT AT US, Steve. Typing in all caps carries that implication.
Most of the people who come here want help; most of us who volunteer to help
do so without needing to be yelled at or pleaded with. A subject line like
this will put people off, not get more or better answers!
I have a database that I created that contains the following fields.

Date, Envelope Number, Name, Offering Type, & Amount.

Here's what I would like to be able to do?

I would like to enter the contributor's envelope number and have the
database automatically insert the name of the person who is associated with
this number into the NAME FIELD. Can this be done? If so, could anyone tell
me how?

Thanks for your help!
Click to expand...

Don't.

That's not how relational databases work! They use the "Grandmother's Pantry
Principle" - "a place - ONE place! - for everything, everything in its place".
A contribution does not HAVE a name; a person does, but a person is one type
of entity, and a contribution is a different type of entity.

Your Envelopes table should have the EnvelopeNumber (don't use blanks in
fieldnames if you can avoid it) as its Primary Key, LastName, FirstName, and
other biographical information (don't use the reserved word Name as a
fieldname). That's the *only* place that the person's name should be stored.

If you want to *SEE* the person's name, you can use a Query joining the
donations table to the envelope's table; you can also use a Form Control such
as a Combo Box, storing the envelope but displaying the name.

If you're entering data directly into table datasheets... don't. That's not
their function. Tables are data storage repositories; use Forms to display,
enter and edit data.

You might want to check out some of these tutorials for how to use databases:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]
 
Back
Top