Get data from combo box to popluate the next box

I have a form whose first box is a combo. I enter in a PO and that numbe
appears. I need to have that number automatically go to the next box so it
will appear in a different table. PO Number comes off the Order table. I
use the PO number to bring up the order's vender and stores the vendors name
in the Invoice Table. So to get the PO into the Invoice table I now have to
rekey. Is there a way to copy it into the box.
Lin

--
Lin Light Herrick District Library

Hi, Lin.

I'm not clear why the combo box itself is not bound to the appropriate field
in the Invoice table, but...

To copy a value to a bound control, use the AfterUpdate event of the combo
box:

Me!YourOtherControlName = Me!YourComboBoxName

You do not need the vendor's Name in the Invoice table, by the way.
Assuming their primary key field is either stored in Orders, or is related by
way of the product, you can get the name through a multiple-table query.

Hope that helps.
Sprinks

"Lin Light" wrote:

I have a form whose first box is a combo. I enter in a PO and that numbe
appears. I need to have that number automatically go to the next box so it
will appear in a different table. PO Number comes off the Order table. I
use the PO number to bring up the order's vender and stores the vendors name
in the Invoice Table. So to get the PO into the Invoice table I now have to
rekey. Is there a way to copy it into the box.
Lin

--
Lin Light Herrick District Library

I don't have a primary key assigned. Will that make a difference in your
instructions? And yes I could and I do have a relationship between tables
based on PO, but I have staff that wanted something more, say, tangible.
They also wanted the secure feeling that the PO they are entering data for is
being charged to the correct vendor.
Thanks Lin



"Sprinks" wrote:

Hi, Lin.

I'm not clear why the combo box itself is not bound to the appropriate field
in the Invoice table, but...

To copy a value to a bound control, use the AfterUpdate event of the combo
box:

Me!YourOtherControlName = Me!YourComboBoxName

You do not need the vendor's Name in the Invoice table, by the way.
Assuming their primary key field is either stored in Orders, or is related by
way of the product, you can get the name through a multiple-table query.

Hope that helps.
Sprinks

"Lin Light" wrote:

I have a form whose first box is a combo. I enter in a PO and that numbe
appears. I need to have that number automatically go to the next box so it
will appear in a different table. PO Number comes off the Order table. I
use the PO number to bring up the order's vender and stores the vendors name
in the Invoice Table. So to get the PO into the Invoice table I now have to
rekey. Is there a way to copy it into the box.
Lin

--
Lin Light Herrick District Library

One more thing. Could you give a more detailed instructions. Some of the
background coding is a bit fussie to me.
LIn


"Sprinks" wrote:

Hi, Lin.

I'm not clear why the combo box itself is not bound to the appropriate field
in the Invoice table, but...

To copy a value to a bound control, use the AfterUpdate event of the combo
box:

Me!YourOtherControlName = Me!YourComboBoxName

You do not need the vendor's Name in the Invoice table, by the way.
Assuming their primary key field is either stored in Orders, or is related by
way of the product, you can get the name through a multiple-table query.

Hope that helps.
Sprinks

"Lin Light" wrote:

I have a form whose first box is a combo. I enter in a PO and that numbe
appears. I need to have that number automatically go to the next box so it
will appear in a different table. PO Number comes off the Order table. I
use the PO number to bring up the order's vender and stores the vendors name
in the Invoice Table. So to get the PO into the Invoice table I now have to
rekey. Is there a way to copy it into the box.
Lin

--
Lin Light Herrick District Library

Lin,

I think you have a misunderstanding of some of Access’ features; I’ll try to
clear them up as I think they’ll help you resolve your form issues.

1. Primary Keys/Relational Database/Normalization. To harness the power of
a relational database, virtually every table should have a primary key. The
simplest type, and the one I use almost exclusively, is an AutoNumber.
Tables should describe attributes of a single “thing� – such as Customers,
Orders, Products, etc. Fields from one table need not and should not be
duplicated in another table with the exception of foreign keys, which are
fields corresponding to another table’s primary key. Tables thus designed
are called normalized. Any Access reference will cover this subject in
detail.

As an example, the Orders table will include a CustomerNumber field to
identify the customer who placed the order. The OrderDetails table will have
a ProductNumber field to identify the product ordered. BUT, Orders will NOT
include the CustomerName or their PhoneNumber, and OrderDetails will NOT
include the ProductName. Foreign keys give you “Access� to any field in the
corresponding table by linking the tables in a query by the common field.
Take a look at the sample database that comes with Access
(Help/SampleDatabases/Northwind).

2. Difference Between What Is “Displayed� and What is “Stored�. Most of
your post is concerned with displaying meaningful information to your users,
which is great! However, you can display information from another table
without redundantly storing it by basing the form on a multi-table query
rather than a single table, or you can display information through
calculations and function and method calls.

