Vba code for After Update

S

Steve A

I have a form sourced to a table, and on the form a combo box refering to a
different table which has a link to the main table. On the linked table I
have two fields, one which is the link and another that I want the value of
entering into a text box when I choose the approptiate link.

I don't want the text box to reference the other table, but to take the
value from it.

I know this is possible as I've seen it done, yet I am unable to see how
this was done.

Can anyone help?

Thanks!
 
S

Sprinks

Hi, Steve A. I'm not sure I'm diagnosing your problem correctly, but I'll
take a shot. First, to clarify a few properties of a combo box.

The RowSource is the SQL statement specifying from where the rows of the
combo are populated. This will include one or more fields which appear as
successive columns in the combo box. The Bound Column is the value stored in
the combo box' ControlSource--a field of the form's underlying RecordSource.
This may be different from what is *displayed*, which is the first
non-zero-width column as specified in the ColumnWidths property.

In most cases, a combo box is used to allow a user to enter a text-based
selection, while storing the numeric primary key associated with it. This
would be done by setting the BoundColumn to 1 and the first ColumnWidth to 0".

As an example, say your Customer table has a CustomerNumber and CustomerName
fields. Your user would type or select the CustomerName, but the
CustomerNumber would be stored in the underlying table.

If all you're looking to do is *display* the text-based field, this should
do it. If you actually want to *store* the text, although without knowing
more, I can't think of a good reason to do this, set the Bound Column to the
appropriate column number.

As an aside, if you wish to display other combo box columns in other
textboxes, you can use the CB's Column property in the textbox'
ControlSource. Unfortunately, Access is not consistent; in using this
property, the first column is 0, the 2nd, 1, etc., e.g.:

= MyComboBox.Column(2) '3rd column

Hope that helps.
Sprinks
 
S

Steve A

Hi Spinks
Perhaps I should have explained it better, at its most basic what I am
attempting is as follows...

I have a combo box sourced to a table called "predicted" this contains two
fields "Service" and "Mileage" Service is a text value, but is the primary
key, as it is always going to be a unique bus service number, mileage is the
predicted mileage of said service.

The form with my combo box on is sourcing a different table, (runs) where
the mileage of vehicle on each run are recorded.

On my form I input start, finish, etc and choose from the combo, the route
it did. When I select a route (after update) I want an empty text box to
display the value of the predicted miles associated with this route. I will
then be able to use this compared to a calculated total to see whether my
mileages match or not.

i hope this makes it easier to understand.

Thanks

Steve
 
S

Sprinks

Got it, Steve. Just include the Mileage field in your SQL query statement
for the combo box, and set a textbox' Control Source using the Column
property:

ComboBox RowSource: SELECT Predicted.Service, Predicted.Mileage FROM
Predicted ORDER BY Predicted.Service;

Textbox ControlSource: =YourComboBox.Column(1)

Hope that helps. As I predict construction costs for a living, I appreciate
where you're coming from!

Sprinks
 
Top