Hello,
I am having a hard time getting my form(s) to link to the correct tables.
What I am trying to do is this:
I have tables set up for my "members". I want to create a form that will put
the information entered on the form into the correct table. EX: I have
members A, B, C and D. One of them refers member X. I want to create a form
where I enter member X's information and it not only creates a new table for
member X but puts his information in the correct "referring" members table.
ie,  the form would have a "referred by" field and I would enter member A. I
want the form to automatically put the new members info into member A's table.
If I haven't confused you too much, please let me know how this can be done.
Thanks,

I may be too literal in my interpretation of your description ...

You mention "creating a new table for member X" and putting information into
the "correct referring member's table".

If you mean that you have one table per member, you have committed
spreadsheet on Access.  Access is a relational database, and expects
well-normalized data, not 'sheet data.  If you are (literally) using one
table per member, stop now!

Since we aren't there, how 'bout if you describe a bit more how your
tables/data are set up.  If it turns out that they're more like spreadsheets
than relational tables, you'll need to decide.  If you don't normalize your
data, you (and Access) will have to work overtime to come up with
work-arounds.

If you don't understand "relational" or "normalization", it's a bit of a
learning curve ... but you will either pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Show quoteHide quote

You are exactly correct, I am into spreadsheet thinking. I take it that is
where I am going wrong? I am trying to create a database where I enter the
information on a form and it creates a "record" in 2 seperate databases( 1-
member database, 2-referring member database). Basically, I will have
"regular" members and "vip" members. The vip members will get credit for the
members they refer. So I would like to have one form that I input a new
members info into and it will create a record in the "regular" members
database and also put the in the correct referring (vip member) database. I
don't know if I'm making sense. Am I?

Jeff Boyce wrote:
Show quoteHide quote

See comments in-line below...

news:987396d2b3a27@uwe...
"wrong" is a relative term.  I can drive nails with my chainsaw, but it is
neither the right tool nor particularly safe or efficient.  You and Access
will both have to work overtime to come up with work-arounds to compensate
for 'sheet data.

So, pay now or pay later... spend time learning "relational database design"
and "normalization" now, or do the extra work to overcome spreadsheet
data/thinking.

Perhaps only a semantic issue, but you are using "database" when I believe
you mean "table" ... and that's still spreadsheet thinking!  It all starts
with the data.  Until you have normalized your data structure (part art,
part science), you will continue to struggle with trying to create two
separate records.

So you're saying that a characteristic/attribute of any member is whether or
not s/he can be considered a "vip".  You can do that in a [Member] table
with a yes/no field ([VIP?]).

First the table, then the form.  Get the data structure in place, then build
a form.  Access tables store data, Access forms (and reports) display data.

I believe I have a better idea what you are trying to accomplish now.
Again, you will either pay now (learn about the data structures Access uses
best) or pay later (keep struggling with work-arounds).

Here's a totally untested (and perhaps not even relevant) idea of a table
structure and process (generic):

  tblMember
    MemberID
    LName
    FName
    VIP?
    ReferredByID (note: this field will hold the MemberID of the person
referring, if any)

You could create a query against this table that lists all VIP members (and
their IDs), then use this query to feed a combobox on the form you create.
You would 'bind' this combobox to the [ReferredByID] field.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hey Jeff,
How do I learn more about relational database design? I thought that forms
are used to put the data into the tables. Is that incorrect? Do I enter the
data directly into the tables?

Jeff Boyce wrote:
Show quoteHide quote

trenttdogg via AccessMonster.com wrote:
....

Access is extraordinarily powerful, but you'll only harness that power
if you start looking at your data in a new way.  It doesn't seem obvious
at first, although you soon develop an "eye" for it.  Very few people
can pick up Access and do anything useful with it immediately - there is
a fairly steep learning curve (although the view from the top is great!).

It helps to think in terms of "layers".  The foundation is table design
- if you get that wrong everything else will be ten times harder.  You
should concentrate on understanding the issues surrounding table design
first, and only move on when you have a good understanding.  Even then,
problems apparently with queries, forms and reports are often solved by
revisiting your table design.

Essentially, you look at your data as a collection of related "entities"
(that's a posh word for "thingy").  If you're running a library, you'd
have tables for Borrowers and Books, each with a field holding the
unique identifier of the individual item, and other properties.  Some
entities may seem abstract:  if it's a lending library you'll want a
table for "Loan", which will link a Borrower record and a Book record
via their identifiers (~"keys"), together with dates.

My favourite resources for learning all this sort of stuff are video
tutorials.  You'll find some on UTube (search for Access table design,
or Normalisation) and you can get good video training from Lynda.com
(just a customer) including some free tasters like this:
http://www.lynda.com/home/Player.aspx?lpk4=31001

Microsoft also have good introductory stuff:
http://tinyurl.com/ms-table-design-tutorial

.... and of course there are many books, and also tutorials provided by
some of the major contributors here:

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

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

Tony Toews' website:
http://www.granite.ab.ca/access

All seems very daunting, but if you take one at a time, you may well
find it addictive.

Anyway, once you have your tables figured out, an Access "application"
will have a Query layer, and a layer of Forms and Reports on top of
that.  You may also get into VBA programming when you start looking at
handling "Events", like a button-click.

To answer your question: you _can_ enter data directly into tables, and
will often do so during testing, but you'd normally create a Form to
make this easier.

