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

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


"Rachel" <(E-Mail Removed)> wrote in message
news:17d7701c44994$4f4bed60$(E-Mail Removed)...
> 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

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
>
>
>"Rachel" <(E-Mail Removed)> wrote in
message
>news:17d7701c44994$4f4bed60$(E-Mail Removed)...
>> 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
>
>
>.
>

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.

--
Wayne Morgan
MS Access MVP


"Rachel" <(E-Mail Removed)> wrote in message
news:17fcd01c4499b$9dc26da0$(E-Mail Removed)...
> 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.