VBA Code to TextBox Vs. ComboBox

[Thomas Wright]

I'm using the following VBA code to setup a value for a comboBox in a Form.
It works well as long as on the form you manually drop down the box to find
the value. I would like to have the value just sent to a TextBox. Deleting
ROWSOURCE and putting parentheses around the expression only results in a
TextBox that shows the formula. How can I get this value to just pop up in
my TextBox? This should be just automated without the requirement to
manually look it up.

I've been working on this for days,

Thanks,

Tom Wright

Me!PrevAdmission.RowSource = "SELECT [Hospital Admissions
Query2].[CountOfLaMerNumber], [Hospital Admissions
Query2].[LaMerNumber],[Hospital Admissions Query2].[FirstName],[Hospital
Admissions Query2].[LastName] FROM [Hospital Admissions Query2] WHERE
[Hospital Admissions Query2].[LaMerNumber]=[Forms]![Hospital Discharge
Information for Each Patient].[LaMerNumber].Value;"

 

[Steve Schapel]

Tom,

I'm not sure if I really understand the question, but here's my guess...

Me.PrevAdmission = DLookup("[CountOfLaMerNumber]","[Hospital Admissions
Query2]","[LaMerNumber]=" & [Forms]![Hospital Discharge Information for
Each Patient].[LaMerNumber])

 

[Dirk Goldgar]

I'm using the following VBA code to setup a value for a comboBox in a
Form. It works well as long as on the form you manually drop down the
box to find the value. I would like to have the value just sent to a
TextBox. Deleting ROWSOURCE and putting parentheses around the
expression only results in a TextBox that shows the formula. How can
I get this value to just pop up in my TextBox? This should be just
automated without the requirement to manually look it up.

I've been working on this for days,

Thanks,

Tom Wright

Me!PrevAdmission.RowSource = "SELECT [Hospital Admissions
Query2].[CountOfLaMerNumber], [Hospital Admissions
Query2].[LaMerNumber],[Hospital Admissions
Query2].[FirstName],[Hospital Admissions Query2].[LastName] FROM
[Hospital Admissions Query2] WHERE [Hospital Admissions
Query2].[LaMerNumber]=[Forms]![Hospital Discharge Information for
Each Patient].[LaMerNumber].Value;"

I'm afraid I don't understand what you're trying to do. Setting a combo
box's RowSource property does *not* set its value property, so your code
for the combo box doesn't do for the combo what I *think* you're saying
you want to do for the text box. I think you're trying to get some
value from this "Query2" -- which I can tell you how to do -- but I
don't know which of the fields you're selecting is the field you want.

 

[Thomas Wright]

I'm trying to get CountOfLaMerNumber to pop up. On the Form I can manually
select the count (value) but why can't I just get it to popup into the box,
either Combo or Text box?

Tom

I'm using the following VBA code to setup a value for a comboBox in a
Form. It works well as long as on the form you manually drop down the
box to find the value. I would like to have the value just sent to a
TextBox. Deleting ROWSOURCE and putting parentheses around the
expression only results in a TextBox that shows the formula. How can
I get this value to just pop up in my TextBox? This should be just
automated without the requirement to manually look it up.

I've been working on this for days,

Thanks,

Tom Wright

Me!PrevAdmission.RowSource = "SELECT [Hospital Admissions
Query2].[CountOfLaMerNumber], [Hospital Admissions
Query2].[LaMerNumber],[Hospital Admissions
Query2].[FirstName],[Hospital Admissions Query2].[LastName] FROM
[Hospital Admissions Query2] WHERE [Hospital Admissions
Query2].[LaMerNumber]=[Forms]![Hospital Discharge Information for
Each Patient].[LaMerNumber].Value;"

I'm afraid I don't understand what you're trying to do. Setting a combo
box's RowSource property does *not* set its value property, so your code
for the combo box doesn't do for the combo what I *think* you're saying
you want to do for the text box. I think you're trying to get some
value from this "Query2" -- which I can tell you how to do -- but I
don't know which of the fields you're selecting is the field you want.

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

(please reply to the newsgroup)

 

[Steve Schapel]

Tom,

Did you try the method I suggested in my previous reply?

 

[Dirk Goldgar]

I'm trying to get CountOfLaMerNumber to pop up. On the Form I can
manually select the count (value) but why can't I just get it to
popup into the box, either Combo or Text box?

Tom

I'm using the following VBA code to setup a value for a comboBox in
a Form. It works well as long as on the form you manually drop down
the box to find the value. I would like to have the value just sent
to a TextBox. Deleting ROWSOURCE and putting parentheses around the
expression only results in a TextBox that shows the formula. How can
I get this value to just pop up in my TextBox? This should be just
automated without the requirement to manually look it up.

I've been working on this for days,

Thanks,

Tom Wright

Me!PrevAdmission.RowSource = "SELECT [Hospital Admissions
Query2].[CountOfLaMerNumber], [Hospital Admissions
Query2].[LaMerNumber],[Hospital Admissions
Query2].[FirstName],[Hospital Admissions Query2].[LastName] FROM
[Hospital Admissions Query2] WHERE [Hospital Admissions
Query2].[LaMerNumber]=[Forms]![Hospital Discharge Information for
Each Patient].[LaMerNumber].Value;"

I'm afraid I don't understand what you're trying to do. Setting a
combo box's RowSource property does *not* set its value property, so
your code for the combo box doesn't do for the combo what I *think*
you're saying you want to do for the text box. I think you're
trying to get some value from this "Query2" -- which I can tell you
how to do -- but I don't know which of the fields you're selecting
is the field you want.

It looks to me as though Steve Schapel has hit it on the head. Try his
suggestion.

 

[Thomas Wright]

Steve, I'm hopeful but I get the error message, Data type mismatch in
criteria expression". I'm working on that right now.

Tom

 

[Steve Schapel]

Tom,

I assumed LaMerNumber was a number. If it's a text data type, you will
need this instead...

Me.PrevAdmission = DLookup("[CountOfLaMerNumber]","[Hospital Admissions
Query2]","[LaMerNumber]='" & [Forms]![Hospital Discharge Information for
Each Patient].[LaMerNumber] & "'")

 

[Thomas Wright]

It was a number but you were right, even if for a different reason.

Thank you, I've been working on this for weeks using the other method.



Tom

Tom,

I assumed LaMerNumber was a number. If it's a text data type, you will
need this instead...

Me.PrevAdmission = DLookup("[CountOfLaMerNumber]","[Hospital Admissions
Query2]","[LaMerNumber]='" & [Forms]![Hospital Discharge Information for
Each Patient].[LaMerNumber] & "'")

--
Steve Schapel, Microsoft Access MVP

Steve, I'm hopeful but I get the error message, Data type mismatch in
criteria expression". I'm working on that right now.

Tom