Creating a form to populate Datasheet

[Michael Croy]

I have Access 2003, and I've designed a number of tables to track global
sales pipeline opportunities.

I don't want people messing with the datasheet, so I want to create 2 forms:

1. Allows people to enter new sales opportunities and fill in about 25
fields. Many of the fields will have drop down menus to limit choices.
2. Allow people to search for and update existing opportunities on the
datasheet.

I'd like them to enter all the new information (or changed information) and
then press a button that either says "Add Opportunity" or "Update
Opportunity."

I'm having trouble designing the form, since whenever I add a field to the
form, it automatically fills in values and then as I enter data it changes
existing entries.

I'm using the Access 2003 Bible and really struggling. Any advice on how to
approach this?

 

[Jeanette Cunningham]

Michael,
A form can have 2 different data properties.
It can be set to view existing records and edit them
It can be set to add new records.

If your form has the standard navigation buttons at bottom left, to add a
new sales opportunity, click the add new button (with the star asterisk on
it)
You should be able to add a new sales opportunity without overwriting
existing entries.
Your problem might be related to the combo boxes - If you would be a bit
more specific about what exactly you do that overwrites existing entries we
can probably be more helpful.

Jeanette Cunningham

 

[Michael Croy]

Thanks Jeanette

I'll provide more detail.

The database has a number of lookup tables (Account status, account rep,
region, country, etc) that all act as lookups for a 'Master' table that
contains all the different sales opportunites for the organization. I built
the 'Master' table by combining information from a bunch of spreadsheets from
our sales people.
Instead of having the sales people all access the datasheet to add or change
opportunities, I thought I should have 2 forms, one to add new opps, and one
to update existing opps.

I re-built a form to enter new opps, and for the fields with that require
lookup information for drop-down menus, I bound them to the lookup tables.
Other fields are text fields that they enter into the form. The problem is
how do i now tie this form to update the 'Master' table? Right now i fill in
a form and it adds the information to a new data sheet, I want to tie it to
the existing 'master' form.

Similarly, I haven't designed the update form yet, but what i want to do is
have probably 5 lookup criteria, have whatever choices that match the
criteria come up in a window and allow the user to choose the right entry,
then have a form come up with all the fields and allow them to update. Any
advice on how to proceed with that?

Design is easy...getting it to work right, now there's the challenge!

 

[Jeanette Cunningham]

Michael,
If your lookup tables have a primary key and one other field ( I assume this
is the setup)
For the the form to enter new opps.
Make its recordsorce a query that is based on the master table and contains
all the foreign key fields for those particular lookup tables.
Each combo is bound to its matching foreign key field in the master table.
Each combo has a row source that is a query based on the appropriate lookup
table.
The query must include the Primary key and any other necessary fields from
the lookukp table.
The combo has its 1st column hidden, and this is the column that is bound to
the master table.
When user makes a selection from the combo it is the ID in the hidden column
that is put in the new record in the table.

Jeanette Cunningham