[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

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
0
HudsonMarine
Asked:
HudsonMarine
  • 16
  • 13
1 Solution
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 16
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now