Access has excellent wizards.  Once you've organised your tables, create
a query to select particular fields of interest (maybe all) and then use
the Form wizard to create a form based on that query.  You may be
surprised at how easy it becomes when you have your tables right.

HTH

Phil, London

Also, how/where do I create a combobox?

Jeff Boyce wrote:
Show quoteHide quote

trenttdogg via AccessMonster.com wrote:
Access allows you to create a "Lookup" field in a table, but the general
consensus is that this is a bad idea.

A combo box allows the user to choose one option from a list via a
drop-down control.  They belong in forms, not tables.  The list of
options can be stored within the combo box properties (appropriate for
choices such as Male/Female) or in a dedicated table (appropriate for a
list of US states or English counties, for example).

Easiest way to learn about them is to play with them.  Create a simple
test table with suitable fields, and then use the Form Builder wizard to
create a Form based on the table (you can skip the usual query layer in
a test like this).  Locate an unused part of your form, make sure the
"Use Wizards" button is enabled, click the Combo Box control and drag
out a rectangle.  A wizard will appear asking you pertinent questions.
Just try this a few times and you'll soon get the hang of it.  Try
these, too:
http://video.google.co.uk/videosearch?q=access+combo+box+tutorial#

Phil, London

I think I am kinda starting to understand the "methodology" to databases. I'm
muddling my way around, built a nice table, a couple queries and am playing
with a form. I can't figure out how to get "options" in my combobox though?
Shouldn't my box drop down with choices for that field?

Philip Herlihy wrote:
Show quoteHide quote

trenttdogg via AccessMonster.com wrote:
Show quoteHide quoteIf you followed my suggestion and made sure that the wizard popped up,
then it would have asked you what choices/options you needed for that
field.  I'd have thought one of those videos would take you through that
  (I didn't watch them all!).

If you want to do it manually, then you add a combo box to a form, and
then make adjustments to the various Properties.  Right-click the
control and pick Properties.  The most interesting ones are
Data/Control-Source (the table/query field which will receive any
input), Data/Row Source Type and Data/Row Source - where the control
finds the list of options presented to choose from.  You may also need
to adjust Format/Column Count and Format/Column Widths if you're picking
up options/choices from a table, and you want to store an ID/Key field
but display other details in the list of choices.

The wizard does all this for you - did you try it?  You need to find the
button in the ribbon/toolbar which turns on wizards to run when adding
controls, then click the combo box control and drag one out on your
form.  This will work even on a blank form in Design view - you'll be
asked all the necessary questions and Access will do all the work.

Have a look at this:
http://office.microsoft.com
/en-gb/access/HA101130521033.aspx?pid=CH100645691033
(should be one line with no spaces)

Phil

I am using an old version of access (2000) and I have no idea where I "turn"
the wizard on because nothing popped up like I was hoping. Where do I find
the wizard on the toolbar?

Philip Herlihy wrote:
Show quoteHide quote

trenttdogg via AccessMonster.com wrote:
Show quoteHide quotehttp://www.btabdevelopment.com/main/QuickTutorials/
Howtocreateacomboboxtosearchforarecord/tabid/60/Default.aspx

(URL one line, no spaces)

Phil

Hey Phil,
I have another question. I have my table set up and I want to create some
queries that will pull information based on a value of a particular field in
the table. For example: I have a field called Type(membership) and in that
field will be one of 3 values M (monthly), A (annually) or V (VIP). I would
like have a query pull only the records that have an "A" in the Type field,
another that will pull only "M" and finally one that pulls only the "V"
records. How can I do this?

Philip Herlihy wrote:
Show quoteHide quote

trenttdogg via AccessMonster.com wrote:
Show quoteHide quoteThis should give you what you need:

http://office.microsoft.com/training/
training.aspx?AssetID=RC102919041033&CTT=6&Origin=RP102919031033
(should be one line with no spaces)

Essentially you put what you want to match in the "Criteria" line of the
query builder.

Under some circumstances using a "filter" is easier - read that up in
Help.  Filtering is very easy, and is always available via
toolbar/ribbon buttons.

You might also like to read up on "Parameter Queries".
http://office.microsoft.com/en-gb/access/HA102563981033.aspx

In a more advanced application, you'd set up a search form with a combo
box so users can choose which type of record they want to view.  This is
getting more advanced, but you might like to have a look here:
http://allenbrowne.com/ser-62.html

It's clear you're thinking along the right lines, but you need to spend
some time on training.  Have a look here:
http://office.microsoft.com/en-gb/training/CR101582831033.aspx

Phil

I'll second Philip's comments ... there are several learning curves involved
in using Access to build applications that are useful and get used.

Depending on previous experience, it may require work on:

  1) relational design and normalization (how Access stores and 'looks for'
data)
  2) Access tips/tricks/techniques (how Access does things)
  3) Graphical user interface design (what makes for a user-friendly
application?)
  4) application design (what process do you use to build an 'app'?)

Some folks get it, some folks don't ...

Some folks have the time to learn all four, some folks end up hiring 1/more
of these done for them...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Show quoteHide quote

junction table setup
Starting Point for Access 2003 database
Report in Legal size
Table Set-up
Copy Field Data
How do I set up a column to populate...
loading Access when I already have a 2007 student Office pkg loade
BIRTHDAY MAIL SHOT
multiple users updating the same table and sharing
Daily download