Dependent text boxes from drop down...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am designing a form where I have a drop down field that has several
columns. When I choose the selection from the drop down I'd like the the
information in the following two columns--that relate to that particular
selection--to appear elsewhere on the form in text boxes. How do I get a text
box to display a piece of data connected to a drop-down choice elsewhere on
the form?

Thanks,
YYY
 
Use a calculated textbox and set its Control Source to the desired column in
the combo box.

Example:
=cboMyCombo.Column(2)

The column index number is zero based, so 2 would be the third column.
 
Hi Wayne,

Thanks for your help. that solution worked however I have a related issue.

In some cases I want the drop down menu to fill text into the linked
textboxes. In other cases the I want the person filling out the form to
either fill in those same fields by typing in the data or change what's come
from the dropdown. the problem is now that when I try to adjust what's there
(even if the textbox is blank because I've placed nothing with that record in
the underlying table/query) Access will not allow me to type something in. Is
there any way to solve this?

thanks,
YYY
 
[Sorry if I've posted this incorrectly or in too many I'm still learning the
ropes in order to post correctly/effectively ;-[o) ]


Hi,

Thanks for your help. that solution worked however I now have a related
issue...

In some cases I want the drop down menu to fill text into the linked
textboxes. In other cases the I want the person filling out the form to
pick an option from the drop down then--rather than going with what the drop
down puts into the linked fields--either filling in those fields by typing in
the data or change what's there from the dropdown. the problem is now that
when I try to adjust what's there
(even if the textbox is blank because I've placed nothing with that record in
the underlying table/query) Access will not allow me to type something in
saying the field is bound to the dropdown list.

Is there any way to solve this?

many thanks,
YYY
 
YYY- Instead of setting the control source to the combo box column,
add a line of code in the AfterUpdate event of the combo box that sets
the other text box to equal the desired column's content. Then, if the
user needs to edit the value, he/she can.

ie:
me!txtBoxName = cboMyCombo.Column(2)

hth-

Betsy
 
You're correct, you can't edit a calculated textbox. Also, a calculated
textbox isn't bound to a field. If you want to bind and/or edit the textbox,
you would use the AfterUpdate event of the combo box to send the value to
the textbox.

Example:
Me.txtMyTextbox = Me.cboMyCombo.Column(2)
 
The article that I posted shows two ways to put a value into a textbox...
the second one shows you how to do it with a bound control, which is the
approach that you'll want to use here (even if the textbox is not bound).
The code method that walkbet posted is that solution.

--

Ken Snell
<MS ACCESS MVP>


Yeahyeahyeah said:
[Sorry if I've posted this incorrectly or in too many I'm still learning
the
ropes in order to post correctly/effectively ;-[o) ]


Hi,

Thanks for your help. that solution worked however I now have a related
issue...

In some cases I want the drop down menu to fill text into the linked
textboxes. In other cases the I want the person filling out the form to
pick an option from the drop down then--rather than going with what the
drop
down puts into the linked fields--either filling in those fields by typing
in
the data or change what's there from the dropdown. the problem is now that
when I try to adjust what's there
(even if the textbox is blank because I've placed nothing with that record
in
the underlying table/query) Access will not allow me to type something in
saying the field is bound to the dropdown list.

Is there any way to solve this?

many thanks,
YYY

Ken Snell (MVP) said:
See http://www.mvps.org/access/forms/frm0058.htm
Click to expand...
Click to expand...
 
Hi Wayne,

I tried that and now I'm running into error messages. I've tried both the
codes listed below (each suggested by different people) and when I try a
selection from the dropdown I get the debugger coming up and an error message
that says

"Compile Error
Invalid Outside Procedure"


Private Sub cboProgAddr_AfterUpdate()
Me.txtProgStreet.Value = Me.cboProgAddr.Column(1)
End Sub

Private Sub cboProgAddr_AfterUpdate()
Me.txtProgStreet = Me.cboProgAddr.Column(1)
End Sub