For example, take a look at the Orders Form in the Northwind database. It
is based on the query Orders Qry, which links the Orders and Customers tables
by the CustomerNumber. When the customer is entered on the form via the
drop-down combo box, the form displays the address, phone, and other customer
information on the form. However, other than the customer number none of
this data is stored in the Orders table!

3. Bound and Unbound Controls. A form control (textbox, combo box,
checkbox, etc.) is Bound if its ControlSource property is set to the name of
a field in the form’s underlying table or query (specified in the form’s
RecordSource property). If data is entered into the control, it is then
stored in the field to which it is bound. Controls can also be unbound, and
their ControlSource could be an expression rather than the name of a field.
For example, a control that displays an Extended Price might have the
ControlSource:

=[UnitPrice]*[Qty]

Unbound controls merely display information.

4. Combo Boxes. Combo boxes confused the hell out of me when I started
with Access, but they aren’t really very complicated once you understand how
they work. They are controlled by several key properties. The RowSource
property holds an SQL statement (a query) that selects the data for the rows
of the combo box. The ControlSource property is the name of the field into
which the selected value is stored once a row is selected. The BoundColumn
property is the number of the column to store. Normally, as described above,
you will store a foreign key, so in most cases, this will be the first
column. The ColumnWidths property is the width in inches devoted to each
column in the display. If a Column is set to 0�, it is not displayed at all.

For example, to choose a customer for an Orders form, you might have the
following property settings:

ControlSource CustomerID
RowSource Select CustomerID, CustomerName FROM Customers
ORDER BY CustomerName;
BoundColumn 1
ColumnWidths 0�;4�

Because the first column in ColumnWidths is set to 0�, only the CustomerName
will display in the drop-down list, but once selected, the CustomerID is what
is stored in the underlying table, because the BoundColumn is set to 1.

5. One-to-One Relationships. Although there are exceptions, if a table has
a one-to-one relationship with another table, it should be a red flag that
you probably don’t need the second table, and its non-redundant fields could
simply be added to the first. I think that is probably true in your case
regarding the one-to-one relationship between Orders and Invoices. Take a
look at the Northwind database, which doesn’t have an Invoices table.

Onto your form’s issues.

I don’t think you need to copy the PO number to another form control, as you
were looking to do in your original post. Simply set its ControlSource to
the corresponding foreign key field in the underlying table.

To display the vendor name on your form, base your form on a query rather
than a table. In Query Design view, link the Orders table to the Customers
table by the foreign key CustomerID, and the Orders table to the Vendors
table via the VendorID. Then select the fields you’d like to display to the
users, including the VendorName that you’d like to show your users. When
selecting one of the linking fields, select the one from the Many side of the
relationship. For example, for an Orders form, select the CustomerID field
from the Orders table not the Customer table.

Once you’ve saved the query, set the RecordSource property of your form to
the name of the query, and you will be able to place any of the queries
fields on your form. If you don’t want the user to be able to change fields
from the One Side, which could affect multiple records on the Many side, set
the Locked property to Yes or the Enabled property to No.

Use the wizard to place a combo box to select an PONumber (to turn Wizards
On if it is off, select View, Toolbox and toggle on the button with the stars
and magic wand). Tell the wizard to get its rows from the Orders table,
select the PONumber field, and tell it to store the value in your
corresponding field.

As explained earlier, a primary key for all your tables is highly
recommended. To add one to an existing table, you’ll have to consider both
the One and the Many sides of any relationships. For example, if you’ve been
storing the CustomerName field in the Orders table, you will need to add an
AutoNumber primary key to the Customers table and a numeric foreign key to
the Orders table. Access will assign a number to each customer in the
Customers table, but you will need to run an Update query to update the field
in the Orders table. Let me know if you need any assistance doing this.

Hope that resolves your issues.
Sprinks


"Lin Light" wrote:

One more thing. Could you give a more detailed instructions. Some of the
background coding is a bit fussie to me.
LIn


"Sprinks" wrote:

Hi, Lin.

I'm not clear why the combo box itself is not bound to the appropriate field
in the Invoice table, but...

To copy a value to a bound control, use the AfterUpdate event of the combo
box:

Me!YourOtherControlName = Me!YourComboBoxName

You do not need the vendor's Name in the Invoice table, by the way.
Assuming their primary key field is either stored in Orders, or is related by
way of the product, you can get the name through a multiple-table query.

Hope that helps.
Sprinks

"Lin Light" wrote:

I have a form whose first box is a combo. I enter in a PO and that numbe
appears. I need to have that number automatically go to the next box so it
will appear in a different table. PO Number comes off the Order table. I
use the PO number to bring up the order's vender and stores the vendors name
in the Invoice Table. So to get the PO into the Invoice table I now have to
rekey. Is there a way to copy it into the box.
Lin

--
Lin Light Herrick District Library