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

Noel Sant wrote:

>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.

--
Marsh
MVP [MS Access]

"Noel Sant" <(E-Mail Removed)> wrote in message
news:r7Xve.7525$(E-Mail Removed)
> 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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"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