Text Box Lookup from Combo Box

[Jecker]

I have a table that contains 'Plans" and "Plan Reports". The "Plan Reports"
is the name of the report that coincides to the Plan. I have a combo box
that reflects all the "Plans". What I need to do is have the Text Box look
at what is chosen in the Combo Box and relfect the report that coincides with
the "Plan" in the text box. I eventually want my form to run the report that
coincides with the "Plan" that is reflected in the text box.

Any assistance would be most appreciated

Thanks,
Jecker

 

[Ken Snell MVP]

See this article:
http://www.mvps.org/access/forms/frm0058.htm

 

[Jack Leach]

If you'd like, you can configure your combo box to show both of these, or
store data for the Plan while showing only the Plan Reports... this is
generally a more practiced approach. If not you can set a textbox to the
Plan Report based on the Plan selected in the combo as well.

To do this with only a combo box, showing the Plan and Plan Report columns
from your table, here's some properties to set (location of the properties is
based on 2003 and previous versions, not sure how they're located in 07):


on the Data tab of the properties box:
Row Source Type: Table/Query
Row Source: the table that holds your two columns
Bound Column: 1

and on the Format tab:
Column Count: 2
Column Widths: 0.5"; 1"

This will show both columns, Plan and Plan Reports, while storing the value
of column one (presumably "Plans")... assuming this is a bound control it
will store the value, anyway.

If you want, you can hide the "Plan" column by setting the column widths to
0"; 1"
This will hide the Plans column, and only show reports, but will still save
the value as the Plan column assuming the control is bound to a field.




Or.... if you want to continue using a textbox, use the AfterUpdate event of
the Combobox to set the value of the text box:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub

