How to access a field in a record not bound to the form, but already accessed by a combo box query

[Noel Sant]

I've come back to Access VBA after a long time and find myself confused.

I have a form with two combo boxes, each bound to a different table. The
user should choose a record from one or the other. I also have a text box
where I want to put the Description of the item chosen, from whichever table
has been used.

So I have a change event for each combo box. Surely, since the user has just
chosen a field (the Name) in a record from the particular table, the whole
of that record should be available? How do I get at it? The form is bound to
a different table and the text box is unbound.

The fact is the Access object models confuse me - all I need is an example
(I hope).

Many thanks,

Noel Sant

 

[Marshall Barton]

I've come back to Access VBA after a long time and find myself confused.

I have a form with two combo boxes, each bound to a different table. The
user should choose a record from one or the other. I also have a text box
where I want to put the Description of the item chosen, from whichever table
has been used.

So I have a change event for each combo box. Surely, since the user has just
chosen a field (the Name) in a record from the particular table, the whole
of that record should be available? How do I get at it? The form is bound to
a different table and the text box is unbound.

You should use the combo box's AfterUpdate event instead of
the Change event. The Change event fires on every keystroke
before an item in the list is actually selected.

The syntax to refer to a field other than the bound column:

Me.thetextbox = Me.thecombobox.Columns(N)

where N is the zero based column number of the field you
want to copy.

 

[Dirk Goldgar]

I've come back to Access VBA after a long time and find myself
confused.

I have a form with two combo boxes, each bound to a different table.
The user should choose a record from one or the other. I also have a
text box where I want to put the Description of the item chosen, from
whichever table has been used.

So I have a change event for each combo box.

General suggestion: don't use the combo box's Change event. Use the
AfterUpdate event. If the user types in the combo, rather than using
the mouse to select an item, the Change event fires for every keystroke.
That's not usually what you want.

Surely, since the user
has just chosen a field (the Name) in a record from the particular
table, the whole of that record should be available?

No "surely" about it!

How do I get at
it? The form is bound to a different table and the text box is
unbound.

The fact is the Access object models confuse me - all I need is an
example (I hope).

The data from the combo box's rowsource table/query is only available in
the combo box if it was selected as one of the columns of the combo. If
it was, then you can use the control's Column property to access it.
Consider this example:

Combo Box "cboMyCombo"
-------------------------------
Row Source: SELECT ItemID, ItemName, ItemDesc
FROM tblItems;

Column Count: 3
Bound Column: 1
Column Widths: 0"; 1", 2"

In code,

Me!cboMyCombo
returns the value of ItemID for the chosen item

Me!cboMyCombo.Column(0)
returns the ItemID for the chosen item, formatted as a string

Me!cboMyCombo.Column(1)
returns the ItemName for the chosen item, formatted as a string

Me!cboMyCombo.Column(2)
returns the ItemDesc for the chosen item, formatted as a string

 

[Noel Sant]

"Dirk Goldgar" wrote>

General suggestion: don't use the combo box's Change event. Use the
AfterUpdate event. If the user types in the combo, rather than using
the mouse to select an item, the Change event fires for every keystroke.
That's not usually what you want.

Yes, I'll do that. The user (who will always be me) is not meant to type
into the box, just to pick a possibility, but I know that in practice, if
there's a long list to choose from, I sometimes start typing to position
myself nearer the entry I want. And I can see that that would cause problems
if I use the Change event.

The data from the combo box's rowsource table/query is only available in
the combo box if it was selected as one of the columns of the combo. If
it was, then you can use the control's Column property to access it.
Consider this example:

Combo Box "cboMyCombo"
-------------------------------
Row Source: SELECT ItemID, ItemName, ItemDesc
FROM tblItems;

Column Count: 3
Bound Column: 1
Column Widths: 0"; 1", 2"

In code,

Me!cboMyCombo
returns the value of ItemID for the chosen item

Me!cboMyCombo.Column(0)
returns the ItemID for the chosen item, formatted as a string

Me!cboMyCombo.Column(1)
returns the ItemName for the chosen item, formatted as a string

Me!cboMyCombo.Column(2)
returns the ItemDesc for the chosen item, formatted as a string

I wanted to use row names so that I could change the table's structure
without having to change the code. But if I use your example, but have:

Column Widths: 0"; 1", 0"

then it comes to the same thing. Thank you! That's great.

Regards,

Noel