Prev: Calculating Dates:
Next: FollowHyperlink no longer working
From: JohnE on 12 Mar 2010 11:36
I have a mainform and a subform. In the continuous subform there is a
combobox that has the id and name. Next to it there is a textbox that is to
show info regarding the name from the combobox. The info that is to populate
the txtbox comes from a different table. Currently I am trying the following;

Me.ChargeAccountType = "SELECT ChargeAccountType FROM ChargeAccountsQ WHERE
ChargeAccountType = Me.ChargeAccountID.Column(1)"

but only get in the txtbox what is between the quotes.

How should this be written to get the info?

Thanks...John


From: Marshall Barton on 12 Mar 2010 12:30
JohnE wrote:
>I have a mainform and a subform. In the continuous subform there is a
>combobox that has the id and name. Next to it there is a textbox that is to
>show info regarding the name from the combobox. The info that is to populate
>the txtbox comes from a different table. Currently I am trying the following;
>
>Me.ChargeAccountType = "SELECT ChargeAccountType FROM ChargeAccountsQ WHERE
>ChargeAccountType = Me.ChargeAccountID.Column(1)"
>
>but only get in the txtbox what is between the quotes.


That would be more like:

Me.ChargeAccountType = DLookup("ChargeAccountType", _
"ChargeAccountsQ", "ChargeAccountType = " _
& Me.ChargeAccountID.Column(1))

But that may be kind of slow. It is usually better to
change the row source query to link to the ChargeAccountsQ
table and include the type field. Then you would not need
any code because the text box could just use an expression
like =ChargeAccountID.Column(2)

--
Marsh
MVP [MS Access]
From: JohnE on 12 Mar 2010 13:22
'Mr Barton, your second idea was the ticket for this. Tough to see the
forest if not for the trees' sometimes.
Thanks.
John


"Marshall Barton" wrote:

> JohnE wrote:
> >I have a mainform and a subform. In the continuous subform there is a
> >combobox that has the id and name. Next to it there is a textbox that is to
> >show info regarding the name from the combobox. The info that is to populate
> >the txtbox comes from a different table. Currently I am trying the following;
> >
> >Me.ChargeAccountType = "SELECT ChargeAccountType FROM ChargeAccountsQ WHERE
> >ChargeAccountType = Me.ChargeAccountID.Column(1)"
> >
> >but only get in the txtbox what is between the quotes.
>
>
> That would be more like:
>
> Me.ChargeAccountType = DLookup("ChargeAccountType", _
> "ChargeAccountsQ", "ChargeAccountType = " _
> & Me.ChargeAccountID.Column(1))
>
> But that may be kind of slow. It is usually better to
> change the row source query to link to the ChargeAccountsQ
> table and include the type field. Then you would not need
> any code because the text box could just use an expression
> like =ChargeAccountID.Column(2)
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: Marshall Barton on 13 Mar 2010 14:56
JohnE wrote:

>'Mr Barton, your second idea was the ticket for this. Tough to see the
>forest if not for the trees' sometimes.

Can't have a forest without the trees ;-)

Glad to help.
--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Calculating Dates:
Next: FollowHyperlink no longer working