Displaying unbound column data

[Rachel]

I have a combobox whose row source is dependent on another
control on the form. The bound column is a numeric code,
but the text description is what shows on the form. When
the form opens, the stored information is not displayed in
the combobox.

I was told the way to solve this is to place a text box on
top of the combobox to display the save information. I
can get it to display the bound column, but I cannot get
it to display the unbound one.

How do I do this? The unbound text comes from two
different tables depending on that other control. I tried
writing a function to look it up, but I get the #Name?
error.

Here is the function:
If Forms!frmAllPts![Type of access] = "Fistula" Then
Forms!frmAllPts![Why].ControlSource = "qryConsentFistula"
ElseIf Forms!frmAllPts![Type of access] = "Graft" Then
Forms!frmAllPts![Why].ControlSource = "qryConsentGraft"
Else: Forms!frmAllPts![Why].ControlSource = ""

And this is what the querys look like. (They're both the
same but just based on different tables.)

SELECT tblReasonsNotConsentedFistula.Reason
FROM tblReasonsNotConsentedFistula INNER JOIN tblFullScale
ON tblReasonsNotConsentedFistula.ID = tblFullScale.[If not
consented, why?]
WHERE (((tblFullScale.[If not consented, why?])=
[tblReasonsNotConsentedFistula]![ID]));

Can anyone help me?

Thanks,
Rachel

 

[Wayne Morgan]

Your "stored info" is probably not being displayed because the combobox is
set to LimitToList=Yes and since the Row Source changes, the stored info may
not be in the list. To refer to a column other than the bound column, use
the column property of the combobox.

=cboMyCombo.Column(1)

This would display the 2nd column's value (the number is zero based).

 

[Rachel]

This sort of worked, but it gives me the same problem as
the combobox. Because the combobox control is based on
the update of a different control, when the form is
opened, no data displays on the form. It's only the bound
column data that is stored in the table. And only when I
update the other control does the information show up.

-----Original Message-----
Your "stored info" is probably not being displayed because the combobox is
set to LimitToList=Yes and since the Row Source changes, the stored info may
not be in the list. To refer to a column other than the bound column, use
the column property of the combobox.

=cboMyCombo.Column(1)

This would display the 2nd column's value (the number is zero based).

--
Wayne Morgan
Microsoft Access MVP

I have a combobox whose row source is dependent on another
control on the form. The bound column is a numeric code,
but the text description is what shows on the form. When
the form opens, the stored information is not displayed in
the combobox.

I was told the way to solve this is to place a text box on
top of the combobox to display the save information. I
can get it to display the bound column, but I cannot get
it to display the unbound one.

How do I do this? The unbound text comes from two
different tables depending on that other control. I tried
writing a function to look it up, but I get the #Name?
error.

Here is the function:
If Forms!frmAllPts![Type of access] = "Fistula" Then
Forms!frmAllPts![Why].ControlSource = "qryConsentFistula"
ElseIf Forms!frmAllPts![Type of access] = "Graft" Then
Forms!frmAllPts![Why].ControlSource = "qryConsentGraft"
Else: Forms!frmAllPts![Why].ControlSource = ""

And this is what the querys look like. (They're both the
same but just based on different tables.)

SELECT tblReasonsNotConsentedFistula.Reason
FROM tblReasonsNotConsentedFistula INNER JOIN tblFullScale
ON tblReasonsNotConsentedFistula.ID = tblFullScale.[If not
consented, why?]
WHERE (((tblFullScale.[If not consented, why?])=
[tblReasonsNotConsentedFistula]![ID]));

Can anyone help me?

Thanks,
Rachel

.

 

[Wayne Morgan]

In that case, you could use a DLookup statement in the textbox, using the
combobox's bound column as the filter part of the statement.