Appending values from a form into a new record in an existing tabl

[jerryb123]

How do I append data from a form into a new record in an existing table?

I have a form that I want to use to generate the following fields: Sales
Rep, Agent, Subagent, Revenue, Commission and Transaction Number. I have
combo boxes so that the user can select the Sales Rep, Agent and Subagent
based on values in a reference table, and I have text boxes for the user to
manually input dollar amounts for Revenue, Commission and Transaction Number.
Once the user has made selections from the combo boxes and entered values in
the text boxes, I want them to be able to press a button so that these values
are appended into an existing table.

Any help is greatly appreciated!

 

[Jeff Boyce]

Are you saying that the form is NOT bound to the underlying table? If so,
why not?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jerryb123]

The form is not bound to the underlying table because I don't want the users
putting junk into the underlying table. For the Sales Rep, Agent and
Subagent, there are existing relationships, and I don't want them to be able
to put in the wrong Agent for a particular Subagent, or the wrong Sales Rep
for a particular Agent. And I don't want them misspelling the names.

Having said that, I'm just getting back into this whole database thing after
a lengthy hiatus, so I don't always know the best way to do things!

 

[Jeff Boyce]

If your form allows a way to enter a misspelled name, you may want to
revisit your basic database design. Having to put the correct name in
repeatedly means you are probably using a spreadsheet, not a relational
database!

Please post a description of the table structure (table names, field names,
type of data in fields) you are using.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jerryb123]

I just started a new job and inherited a process which I am trying to
improve. I'm sure I'll have to revisit the design at some point--right now
it's definitely more of a "spreadsheet in Access" than a relational database.

Having said that, I still need to clean the data and keep it clean so we can
automate some of our reporting downstream. Upstream, people enter line items
manually into a Billing database, and there are Sales Reps associated with
those line items. Each Sales Rep has numerous possible Agents, and each Agent
has numerous possible Subagents, but the upstream people sometimes spell
names wrong or fat-finger stuff. So people who generate reports downstream
are eyeballing a lot of stuff that should be automatically queried! So for
instance I have two tables:

LINE_ITEMS Contains the fields ID (Autonumber), CIRCUIT_ID (Number), Sales
Rep (Text), Agent (Text), Subagent (Text), Revenue (Currency), and Commission
(Currency).

REF_SALESREP_AGENT_SUBAGENT Contains all allowable combinations of Sales Rep
(Text), Agent (Text) and Subagent (Text)

I want to design a form so that the people upstream can select the Sales
Rep, Agent and Subagent using combo boxes, then manually input the other
data, and have their input and selections as a new record in LINE_ITEMS.

Thanks for your help!

 

[Jeff Boyce]

You can add a combobox to the form that returns both SalesRepID and
SalesRepName, but only shows SalesRepName. What the table into which you
are adding new record will store is the SalesRepID. What the form that lets
you add records shows is the SalesRepName.

Folks using the form will "pick" the correct name, not spell it. Access
will store the corresponding ID (but the users won't see it).

For each of the "pick the correct" comboboxes, use the same approach.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP