Yes, I figured Me! is a function, but I couldn't find it in the functions
lists.
sorry i wasn't clear. NO, Me is *not* a function. it is an object reference
in VBA syntax that refers to the "current" form (or report) that the code is
running in. go back and read my previous explanation again and see if it
makes more sense the 2nd time around.
The form also uses the Order Details Table. Structure:
OrderDetail ID, autonumber, primary key
Order ID, number
Product ID, number (the table actually displays the name of the product
rather than the numeric id number. This comes from the Product table.
so ProductID is a Lookup field in tblOrderDetails. just like i said about
the other one - *get rid of the Lookup field in the table*!
These 4 tables are related on the redundant ID fields in each. The FORM
called "Add an Order and Details" is based on the Orders table and includes
all flelds in Orders table other than CSitus, which is the field he wants to
populate when a new order is entered.
okay, then. first, add field CSitus to the *RecordSource* of form "Add an
Order and Details". if you're using a version of Access prior to A2000, it
may be necessary to add a control to the form, with ControlSource of CSitus,
so the system will recognize it. if you need to do that, just make sure you
add the control to the Detail section of the form, and set its' Visible
property to False.
next, let's talk about the RowSource of the combobox control, in the form,
that you use to choose a customer name for a new order record. (and forget
about the subform, it's not relevant to the discussion.) i'm going to call
the combobox "cboCustomer". you posted a RowSource for cboCustomer a few
posts ago, as
SELECT DISTINCTROW Customers.*
FROM Customers ORDER BY Customers.CompanyName;
first, you don't need all those fields - unless you're using the customer's
address, etc, to help choose the right customer. next, why did you include
DISTINCTROW in the query? please tell me you don't have the same customer
listed more than once in tblCustomers! if you do, we need to stop here and
have a separate discussion about table structure. if you don't, let's change
the RowSource to only the fields you *need* to see in the combobox droplist
when choosing a customer for an order record. for example:
SELECT CustomerID, CompanyName, CSitus FROM Customers ORDER BY CompanyName;
based on the above RowSource, set the following properties for cboCustomer
as
ColumnCount: 3
BoundColumn: 1
ColumnWidths: 0";2";0"
ListWidth: 2.25"
LimitToList: Yes
in the combobox control's AfterUpdate event, add the following code, as
Me!CSitus = Me!cboCustomer.Column(2)
taking another look at the RowSource posted above, you'll see that CSitus is
the third field listed.
looking at the BoundColumn setting, you'll see that the value that will be
stored in the control's ControlSource field is the first column -
CustomerID. (the number in the BoundColumn property setting is *not* an
index value, so the "zero-based" rule does not apply here. and yes, i know
it's confusing.)
looking at ColumnWidths property setting posted above, you'll see that the
first and third column's widths are zero, so the customer id and city code
will not be seen when the droplist is open - only the customer name.
and finally, looking at the VBA code above, you'll see that the *index*
value referred to is Column(2); remember that combobox column indexes are
zero-based, first column is Column(0), second is Column(1), third is
Column(2), etc.
and btw, in case you're not already familiar with object-naming conventions,
suggest you see
http://home.att.net/~california.db/tips.html#aTip5.
hth
Patttt said:
The cities code is part of the Customers table. Here's the structure:
Customer ID - autonumber, primary key
Company Name, text
CSitus (this is the city code field), text
First Name, text
Last name, text
Billing Address, text
City, text
State, text
Zip, text
Phone Number, text
Notes, memo
Here is the structure of the Orders table:
OrderID, autonumber, primary key
Customer ID, number
Order Date, date/time
Purchase Order Number, text
CSitus, number
Payment received, yes/no
The form also uses the Order Details Table. Structure:
OrderDetail ID, autonumber, primary key
Order ID, number
Product ID, number (the table actually displays the name of the product
rather than the numeric id number. This comes from the Product table.
Quantity, number
Unit Price, number
Discout, number
Here is the structure of the Products table:
Product ID, autonumber, primary key
Product Name, text
CostofEach, currency
Unit Size, number
In Stock, yes/no
These 4 tables are related on the redundant ID fields in each. The FORM
called "Add an Order and Details" is based on the Orders table and includes
all flelds in Orders table other than CSitus, which is the field he wants to
populate when a new order is entered. The form also contains a subform that
is now based on the Order Details table plus a calculated field that I added
to provide an extended price for the order (quantity X unit price).
Yes, I figured Me! is a function, but I couldn't find it in the functions
lists.
table
