Combo box action

[Thomas Kroljic]

All,

Not sure why this happens and hopefully someone can enlighten me. I have a
form that has a query assign to it:



(SELECT DepositLog.*, Customer.Addr1, Customer.Addr2, Customer.City,
Customer.State, Customer.Zip, Customer.HomePhone, DepositLog.CustomerID

FROM DepositLog INNER JOIN Customer ON DepositLog.CustomerID =
Customer.CustId





The data from the query is bind to the form fields. One of the fields of the
form is a combobox for the customer ID. When the form opens, the customer
names is displayed in the combo box and the address fields are display
appropriately.



The combo box has the following query assigned to it:



(SELECT Customer.CustLastName, Customer.CustFirstName, Customer.Addr1 AS a,
Customer.HomePhone AS b, Customer.CustId AS c, Customer.Addr1 AS d,
Customer.Addr2, Customer.City, Customer.State, Customer.Zip

FROM Customer

WHERE (((Customer.CustLastName)>" "))

ORDER BY Customer.CustLastName





Now the weird stuff. After the form is open and displaying the first record'
s information, if I change the customer name using the combo box, the
address fields on the form also change. How is this? I do not have any VBA
logic that moves the data in the combo boxes columns to the appropriate
fields on the form. It just happens magically.



Any thoughts? This is driving me a little crazy that I can't figure out why
it is changing the data that is being display in the address fields (addr1,
city, state, zip).



Thank you,

Thomas J. Kroljic

 

[Dirk Goldgar]

All,

Not sure why this happens and hopefully someone can enlighten me. I
have a form that has a query assign to it:

(SELECT DepositLog.*, Customer.Addr1, Customer.Addr2, Customer.City,
Customer.State, Customer.Zip, Customer.HomePhone,
DepositLog.CustomerID

FROM DepositLog INNER JOIN Customer ON DepositLog.CustomerID =
Customer.CustId


The data from the query is bind to the form fields. One of the fields
of the form is a combobox for the customer ID. When the form opens,
the customer names is displayed in the combo box and the address
fields are display appropriately.

The combo box has the following query assigned to it:

(SELECT Customer.CustLastName, Customer.CustFirstName, Customer.Addr1
AS a, Customer.HomePhone AS b, Customer.CustId AS c, Customer.Addr1
AS d, Customer.Addr2, Customer.City, Customer.State, Customer.Zip

FROM Customer

WHERE (((Customer.CustLastName)>" "))

ORDER BY Customer.CustLastName


Now the weird stuff. After the form is open and displaying the first
record' s information, if I change the customer name using the combo
box, the address fields on the form also change. How is this? I do
not have any VBA logic that moves the data in the combo boxes columns
to the appropriate fields on the form. It just happens magically.

Any thoughts? This is driving me a little crazy that I can't figure
out why it is changing the data that is being display in the address
fields (addr1, city, state, zip).

Thank you,

Thomas J. Kroljic

Your form's recordsource query acts as what is called an "autolookup"
query. It joins the two tables together on the CustomerID field. By
the logic of the query, then, the address fields (which are picked up
from the matching Customer record) are dependent on the CustomerID in
the DepositLog record.

The combo box is bound to the CustomerID field from the DepositLog
record. When you change the value of that CustomerID field, by choosing
a different customer in the combo box, the database engine "knows" that
this new CustomerID matches a different customer, and automatically
finds that record and matches it up to the DepositLog record in place of
the Customer record that was previously matched to this record. Thus,
the customer address fields displayed on the form change to reflect
those of the new customer.

Does that explain it? Pretty cool, no?

 

[Guest]

Hi Thomas,
Likely your problem is that the combobox is bound. Are you wanting to select
a record with the comboBox? If so, open the properties, go to the control
source property and delete whatever is there. You will then need to put code
on the combobox to direct it to the selected record. This may help:

http://www.mvps.org/access/forms/frm0005.htm

 

[Thomas Kroljic]

Dirk,
First off, thanks for the great explanation.
I understand your first paragraph, but when I try to get through the
second, I start to get confused.

<<and automatically

finds that record and matches it up to the DepositLog record in place of
the Customer record that was previously matched to this record>>

Not sure I fully understand the above sentence. What do you mean it "matches
it up to the DepositLog record in place of the Customer record what was
previouly matched to this record"?

If the recordsource query joins the depositLog and Customer table via the
customerID, and the join says "only include rows where the joined fields
from both tables are equal", wouldn't the query fail if you select a
customer (customerID) that currently is not on file in the DepositLog table?

Does the form still retain the underlying record it initally displayed
(DepositLog data) even though the CustomerID changed? I think it does, since
any changes are saved to the record that was initially displayed.

Is there some doc on how this "autolookup" works that I can read?

Thomas j. Kroljic

 

[Thomas Kroljic]

Sam,
Thanks for replying. But I think Dirk (see other posting) has an
explanation that works.
Not sure I fully understand how the "autolookup" works with the underlying
recordsource query. Hopefully Dirk will give me some more info so I can get
a clear picture.

Thanks,
Thomas J. Kroljic

 

[Thomas Kroljic]

Dirk,

Is this what you're talking about (see below)? After my last posting, I
went back into my Access app and went into Help and did a lookup on
"autolookup".

Now I understand your explanation fully. And yes, it's pretty Cool.

Thanks for making me think a little bit more and helphing me to understand
something I didn't know before.

-------------------

You can design a multiple-table query to automatically fill in certain field
values for a new record. When you enter a value in the join field in the
query, or in a form, report, or data access page based on the query,
Microsoft Access looks up and fills in existing information related to that
value. For example, if you know the value in the join field between a
Customers table and an Orders table (typically, a customer identifier such
as CustomerID), you could enter the customer ID and have Access enter the
rest of the information for that customer. If no matching information is
found, Access displays an error message when the focus leaves the record.



Add a new order including the Customer ID, and then press ENTER.

Access fills in the rest of the customer information.

Thanks,

Thomas j. Kroljic

 

[Dirk Goldgar]

Dirk,

Is this what you're talking about (see below)? After my last
posting, I went back into my Access app and went into Help and did a
lookup on "autolookup".

Yes, that's exactly what I'm talking about. Your research was good.