This assumes that "Plan" is a number field... if it's a string you need to
enclose it in quotes....
"[Plans] = """ & Me.Combobox & """", "")



hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

oops... I forgot a closing parenthese in the expression:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub [...]
"[Plans] = """ & Me.Combobox & """", "")

s/b

Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, ""))

and

...."[Plans] = """ & Me.Combobox & """", ""))


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

If you'd like, you can configure your combo box to show both of these, or
store data for the Plan while showing only the Plan Reports... this is
generally a more practiced approach. If not you can set a textbox to the
Plan Report based on the Plan selected in the combo as well.

To do this with only a combo box, showing the Plan and Plan Report columns
from your table, here's some properties to set (location of the properties is
based on 2003 and previous versions, not sure how they're located in 07):


on the Data tab of the properties box:
Row Source Type: Table/Query
Row Source: the table that holds your two columns
Bound Column: 1

and on the Format tab:
Column Count: 2
Column Widths: 0.5"; 1"

This will show both columns, Plan and Plan Reports, while storing the value
of column one (presumably "Plans")... assuming this is a bound control it
will store the value, anyway.

If you want, you can hide the "Plan" column by setting the column widths to
0"; 1"
This will hide the Plans column, and only show reports, but will still save
the value as the Plan column assuming the control is bound to a field.




Or.... if you want to continue using a textbox, use the AfterUpdate event of
the Combobox to set the value of the text box:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub

This assumes that "Plan" is a number field... if it's a string you need to
enclose it in quotes....
"[Plans] = """ & Me.Combobox & """", "")



hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have a table that contains 'Plans" and "Plan Reports". The "Plan Reports"
is the name of the report that coincides to the Plan. I have a combo box
that reflects all the "Plans". What I need to do is have the Text Box look
at what is chosen in the Combo Box and relfect the report that coincides with
the "Plan" in the text box. I eventually want my form to run the report that
coincides with the "Plan" that is reflected in the text box.

Any assistance would be most appreciated

Thanks,
Jecker

 

[Jecker]

I'm not a good VB writer - I think I may have left out either a ( or "". Can
you please re-write the code so that I know that I'm getting all the "" and
().

Thanks so much!
Jecker

oops... I forgot a closing parenthese in the expression:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub [...]
"[Plans] = """ & Me.Combobox & """", "")

s/b

Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, ""))

and

..."[Plans] = """ & Me.Combobox & """", ""))


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

If you'd like, you can configure your combo box to show both of these, or
store data for the Plan while showing only the Plan Reports... this is
generally a more practiced approach. If not you can set a textbox to the
Plan Report based on the Plan selected in the combo as well.

To do this with only a combo box, showing the Plan and Plan Report columns
from your table, here's some properties to set (location of the properties is
based on 2003 and previous versions, not sure how they're located in 07):


on the Data tab of the properties box:
Row Source Type: Table/Query
Row Source: the table that holds your two columns
Bound Column: 1

and on the Format tab:
Column Count: 2
Column Widths: 0.5"; 1"

This will show both columns, Plan and Plan Reports, while storing the value
of column one (presumably "Plans")... assuming this is a bound control it
will store the value, anyway.

If you want, you can hide the "Plan" column by setting the column widths to
0"; 1"
This will hide the Plans column, and only show reports, but will still save
the value as the Plan column assuming the control is bound to a field.




Or.... if you want to continue using a textbox, use the AfterUpdate event of
the Combobox to set the value of the text box:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub

This assumes that "Plan" is a number field... if it's a string you need to
enclose it in quotes....
"[Plans] = """ & Me.Combobox & """", "")



hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have a table that contains 'Plans" and "Plan Reports". The "Plan Reports"
is the name of the report that coincides to the Plan. I have a combo box
that reflects all the "Plans". What I need to do is have the Text Box look
at what is chosen in the Combo Box and relfect the report that coincides with
the "Plan" in the text box. I eventually want my form to run the report that
coincides with the "Plan" that is reflected in the text box.

Any assistance would be most appreciated

Thanks,
Jecker

 

[Jecker]

I'm not a good VB writer - I think I may have left out either a ( or "". Can
you please re-write the code so that I know that I'm getting all the "" and
().

Thanks so much!
Jecker

oops... I forgot a closing parenthese in the expression:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub [...]
"[Plans] = """ & Me.Combobox & """", "")

s/b

Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, ""))

and

..."[Plans] = """ & Me.Combobox & """", ""))


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

If you'd like, you can configure your combo box to show both of these, or
store data for the Plan while showing only the Plan Reports... this is
generally a more practiced approach. If not you can set a textbox to the
Plan Report based on the Plan selected in the combo as well.

To do this with only a combo box, showing the Plan and Plan Report columns
from your table, here's some properties to set (location of the properties is
based on 2003 and previous versions, not sure how they're located in 07):


on the Data tab of the properties box:
Row Source Type: Table/Query
Row Source: the table that holds your two columns
Bound Column: 1

and on the Format tab:
Column Count: 2
Column Widths: 0.5"; 1"

This will show both columns, Plan and Plan Reports, while storing the value
of column one (presumably "Plans")... assuming this is a bound control it
will store the value, anyway.

If you want, you can hide the "Plan" column by setting the column widths to
0"; 1"
This will hide the Plans column, and only show reports, but will still save
the value as the Plan column assuming the control is bound to a field.




Or.... if you want to continue using a textbox, use the AfterUpdate event of
the Combobox to set the value of the text box:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub

This assumes that "Plan" is a number field... if it's a string you need to
enclose it in quotes....
"[Plans] = """ & Me.Combobox & """", "")



hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have a table that contains 'Plans" and "Plan Reports". The "Plan Reports"
is the name of the report that coincides to the Plan. I have a combo box
that reflects all the "Plans". What I need to do is have the Text Box look
at what is chosen in the Combo Box and relfect the report that coincides with
the "Plan" in the text box. I eventually want my form to run the report that
coincides with the "Plan" that is reflected in the text box.

Any assistance would be most appreciated

Thanks,
Jecker

 

[Jecker]

Thank you for your assistance. I'm getting a Compile Error.. Here is my code:
Private Sub cboplans_AfterUpdate()
Me.txtreport.Value = Nz(DLookup("[PlanReport", "tbl_ReportDef", "[Plan]=
""" & Me.cboPlans & """", ""))

End Sub
Here are my definitions:
cboplans = ComboBox
txtreport = Text Box
PlanReport = Report Name in Plan Table
Tbl_ReportDef = Table that holds Plans and Plan Report
Plan = Plan listed in tbl_ReportDef

If you'd like, you can configure your combo box to show both of these, or
store data for the Plan while showing only the Plan Reports... this is
generally a more practiced approach. If not you can set a textbox to the
Plan Report based on the Plan selected in the combo as well.

To do this with only a combo box, showing the Plan and Plan Report columns
from your table, here's some properties to set (location of the properties is
based on 2003 and previous versions, not sure how they're located in 07):


on the Data tab of the properties box:
Row Source Type: Table/Query
Row Source: the table that holds your two columns
Bound Column: 1

and on the Format tab:
Column Count: 2
Column Widths: 0.5"; 1"

This will show both columns, Plan and Plan Reports, while storing the value
of column one (presumably "Plans")... assuming this is a bound control it
will store the value, anyway.

If you want, you can hide the "Plan" column by setting the column widths to
0"; 1"
This will hide the Plans column, and only show reports, but will still save
the value as the Plan column assuming the control is bound to a field.




Or.... if you want to continue using a textbox, use the AfterUpdate event of
the Combobox to set the value of the text box:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub

This assumes that "Plan" is a number field... if it's a string you need to
enclose it in quotes....
"[Plans] = """ & Me.Combobox & """", "")



hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have a table that contains 'Plans" and "Plan Reports". The "Plan Reports"
is the name of the report that coincides to the Plan. I have a combo box
that reflects all the "Plans". What I need to do is have the Text Box look
at what is chosen in the Combo Box and relfect the report that coincides with
the "Plan" in the text box. I eventually want my form to run the report that
coincides with the "Plan" that is reflected in the text box.

Any assistance would be most appreciated

Thanks,
Jecker

 

[David H]

You can use the DLookup() to grab the relevant information from the
underlying table and display it in the text box. In the afterUpdate event of
the comboBox call the .Requery method of the text box.

1) Having done something similar, I would go with a list box that displays
the 'Plans' as it'll make things easier for your users.

2) Set the .ControlSource for the textbox to
=DLookup([FieldName],

,[Value] = [ListBox])

3) In the ListBox's afterupdate event add the [TextBoxName].Requery

Also, depending on the information that you need to display you may want to
size the Text Box accordingly to ensure that its big enough for any possible
value displayed.

 

[Jack Leach]

What's the error number and description?


....DLookup("[PlanReport", "tbl.....

Looks like you forget the closing bracket on [PlanReport] but that shouldn't
cause a compile error, that would be a runtime error.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Thank you for your assistance. I'm getting a Compile Error.. Here is my code:
Private Sub cboplans_AfterUpdate()
Me.txtreport.Value = Nz(DLookup("[PlanReport", "tbl_ReportDef", "[Plan]=
""" & Me.cboPlans & """", ""))

End Sub
Here are my definitions:
cboplans = ComboBox
txtreport = Text Box
PlanReport = Report Name in Plan Table
Tbl_ReportDef = Table that holds Plans and Plan Report
Plan = Plan listed in tbl_ReportDef

If you'd like, you can configure your combo box to show both of these, or
store data for the Plan while showing only the Plan Reports... this is
generally a more practiced approach. If not you can set a textbox to the
Plan Report based on the Plan selected in the combo as well.

To do this with only a combo box, showing the Plan and Plan Report columns
from your table, here's some properties to set (location of the properties is
based on 2003 and previous versions, not sure how they're located in 07):


on the Data tab of the properties box:
Row Source Type: Table/Query
Row Source: the table that holds your two columns
Bound Column: 1

and on the Format tab:
Column Count: 2
Column Widths: 0.5"; 1"

This will show both columns, Plan and Plan Reports, while storing the value
of column one (presumably "Plans")... assuming this is a bound control it
will store the value, anyway.

If you want, you can hide the "Plan" column by setting the column widths to
0"; 1"
This will hide the Plans column, and only show reports, but will still save
the value as the Plan column assuming the control is bound to a field.




Or.... if you want to continue using a textbox, use the AfterUpdate event of
the Combobox to set the value of the text box:

Private Sub Combobox_AfterUpdate()
Me.Textbox = Nz(DLookup("[Plan Reports", "PlansTable", _
"[Plans] = " & Me.Combobox, "")
End Sub

This assumes that "Plan" is a number field... if it's a string you need to
enclose it in quotes....
"[Plans] = """ & Me.Combobox & """", "")



hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have a table that contains 'Plans" and "Plan Reports". The "Plan Reports"
is the name of the report that coincides to the Plan. I have a combo box
that reflects all the "Plans". What I need to do is have the Text Box look
at what is chosen in the Combo Box and relfect the report that coincides with
the "Plan" in the text box. I eventually want my form to run the report that
coincides with the "Plan" that is reflected in the text box.

Any assistance would be most appreciated

Thanks,
Jecker

 

[John Spencer]

Me.txtreport.Value =
Nz(DLookup("PlanReport", "tbl_ReportDef"
, "[Plan]= """ & Me.cboPlans & """"), "")

Your parens were in the wrong place - that will cause the compile error
You had an opening bracket before PlanReport but no closing bracket - that
will cause a runtime error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jack Leach]

Your parens were in the wrong place - that will cause the compile error

sorry Jecker... I should have seen this and haven't been in many positions
to throw a sample line into access today...

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Me.txtreport.Value =
Nz(DLookup("PlanReport", "tbl_ReportDef"
, "[Plan]= """ & Me.cboPlans & """"), "")

Your parens were in the wrong place - that will cause the compile error
You had an opening bracket before PlanReport but no closing bracket - that
will cause a runtime error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What's the error number and description?


...DLookup("[PlanReport", "tbl.....

Looks like you forget the closing bracket on [PlanReport] but that shouldn't
cause a compile error, that would be a runtime error.

 

[Jecker]

this worked - THANK YOU SO MUCH

Me.txtreport.Value =
Nz(DLookup("PlanReport", "tbl_ReportDef"
, "[Plan]= """ & Me.cboPlans & """"), "")

Your parens were in the wrong place - that will cause the compile error
You had an opening bracket before PlanReport but no closing bracket - that
will cause a runtime error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

What's the error number and description?


...DLookup("[PlanReport", "tbl.....

Looks like you forget the closing bracket on [PlanReport] but that shouldn't
cause a compile error, that would be a runtime error.