I'm totally stumped so any suggestions would be welcome.

Many thanks,
David
 
Hi Ken,

I tried that and now I'm running into error messages. I've tried both the
codes listed below (each suggested by different people) and when I try a
selection from the dropdown I get the debugger coming up and an error message
that says

"Compile Error
Invalid Outside Procedure"


Private Sub cboProgAddr_AfterUpdate()
Me.txtProgStreet.Value = Me.cboProgAddr.Column(1)
End Sub

Private Sub cboProgAddr_AfterUpdate()
Me.txtProgStreet = Me.cboProgAddr.Column(1)
End Sub

I'm totally stumped so any suggestions would be welcome.

Many thanks,
David
 
YYY-

It looks like you have a syntax error in your code. You always need to
proceed items that you have named with an exclamation mark, not a
period. (See my sample in my previous post...) Try this instead:

Private Sub cboProgAddr_AfterUpdate()
Me!txtProgStreet.Value = Me!cboProgAddr.Column(1)
End Sub

hth- Betsy
 
Hi Betsy,

Thanks for your help. I pasted the code as follows:

Private Sub cboProgAddr_AfterUpdate()
Me!txtProgStreet.Value = Me!cboProgAddr.Column(1)
End Sub


Unfortunately I get the same result. I'm wondering...is there anything
special I should be doint with the text box? Or how about the combo box?

Many Thanks,
David
 
Which line does the debugger highlight when it gives the error? In the code
editor, go to Debug|Compile and click Compile there. It should show you
which line is the problem. You should be able to use the "!" or "." to do
this. I tend to use the "." because it give you the IntelliSense to reduce
your typing.

What do you have in the Properties dialog for the combo box under "On After
Update" on the Events tab? Where is the procedure located? It should be in
the form's module. The Events tab should show [Event Procedure] for this
item, then click the ... button to its right (visible when the cursor is in
that box) to go to the code module. The first and last lines of the
procedure will be created for you automatically.
 
Hi Wayne,

I'm going to do my best to answer this, but it's getting really over my
head...

OK first things first:

1. When I run the process the debugger highlights the following line in
yellow with a yellow arrow to the left of it:

Private Sub cboProgAddr_AfterUpdate()

2. In the Properties Box for Combo Box under "After Update" it reads:
[Event Procedure]

3. In the Debugger there is a directory window on the upper left. In that
window there is a folder named "Microsoft Office Access Class Objects". In
that folder is a file named "Form_frmUIRInitialInput". That's where the
yellow highlighted code is.

I don't know if this matters or not but when the debugger initially comes up
the main window shows code from another form "Form_Form 1" and in the main
window is the following code:

Option Compare Database

End Sub

....and of the above, the words "End Sub" are highlighted in blue. Since this
form is not even open or being worked with, I have no idea if this is related
to the problem or not.

Many thanks for your help. I actually learning as we go along.

David
 
The debugger will go through all of the code in the database when you do a
compile, so it will find a compile error anywhere in any of the code. So,
which code window is showing doesn't matter.

For item #3 it indicates that the name of the form where the code is located
is "frmUIRInitialInput". Is that the name of the form that has the combo box
named "cboProgAddr"?

Can you zip up the database and send it to (e-mail address removed) so that I may
take a look at it?

--
Wayne Morgan
MS Access MVP


Yeahyeahyeah said:
Hi Wayne,

I'm going to do my best to answer this, but it's getting really over my
head...

OK first things first:

1. When I run the process the debugger highlights the following line in
yellow with a yellow arrow to the left of it:

Private Sub cboProgAddr_AfterUpdate()

2. In the Properties Box for Combo Box under "After Update" it reads:
[Event Procedure]

3. In the Debugger there is a directory window on the upper left. In that
window there is a folder named "Microsoft Office Access Class Objects". In
that folder is a file named "Form_frmUIRInitialInput". That's where the
yellow highlighted code is.

