Make a text field refresh after autopopulate from combobox selection!

I have a dropdown Combo box which when a selection is made it populates 3 other text boxes but i cant figure out how to refresh the 3 text boxes after this fuction happens - can someone please help?

Heres how its setup and the code:

I created a table called OSRO with the fields I wish to auto populate when the dropdown item is selected - they are:

OSRO <------dropdown selection of field
OSRO Tel
OSRO Fax
OSRO Email

Here is what is in the after_update code of the dropdown combo box:

Private Sub Combo102_AfterUpdate()
[OSRO Tel].Value = Me.Combo102.Column(1)
[OSRO Fax].Value = Me.Combo102.Column(2)
[OSRO Email].Value = Me.Combo102.Column(3)

End Sub


Reference the pics below for the rest of the combo info:

osro.JPG
combo.JPG
combo2.JPG
LVL 1
HudsonMarineAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wiswalldCommented:
Not sure I understand. Try to requery the textbox. What you have looks like you should be able to populate the textbox with the data from the combobox column. Is the problem when you change the selection?
0
HudsonMarineAuthor Commented:
yes exactly - when you make one selection and the drop down autopopulates the 3 fields then if you make a different selection from the dropdown it then causes the data in 1 of the 3 text fields to go invisible until you tab or click in the field and then leave the field - then the correct value is entered into that field so I tried adding Me.Requery to the afterupdate of the text field but it doesnt help the issue!
0
wiswalldCommented:
Private Sub Combo102_AfterUpdate()
me.[OSRO Tel].requery
me.[OSRO Fax].requery
me.[OSRO Email].requery
[OSRO Tel].Value = Me.Combo102.Column(1)
[OSRO Fax].Value = Me.Combo102.Column(2)
[OSRO Email].Value = Me.Combo102.Column(3)
End Sub
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HudsonMarineAuthor Commented:
That gave me a "compile error" and the VB editor opened and highlighted the first line

Me.[OSRO Tel].Requery

Any other Suggestions? Thanks for your help by the way!
0
wiswalldCommented:
What about

me.[OSRO Tel] = ""
0
wiswalldCommented:
Or use the mouse down event of the combobox to

me.[OSRO Tel] = ""


This will clear the textboxes before you select a value
0
HudsonMarineAuthor Commented:
That made the compile error go away but the same bug still exists!
0
wiswalldCommented:
Which one or both
0
wiswalldCommented:
Also for reference never use spaces in names of fields, forms, tables, etc.

This may be your problem.
0
HudsonMarineAuthor Commented:
ok adding the mouse down event of the combobox to

me.[OSRO Tel] = ""


This will clear the textboxes before you select a value


This worked and solved the problem!!!!!!!!!!

But now i need to tie up a few loose ends:

in the dropdown menu I can see the entire table including all rows and fields - how do I get it to only display the name that needs to be displayed in the dropdown menu?
0
wiswalldCommented:
Set the column count to 1.
0
HudsonMarineAuthor Commented:
ok well that does correct that problem and is now only displays the correct field in the dropdown combo box menu

BUT

At the same time it causes only 1 of the 3 text fields to populate now the other 2 are left blank!

How do I fix that?
0
wiswalldCommented:
OK

Set the column count to however many you have and the size to 1,0,0

This will hide the others in the drop down.
0
HudsonMarineAuthor Commented:
When i put the column count back to 4 it works correctly and when a selection is made from the dropdown combo box all 3 text fields are populated correctly but while it is in this state I :

1) still see the other columns when clicking on the dropdown menu and it displays the entire tables contents!

2) have an issue where after making a selection from the dropdown combo box and the 3 text fields populate correctly this leaves the value in the combobox high lighted and if you click back on it then the data in 2 of the 3 text boxes goes blank for some reason!

is this because fo the mousedown event you told me to put in place?
0
wiswalldCommented:
is this because fo the mousedown event you told me to put in place?

Yes

and you have to set the column widths to only see the first column.
Set the others to 0
0
HudsonMarineAuthor Commented:
everytime I enter 1,0,0,0 into the column count it turns it into 100 and i still see the entire table in the dropdown menu.
0
wiswalldCommented:
1";0";0";0"
0
HudsonMarineAuthor Commented:
And is there a way to refresh the text boxes without making the data in them disapear after a slection is made and you reclick on the high lighted dropdown value?
0
wiswalldCommented:
Why would you reclick the drop down?
0
HudsonMarineAuthor Commented:
if a user makes a mistake and needs to rechoose they would reclick the dropdown.

And I also tried what you recommended for the colum count
1";0";0";0"
even tried it like this
1",0",0",0"
and Access 2003 is still making it 100 when I close and open design view.

Any other suggestions i could try - thanks again for your help.
0
wiswalldCommented:
If they are going to choose a different item in the drop down you would want the textboxes cleared (I would think).

In the picture above combo2.jpg can you reload that so I can look at what you have.
0
HudsonMarineAuthor Commented:
ok here it is again
combo3.JPG
0
wiswalldCommented:
Are you putting 1,0,0 under column widths?
0
wiswalldCommented:
Access should automatically recognize and change to 1",0",0",0"

Try tabbing to a different field under properties.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HudsonMarineAuthor Commented:
ok that worked and yes i finally put it under colum width.

Your point is well taken that the user will probly want to clear the text fields if they want to make another selection.

But is there another way to fix this issue so if the user clicks on the dropdown a second time it does not clear the other fields - i really wanna make it bug free and user proof so i dont get a million of the same question - Thanks so much for your help!~
0
wiswalldCommented:
You could try an if statement like

In the mouse down event

If Me.Combo10 = "" Then
me.[OSRO Tel] = ""
Else
'maybe set focus to the next field here
End If




0
HudsonMarineAuthor Commented:
OK but I am  newb and I have no idea how to write that for these attributes:

Me.[OSRO Tel] = ""
Me.[OSRO Fax] = ""
Me.[OSRO Email] = ""
0
wiswalldCommented:
If Me.Combo10 = "" Then
me.[OSRO Tel] = ""
Me.[OSRO Fax] = ""
Me.[OSRO Email] = ""
Else
'maybe set focus to the next field here
End If

I am figuring that it will check if the combobox is blank. If so will clear the other fields. If not go to another field. But they could never make a change. Thats not good. I would stick with the original code. With any database type program a user can always make a change. You could be here for days trying to solve that. I would maybe think of locking all the fields then have a button that when you click adds a new record and a save button that relocks all the fields. Then an edit button to make changes.
0
HudsonMarineAuthor Commented:
Thanks for your help Wiswalld
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.