Autopopulate text box based on an unbound combo box

[rocketD]

Hi All,

I've read the autopopulate-text-box-based-on-combo-box threads, but I
still can't seem to get mine to work. My combo box is unbound, but
draws its info from a SELECT statement, specifically a join query that
links a bunch of tables. The combo box, called targetID, draws 3
fields, and I have them set to display as 0";1";0". I want the text
box, called API14, to autopopulate with the info from the third column
in the targetID combo box when an item is selected.

The combo box is set up to refresh (Me.Refresh) OnChange so that a
subform linked to its value will refresh and display relevant
records. To populate the text box, I've also included the following
AfterUpdate procedure:

Private Sub targetID_AfterUpdate()
Me.API14 = Me.targetID.Column(3)
End Sub

However, when the targetID is selected, the API14 remains blank. Can
anyone please tell me what I'm doing wrong?

Thanks,
Dara

 

[boblarson]

Are you sure -

1. You want the FOURTH column as Column(3) is the fourth column since it is
zero based.

2. You have set the NUMBER OF COLUMNS property of the combo box to at least
4 (or 3 if you want the 3rd column and using Column(2) )

 

[Ken Sheridan]

Dara:

To assign the value of the third column to the text box use:

Me.API14 = Me.targetID.Column(2)

Don't put anything in its Change event procedure.

I assume that your subform is based on a query which references the combo
box as a parameter, in which case requery the subform in the combo box's
AfterUpdate event procedure with:

Me.YourSubformControl.Requery

where YourSubformControl is the name of the control in the parent form which
houses the subform.

However, you probably don't need to do that at all. Simply base the subform
on a query without parameters and set the LinkMasterFields property of the
subform control to the name of the combo box:

targetID

and its LinkChildFields property to the name of the corresponding column in
the subform's underlying query.

I must admit to being a little puzzled by this approach. You appear to be
assigning the value of a non-key column to a bound control. I'd have
envisaged the parent form's underlying table having a foreign key targetID
column to which the combo box is bound, and the API14 text box being an
unbound control with a ControlSource property of:

=targetID.Column(2)

In which case the LinkmasterFields property of the subform control would
then be the name of the column to which the combo box is bound rather than
the name of the control.

Ken Sheridan
Stafford, England

 

[John Spencer]

You want to use 2 not 3. Columns in this context are numbered starting with
zero.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[rocketD]

Dara:

To assign the value of the third column to the text box use:

     Me.API14 = Me.targetID.Column(2)

Don't put anything in its Change event procedure.

I assume that your subform is based on a query which references the combo
box as a parameter, in which case requery the subform in the combo box's
AfterUpdate event procedure with:

    Me.YourSubformControl.Requery

where YourSubformControl is the name of the control in the parent form which
houses the subform.

However, you probably don't need to do that at all.  Simply base the subform
on a query without parameters and set the LinkMasterFields property of the
subform control to the name of the combo box:

    targetID

and its LinkChildFields property to the name of the corresponding column in
the subform's underlying query.

I must admit to being a little puzzled by this approach.  You appear tobe
assigning the value of a non-key column to a bound control.  I'd have
envisaged the parent form's underlying table having a foreign key targetID
column to which the combo box is bound, and the API14 text box being an
unbound control with a ControlSource property of:

    =targetID.Column(2)

In which case the LinkmasterFields property of the subform control would
then be the name of the column to which the combo box is bound rather than
the name of the control.

Ken Sheridan
Stafford, England










- Show quoted text -

Thanks so much to everyone who replied. I've got it working given
your suggestions.

In response to Ken - I'm not sure what you're talking about; I admit
to having to stumble my way through Access for the most part. My form
is not actually based on anything, that is, a table or a query. It's
got a set of unbound controls, which reference information from a
query that pulls from several tables. It's also got a couple of
subforms, so the users can look at things related to the items chosen
in the unbound controls (most of which are drop-down boxes. I want to
be able to add a function that allows the user to add new records to
the table after selecting the options from the combos and viewing the
resulting data in the subforms, but I haven't gotten to that point
yet. I think that might be where you were saying I need to base my
form on a table, but as I said, I haven't tackled that yet. The
databases I was thrown into are incredibly complicated and have no
dictionary for their data so I'm having to do things in baby steps.

The fields I was referencing above, targetID and API14, are linked
such that targetID is a unique identifier for an oil well, which is
referred to by one of two names - an alphanumeric well name (used
mostly by engineers to talk about their wells - that's what you
actually see in the combo box) and a numeric name (API14, which
engineers don't know their wells by, but must use as an index to file,
report, and store information on them). I need to show the API14 so
that the engineers have it handy and don't need to go looking for it
elsewhere, but what they want to select info by is the well name.
Underneath, both are referenced by the unique targetID. What I wanted
my subforms to do is to show records for the well they select, but the
relevant info comes from a bunch of tables, so I use a query, and
within the query, specify that I want to see records Like [forms].
[parentForm].[targetID].

It all works now, thanks to everyone who responded, but I have no
doubt there are better ways to do it (learning them is my problem,
though, because I'm the resident "expert" in Access, and as you can
tell, that's not saying much).

Thanks again,
Dara