I don't know if this matters or not but when the debugger initially comes
up
the main window shows code from another form "Form_Form 1" and in the main
window is the following code:

Option Compare Database

End Sub

...and of the above, the words "End Sub" are highlighted in blue. Since
this
form is not even open or being worked with, I have no idea if this is
related
to the problem or not.

Many thanks for your help. I actually learning as we go along.

David

Wayne Morgan said:
Which line does the debugger highlight when it gives the error? In the
code
editor, go to Debug|Compile and click Compile there. It should show you
which line is the problem. You should be able to use the "!" or "." to
do
this. I tend to use the "." because it give you the IntelliSense to
reduce
your typing.

What do you have in the Properties dialog for the combo box under "On
After
Update" on the Events tab? Where is the procedure located? It should be
in
the form's module. The Events tab should show [Event Procedure] for this
item, then click the ... button to its right (visible when the cursor is
in
that box) to go to the code module. The first and last lines of the
procedure will be created for you automatically.
Click to expand...
Click to expand...
 
Hi Wayne,

I'd be happy to send you a zipped copy; unfortunately it may have to wait
until Tuesday since the file is located on the server at work. If I can pop
in tomorrow and d/l a copy to my laptop it may be sooner.

Many thanks,
David


Wayne Morgan said:
The debugger will go through all of the code in the database when you do a
compile, so it will find a compile error anywhere in any of the code. So,
which code window is showing doesn't matter.

For item #3 it indicates that the name of the form where the code is located
is "frmUIRInitialInput". Is that the name of the form that has the combo box
named "cboProgAddr"?

Can you zip up the database and send it to (e-mail address removed) so that I may
take a look at it?

--
Wayne Morgan
MS Access MVP


Yeahyeahyeah said:
Hi Wayne,

I'm going to do my best to answer this, but it's getting really over my
head...

OK first things first:

1. When I run the process the debugger highlights the following line in
yellow with a yellow arrow to the left of it:

Private Sub cboProgAddr_AfterUpdate()

2. In the Properties Box for Combo Box under "After Update" it reads:
[Event Procedure]

3. In the Debugger there is a directory window on the upper left. In that
window there is a folder named "Microsoft Office Access Class Objects". In
that folder is a file named "Form_frmUIRInitialInput". That's where the
yellow highlighted code is.

I don't know if this matters or not but when the debugger initially comes
up
the main window shows code from another form "Form_Form 1" and in the main
window is the following code:

Option Compare Database

End Sub

...and of the above, the words "End Sub" are highlighted in blue. Since
this
form is not even open or being worked with, I have no idea if this is
related
to the problem or not.

Many thanks for your help. I actually learning as we go along.

David

Wayne Morgan said:
Which line does the debugger highlight when it gives the error? In the
code
editor, go to Debug|Compile and click Compile there. It should show you
which line is the problem. You should be able to use the "!" or "." to
do
this. I tend to use the "." because it give you the IntelliSense to
reduce
your typing.

What do you have in the Properties dialog for the combo box under "On
After
Update" on the Events tab? Where is the procedure located? It should be
in
the form's module. The Events tab should show [Event Procedure] for this
item, then click the ... button to its right (visible when the cursor is
in
that box) to go to the code module. The first and last lines of the
procedure will be created for you automatically.
Click to expand...
Click to expand...
Click to expand...
 
Hi Wayne,

Thanks for your help. I figured out the last issue; now of course I have
another one.

I've got the dropdown configured so it fills those other fields from the
"after update" event, however when I complete one record and click to enter a
fresh one the info that was entered as a result of that drop down stays in
those same fields in the next "blank" record. How do I get those field to go
back to blank so the next record will be fresh info?

Thanks,
David
 
Since the combo box is unbound, it doesn't follow the record changes as you
move from one record to the next. In the form's Current event, set the combo
box to Null.

Me.cboMyCombo = Null
 
Back
Top