Combo Box Pickle

  • Thread starter Thread starter FrunkaBlunka
  • Start date Start date
F

FrunkaBlunka

Hi everybody,

I am trying to make a datbase that a prints report that will be used as
an invoice

I am trying to eliminate the need of typing the same clients name,
phone, fax in every single record. I have created a table
(tblClientDetails) where I have added all the clients that I deal with.

On my main form (frmProjectDetails) I have created a combo box that
lists all the different clients. Once selected I would like the combo
box to show the details of the client on the report called
(rptTaxInvoice) using the data in the (tblClientDetails) All the tax
invoices are unique identified by there JobID. When I tried to do it I
got it to work to an extent but the client was the same for every tax
invoice and never changed when navigating through the database.

There is something simliar in the orders form of the Northwind
database which in is perfect I just can't translate it to mine.

Forms
frmClientDetails
frmMain

Reports
rptTaxInvoice

Tables
tblClientDetails
tblProjectDetails

Hope that makes sense :)
 
Rather than basing your form on tblProjectDetails directly, create a query
that links the two tables by the ClientID number, and includes all of the
tblProjectDetails fields, and all of the tblClientDetails fields you'd like
to display on your form. Do not include the primary key of tblClientDetails,
or your query will be non-updateable.

The ControlSource of your combo box should be the numeric field in
tblProjectDetails that corresponds to the primary key of the tblClientDetails
table. You can *display* the client name instead by setting the first
ColumnWidth to 0". Then place textboxes for the other fields.

Sprinks
 
Thanks Sprinks. I must be doing it wrong because I can not get it to
work. :(

My main form is where I enter the data about the job I am currently
doing ie project name, project address, specification etc. At the
moment because my database is very basic I am entering in every clients
details with all of that other data. I have made a table that has all
my clients names, phone numbers, addresses which I would like to
utilise so I dont have to enter the clients details for every record on
my main form. Am I going about this the right way? Is combo box the way
to go save me time or should I try something else. All I want to be
able to do is select a client so there details are automatically
inserted into the current record. What is the best way to utilise the
table that has all my clients details?

Thanks
 
FrunkaBlunka,

Like many new relational database users, you're getting hung up on what is
*displayed* in *controls* on a form and what is *stored* in *fields* in a
table. Tables should follow normalization rules--see references below. A
normalized application is efficient and easy to maintain. I urge you to
master the basic rules--they're not difficult--before you go any further.

One rule is that you don't store information redundantly from one table to
another. A Clients table should have a structure something like:

Clients
-------------------------
ClientID AutoNumber (Primary Key)
ClientName Text
Address
Phone
City
State
Zip
Fax
Website
....
other Client-specific fields

A Projects table should store information specific to each project. One of
these pieces of information is the client. To identify the client, you need
store only the ClientID from the Clients table, called a "foreign key" in the
Projects table, because it corresponds to the primary key of another table.
Since the primary key of Clients is an AutoNumber, the ClientID in Projects
must be of the Long Integer type.

However, who remembers or even cares about a ClientID number? We're much
more comfortable dealing with a client name. That's where a combo box comes
in. Several key combo box properties drive its behavior. If the
RowSourceType is a Table/Query (nearly all cases), the RowSource property is
an SQL statement selecting one or more rows of a table or query, such as:

SELECT Clients.ClientID, Clients.ClientName FROM Clients ORDER BY
Clients.ClientName;

The ControlSource is the name of the field in the form's underlying
recordset where the combo selection is stored. Following the earlier
discussion, this should be ClientID of the Projects table.

The BoundColumn property determines which column is stored in the field
specified in the ControlSource. Since we want to store the ClientID, this
should be 1.

The ColumnWidths property determines how much display space is allocated to
each column. If the first column width is set to 0", it will not be
displayed in the drop-down list, and the first non-zero-width column will
display once a selection is made. In this way, the user can efficiently
*store* the numeric foreign key, but never see it nor need to know it exists!
He will be looking solely at a list of names.

If you base your main form on a query as I suggested, once the ClientID is
stored, the form can display the other Client fields.

If this discussion doesn't help resolve your problem, please post:

- Your table structures (fieldnames, fieldtypes, primary keys)
- Your form's RecordSource. If it is based on a query, please post the SQL
(from query design view, choose View, SQL, and cut and paste the statement
- Your combo box' name, ControlSource, RowSource, BoundColumn, &
ColumnWidths properties
- Key properties of any subforms

Sprinks
 
Thanks Sprinks I worked it out. I am self taught, but I have learnt a
great deal from these forums. Thanks again
 
Back
Top