comboBox & northwinds sample orders form

[Guest]

Hello,

I've been trying to figure out how the northwinds sample database uses the
comboBox on the Orders Form.

I'm not worried about the ShipTo information, which is updated by the
AfterUpdate event. I know how they do that.

I've been trying to figure out how the change to the BillTo ComboBox also
changes the rest of the BillTo address information?

I've looked at the query attached to the form, the DataSource for the
ComboBox, etc. but when I try to create a similar form and similar comboBox,
mine will not update the rest of the BillTo address information.


I know I'm missing something very simple that links the comboBox to the rest
of the BillTo information, but I can not see it. Could someone give me a
detailed explanation of what is linking all the BillTo information together?

Again, just for clarity, I have created a similar form query and comboBox
with dataSource, but what I'm missing is not updating the rest of the BillTo
information.


Thank you,

phil

 

[Albert D. Kallal]

The trick done with that is a double whammy......

First, the table design uses the dreaded lookup feature that so many of us
developers avoid.

Now, it turns out, that the this lookup feature is NOT responsible for this
trick.

To understand how this works. In the northwind, lets create a NEW table
called cooltest.

Lets also add ONE field to this table. Lets call it EmployeeID (it has to be
of type long).

Now, save this table (you can allow ms-access to add the primary key "auto"
number if you wish.

Now, open up the table..and type in
EmployeeID
-------------
2
3

(that is two values you just entered into this table).

Now, close this table.

Fire up the query builder. Create a new query,and Drop in our table called
CoolTest, and also drop in the table called Employees

Now, draw a join line FROM table cooltest, from EmployeeID field to
EmployeeID in table Employees

Double click on the join line, and make you choose the 2nd option:

Include All records from coolTest and only those records from Employees
where joined fields are equal

(this is called a left join..and most your joins will be this choice).

click ok. Now drop in the EmployeeID field from table cooltest, and ten drop
in the FirstName, LastName, Address fields from employees table.

That should do it. Now save this query. Now open the query. Notice how data
from table employees is in the query.
Now, for the really cool part, while looking at this grid/table....just add
new record to the query, but ONLY enter the EmplyeeID (say lets use 5) into
the first field (EmployeeID). Now, hit the tab, or down-arrow key.
...viola..the rest of the fields appear...and that is how the northwind does
this. In fact, you can enter 2, or even existing numbers again if you wish.
I use this trick in sub-forms for look up of partdescrtions etc, as you
don't need to write code to update, nor do you need to "copy" the data, nor
do you need to pull the data in the combo box and use .column(2) etc to get
these extra fields.

I will not makes comments about the other issue of using a lookup
field...you can read the 2nd "commandment" here on this issue:
http://www.mvps.org/access/tencommandments.htm

 

[Mike Painter]

The trick done with that is a double whammy......

(that is two values you just entered into this table).

Now, close this table.

Fire up the query builder. Create a new query,and Drop in our table
called CoolTest, and also drop in the table called Employees

Now, draw a join line FROM table cooltest, from EmployeeID field to
EmployeeID in table Employees

Double click on the join line, and make you choose the 2nd option:

Include All records from coolTest and only those records from
Employees where joined fields are equal

(this is called a left join..and most your joins will be this choice).

click ok. Now drop in the EmployeeID field from table cooltest, and
ten drop in the FirstName, LastName, Address fields from employees
table.

That should do it. Now save this query. Now open the query. Notice
how data from table employees is in the query.
Now, for the really cool part, while looking at this
grid/table....just add new record to the query, but ONLY enter the
EmplyeeID (say lets use 5) into the first field (EmployeeID). Now,
hit the tab, or down-arrow key. ..viola..the rest of the fields
appear...and that is how the northwind does this. In fact, you can
enter 2, or even existing numbers again if you wish. I use this trick
in sub-forms for look up of partdescrtions etc, as you don't need to
write code to update, nor do you need to "copy" the data, nor do you
need to pull the data in the combo box and use .column(2) etc to get
these extra fields.

I'm not sure wht the intent of this was but for any readers that may be new
to Access or relational databases this is the way they are designed to work.

There are no tricks involved and it is no more cool than having a light go
on when you flip a switch.

dLookup is slow but that will only be important if you are running one in a
large query.
Why it is slow is mystery because you can run a query in VB that does
exactly the same thing and it many times faster.

Hopefully some version down the line will fix this.

I wish MSFT would buy Pick systems and copy features found there, especially
in BASIC.

 

[Albert D. Kallal]

I'm not sure wht the intent of this was but for any readers that may be
new
to Access or relational databases this is the way they are designed to
work.

There are no tricks involved and it is no more cool than having a light go
on when you flip a switch.

True, but OFTEN to display a customer name when you have a customer id, we
are told to use a dlookup, or use the

me.MyComboBox.column(1)

And, a very large portion of the time in this newsgroup, when people need to
"display" a related value in a form, the above two solutions (column(1), and
dlookup) are suggest.

dLookup is slow but that will only be important if you are running one in
a
large query.

Why it is slow is mystery because you can run a query in VB that does
exactly the same thing and it many times faster.

Well, the problem is that you don't need dlookup in a query. And, the reason
why it is slow is because for each record, a whole function is called that
has to open up a table. You can always just shove in the sql in place of the
dlookup anyway.

select customerID, dllookup("CustomerName",tblCustomers","CustomerID = " &
customerID as CustName,
OrderDate, OrderTerms from tblOrders.

In the above, we are lookup in the customer name. However, the above should
be replaced with a sub-query. A sub-query gets compiled by the query
process..and runs VERY fast. So, we can/should use:

select customerID, (select CustomerName from tblCustomers where CustomerID =
tblOrders.CustomerID) as CustName,
OrderDate, OrderTerms from tblOrders

The above runs fast, since the query processor can compile the whole
statement..and "KEEP" the tblCustomers table opened. We have no such luxury
with dlookup. And, to be frank, the only time dlookup is of any use in on a
form for a quick and dirty lookup..and as mentioned, even for a quick
lookup...dlookup is rarely needed. And, as mentioned, you can use the query
builder, and use left joins. So, even if you have 3 or 4 lookups in a
table..the query builder can make this form you..and no sql needs to be
typed. (the above sub-select tends to be cleaner solution then left joins
via the query builder, but it don't matter..as both perform very well..and
certainly MANY times faster then dlookup).

Further, dlookup is a access VB function, and one would do well to use of
the "many" "sql" based solutions in place of a VB function (least someday
one moves the back end data part to sql server. In fact, one should avoid
ALL use of VB functions directly in quires for performance reasons anyway).

We also as a general rule don't call basic functions in pick queries either!
(but, the callX does perform very well!)

Of course, the same quick and dirty lookup trick in Pick basic code is often
used. It is:

CustomerName = oconv(customerID,"TCUSTOMERS;X;5;5")

(assumes that customerName is amc field 5)

Using a translate directly in pick code was also consider "lazy", and
opening a file for reading in place of oconv was always MUCH faster.
However, I will admit that using the oconv with a file translate in pick
basic don't seem to suffer much of a performance hit like ms-access does.

As mentioned, with several sql based solutions....dlookup should be avoid,
and is really on there for convenience sake.

 

[Albert D. Kallal]

1. the main problem
after changing the employeeID many times, the cooltest table will
eventually contain all the same employeeID's. I start out with
cooltest

I don't see why this is happening at all. I mean, if you need to create a
new related table that JUST has the EmployeeID and some other information,
then do so.

In your example:

employeeid, index (autonumber), dateFromDetroit, dateToDetroit
1 1
9/1/2004
2 2
5 3
7 4

Are the fields dateFromDetrotit part of this table...or are they lookup
values? (regardless, I don't see how editing a table will change all the
employees id's unless you are editing the wrong record when making
changes???

2. the other problem is, the fields in table:coolTest if blank do not
blank
out the
corresponding field on my form.

so, once the dateToDetroit field shows the date in coolTest:record 1,
it always
shows on the screen.

Is the form now based on that new left join query?

How do I make a Null field or blank value, erase the field on the
form?

It is going to depend on what table the datefield comes from. If the values
are from the joined table, then when you change the EmployeeID (or
whatever), then those joined in fields will change. If you are deleting the
EmployeeID, you likely should be deleting the record, and not trying to
empty the fields. If you are trying to put more the one value into a form,
then using a sub-form to lookup, and maintain the relation might be a better
choice here then using join query.

 

[Guest]

hi again,

1. What i should say, is that when I select a different employeeID from the
comboBox, at random times the employeeID in table:addr will be changed
automatically. I do not try to change it.
If i change the employeeID field to (no duplicates), I will get an error at
random times while selecting different employeeID's from the comboBox

error:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
(Error 3022)


sql:

SELECT addr.employeeID, Employees.LastName, Employees.FirstName,
Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate,
Employees.HireDate, Employees.Address, Employees.City, Employees.Region,
Employees.PostalCode, addr.age, addr.phone
FROM addr INNER JOIN Employees ON addr.employeeID=Employees.EmployeeID;

 

[Albert D. Kallal]

hi again,

1. What i should say, is that when I select a different employeeID from
the
comboBox, at random times the employeeID in table:addr will be changed
automatically. I do not try to change it.

You mean random records..not at random times? I thought the WHOLE purpose of
the combo box is to select a PARTICULAR employeeID into the addr table. So,
if you select a particular emploreeID, then the addr.employeeID field is to
be updated here. That is the WHOLE idea..right? The process of selecting the
employee from the combo box will set the addr.employeeID for you. So, I
don't know what you mean by random "time". But, for sure, you select a
employee in the combo box..then the intention here is thus to set the
addr.employeeID.

And, just like the orders example, "many" orders could have the SAME
customer. It would be silly to not allow duplicates in the customerID in the
orders example. If you do not allow duplicates in the orders table..then a
customer would ONLY every be allowed to have one order..and that don't make
sense.

If i change the employeeID field to (no duplicates), I will get an error
at
random times while selecting different employeeID's from the comboBox

As per the orders example, of course you will get an error if you don't
allow duplicates in the addr emplyeeID field. If you never want to allow
more then one emplyreeID into the addr table, then yes, I suppose you would
set the index to not allow duplicates. (but, then this does means that you
might want to look at how your relations are to be setup, or what the goal
here is). This process don't copy any data...but only displays it. The only
"real" data field we are setting is the emplyeeID field in the addr table.
However, we certanly have to be carefull what record in the addr table we
are changing.

Another approach to display this data is to use a sub-form. I explain how
here:
http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html