Combo Issue

  • Thread starter Thread starter David
  • Start date Start date
D

David

HI there. I'm sure this has been asked before, or at
least a variation of it, but I wasn't able to find my
answer by browsing or using the search function.

I have a combo box linked to a query so that the
appropriate Field is listed. However, I want this to
auto populate the rest of the appropriate fileds on the
form when I select my choice.

I'm really new to access and this is stumping me, as I'm
sure many other issues will. Apologies for any repost.
 
David said:
HI there. I'm sure this has been asked before, or at
least a variation of it, but I wasn't able to find my
answer by browsing or using the search function.

I have a combo box linked to a query so that the
appropriate Field is listed. However, I want this to
auto populate the rest of the appropriate fileds on the
form when I select my choice.

I'm really new to access and this is stumping me, as I'm
sure many other issues will. Apologies for any repost.
Click to expand...

More often than not the correct approach is to NOT do what you're
attempting. Lookup tables should not be though of as templates to make
*copying* data into your records easier. They should be thought of as a
place to *Retrieve* data so you don't have to copy it in the first place.

SALES ORDER EXAMPLE:

Table CUSTOMERS where information about customers is stored Primary Key =
CustomerID

Table ORDERS where we enter new orders.

To identify the customer on the order all I want to store as part of the
order record is the CustomerID. If I want to *SEE* more information about
the customer when looking at the Orders form I use a method that retrieves
the additional data from CUSTOMERS based on the CustomerID and displays it
on my Orders form, but it is not stored as part of the order record.

EXCEPTION:

The above scenario assumes that whenever you look at an Order you want to
see displayed the current data for the customer. If the order is entered
and then viewed later after the customer's phone number has changed you
would still see the current phone number since it is being retrieved from
the CUSTOMERS table and was not stored as part of the order. However; the
CUSTOMERS table might include data that needs to be part of the order
record because when looking at the order we need to see what the value was
"when the order was created". An example of this would be the customer's
discount rate. This DOES need to be copied to the Order because when
looked at a year later you need to see what his discount rate was when the
order was entered (not what it is today).

So...any part of the data from the lookup that needs to be a snapshot at
the time of your record creation should be copied over. Anything that
doesn't require this should be retrieved and displayed, but not saved as
part of the new record.

Both of these can be accomplished by adding more columns to your ComboBox
that contain the additional data. These can be hidden by setting their
width to zero. To *COPY* data from these columns into the record you are
creating you use code in the AfterUpdate event of the ComboBox similar
to...

Me.SomeControl = Me.ComboBoxName.Column(1)
Me.SomeOtherControl = Me.ComboBoxName.Column(2)
etc..

To *DISPLAY* data on your form without copying it use TextBoxes that have
ControlSources similar to...

=Me.ComboBoxName.Column(3)
=Me.ComboBoxName.Column(4)
etc..
 
I think I get what you're saying. I am, however,
actually only trying to call forth data. At this point
we are not going to be adding any information in on the
fly. I want to have this database working in incraments
and have it develop. Plus, that way I'll have a better
understanding.

Anyway, with what you said I was able to partially
accomplish what I was looking for. I don't think it
should have any inpact, but the following is the way I
have my form setup:

______ ______
Combo |Text |
________________________________________
Text |Text |Text| Text| Text| Text| Text|
________________________________________
Text |Text |Text|
________________

Using what you said, I was able to get the text box
immediately to the right of the combo box to change when
I selected an item from the combo box. I accomplished
this by simly putting =NameofComboBox.Column(1) in the
source field for text box to the right. Once that worked
I proceeded to do the same for all of the text boxes in
the second row. I simply put =NameofComboBox.Column(x),
where x is obviously the number that corresponds with the
appropriate column. However, after I did this the only
field that would change in unision was the text box to
the immediate right of the combobox. Meanwhile, all of
the fields in row two stayed blank and did not change.

What am I missing here? Do I need to have something
different in the row source? Does my combo box need to
not be on the form itself if I want all of the fields on
the for to change? Do I need some sort of afteraction
query?

David
 
Back
Top