VBA Code to TextBox Vs. ComboBox

  • Thread starter Thread starter Thomas Wright
  • Start date Start date
T

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;"
 
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])
 
Thomas Wright said:
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;"
Click to expand...

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


Dirk Goldgar said:
Thomas Wright said:
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;"
Click to expand...

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)
Click to expand...
 
Thomas Wright said:
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


Dirk Goldgar said:
Thomas Wright said:
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;"
Click to expand...

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.
Click to expand...
Click to expand...

It looks to me as though Steve Schapel has hit it on the head. Try his
suggestion.
 
Steve, I'm hopeful but I get the error message, Data type mismatch in
criteria expression". I'm working on that right now.

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] & "'")
 
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


Steve Schapel said:
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


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

Tom
Click to expand...
Click to expand...
 
Back
Top