473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Auto-filling fields on a Form using a Selection from a Listbox

I have tried these and multiple combination of codes (from multiple websites) and non of them work for this seemingly simple task. I am using Access 2007. I have 3 fields I would like to auto-populate from a selection in a list box. The list box is controlled from a query. All the fields are included in the query grid. The query is listed as the control source

The following code worked for me in Access 2003:

Expand|Select|Wrap|Line Numbers
  1. Private Sub List114_Click()
  2. Me![Upline ID] = Me!List114.Column(1)
  3. Me![Upline First Name] = Me!List114.Column(2)
  4. Me![Upline Last Name] = Me!List114.Column(3) 
  5. End Sub
What I am trying to use now in Access 2007, since the previous code no longer works is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub List114_Click()
  2.  
  3. Me.[Upline ID] = Me.List114.Column(1)
  4. Me.[Upline First Name] = Me.List114.Column(2)
  5. Me.[Upline Last Name] = Me.List114.Column(3)
  6.  
  7.  
  8. End Sub
However, when I click the selection; I just get a little bell that rings and no action.

I even tried the suggestion to use this code:

Expand|Select|Wrap|Line Numbers
  1. =[List114].[Column](3) 
in the Control Source of the receiving Fields

However, this doesn't work either.

After days trying to find a solution; I am totally frustrated. What am I missing here?
Nov 4 '12 #1
13 5231
zmbd
5,501 Expert Mod 4TB
"It doesn't work" is really non-descipt...

You can try Placing a me.repaint after all of your updates and see if it's a simple screen update issue. Otherwise please post more detail.
Nov 4 '12 #2
NeoPa
32,556 Expert Mod 16PB
It seems like you're really asking about how to reference items within a ListBox control in Access 2007. Unfortunately your question doesn't make this at all clear.

I've checked your code, and the syntax and usage of the Column() property all appear to be fine. This leaves us needing more information of course. Are the controls used exactly as you have them spelled? Probably, as it's an upgrade from an existing database. Nevertheless needs to be confirmed. Did you get any error message at all when it failed (If so what)? Have you tried tracing the individual lines of code (See Debugging in VBA)? If not then please give that a try and make note of the values returned by the references to the columns.
Nov 4 '12 #3
First of all; I appreciate both your responses.

zmbd: It was suggested that I could use: =[List114].[Column](3) in one of the receiving field's, control source property. The prhase: "Doesn't work", means the fields don't auto-populate when the selection is made from the combo / listbox. I have tried both possibilities.

NeoPa: I am taking your instructions under advisement. However, I am new to coding and even newer to debugging. I don't get any error message, only that little bell, like you get when you've clicked your mouse in the wrong place or hit the wrong key on the keyboard. I am checking out the link you provided, as well.

Thanks again
Nov 6 '12 #4
zmbd
5,501 Expert Mod 4TB
Are the fields bound to a table?
Nov 6 '12 #5
NeoPa
32,556 Expert Mod 16PB
That seems like a sensible reply. I'll await further info from you, but it sounds like you're approaching things the right way - which is very promising.
Nov 6 '12 #6
zmbd
5,501 Expert Mod 4TB
Basic code...
I had to clean up the refernces as it refered to some company specfic stuff.
I have another form that refernces the Listbox selection as the control source; however, when I changed the values I broke the form... :( So once I get those fixed I may post the example database too.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub z_lstbx_people_Click()
  5. Dim zselectedrow As Integer, z1stclm As Integer, z2ndclm As String, z3rdclm As String
  6. On Error GoTo z_error_trap
  7. '
  8. 'Fetch the details about the selected item in the list box
  9. zselectedrow = Me.z_lstbx_people.ListIndex
  10. z1stclm = Me.z_lstbx_people.Column(0, zselectedrow)
  11. z2ndclm = Me.z_lstbx_people.Column(1, zselectedrow)
  12. z3rdclm = Me.z_lstbx_people.Column(2, zselectedrow)
  13. '
  14. 'Set the textbox control values to match the information selected in the list box
  15. Me.z_txt_peoplepk = z1stclm
  16. Me.z_people_firstname = z2ndclm
  17. Me.z_txt_people_lastname = z3rdclm
  18. '
  19. 'Make sure the form has updated
  20. Me.Repaint
  21. '
  22. 'Should be it.
  23. '
  24. 'on error clean up
  25. z_return_from_error:
  26. 'no cleanup
  27. '
  28. Exit Sub
  29. z_error_trap:
  30. MsgBox "Please report the following error to the DBA:" _
  31.  & vbCrLf & Me.Name _
  32.  & vbCrLf & Me.ActiveControl.Name _
  33.  & vbCrLf & "Error Number: " & Err.Number _
  34.  & vbCrLf & "Error Detail: " & Err.Description
  35. Resume z_return_from_error
  36. End Sub
Ah there it is:
Expand|Select|Wrap|Line Numbers
  1. =[Forms]![frm_simple_list_to_textbox]![z_lstbx_people].[column](([Forms]![frm_simple_list_to_textbox]![z_lstbx_people].[ListIndex]),1)
returns the value of the second column of the selected item within the listbox control...
Attached Files
File Type: zip bytesthread_944172.zip (81.3 KB, 148 views)
Nov 6 '12 #7
zmbd I appreciate your contribution to this dialogue. To answer your earlier question: Yes, the fields are bound to a table.
Nov 9 '12 #8
NeoPa Although, I still have not found a solution to the problem at hand I have begun to review your information related to debugging. Again, I appreciate your attention to this matter. It is good to know that you feel I am heading in the right direction.
Nov 9 '12 #9
zmbd
5,501 Expert Mod 4TB
my solution should work for either bound or unbound.
Nov 9 '12 #10
Thanks zmbd. Will let you know how it turns out.
Nov 11 '12 #11
zmbd: Used the code you suggested, adjusted to fit my intended application, as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub List114_Click()
  2. Dim selectedrow As Integer, Upline ID As Integer, Upline First Name As String, Upline Last Name As String
  3. On Error GoTo List114_error_trap
  4. '
  5. 'Fetch the details about the selected item in the listbox
  6. selectedrow = Me.List114.ListIndex
  7. [Upline ID] = Me.List114.Column(0, selectedrow)
  8. [Upline First Name] = Me.List114.Column(1, selectedrow)
  9. [Upline First Name] = Me.List114.Column(2, selectedrow)
  10. '
  11. 'Set the textbox control values to match the information selected in the listbox
  12.  
  13. Me.[Upline ID] = List114.Column(0)
  14. Me.[Upline First Name] = List114.Column(1)
  15. Me.[Upline Last Name] = List114.Column(2)
  16. '
  17. 'Make sure the form has updated
  18.  
  19. Me.Repaint
  20. '
  21. 'Should it be
  22. '
  23. 'on error cleanup
  24. List114_return_from_error
  25. '
  26. 'no cleanup
  27. '
  28. End Sub
  29.  
  30. List114_error_trap:
  31. MsgBox "Please report the following error to the DBA:" _
  32. & vbCrLf & Me.Name_ & vbCrLf & Me.ActiveControl.Name _
  33. & vbCrLf & "Error Number: " & Err.Number _
  34. & vbCrLf & "Error Detail: " & Err.Description
  35. Resume List114_return_from_error
  36. End Sub
No error messages!

However, I still just get that little bell sound upon clicking the selection in the listbox.

Could there be something in one of the properties in the list box field that is keeping the code from executing?
Nov 11 '12 #12
zmbd
5,501 Expert Mod 4TB
(...) However, when I click the selection; I just get a little bell that rings and no action.(...)
(...)However, I still just get that little bell sound upon clicking the selection in the listbox (...)

Now how did I miss that in the first post...

There shouldn't be anything propertiy wise: if "enabled" is set to false, then the control would be dimmed, if the "locked" is set to true, then you wouldn't be able to select anything.

Place a STOP just after your error trap, compile and save. Close and re-open the form. Click in the list and then [F8] thru the code until you hit the error. If you're changeing values in bound fields then it sounds like your record set is locked or the record isn't set a current (?) However, you should be getting some sort of error message.

You might have to re-create the form. Not the first time during a development that I've had a form "break." I've simply rebuilt the form, copy and pasted the code into the events, and it worked.

Another thing... please make your life easier...
Find a list of reserved VBA and SQL words... do not ever use them as a name for anything in either application.
Second, get rid of anything other than AlphaNumerics and the underscore in your names... file names, field names, control names... everything! 1) Makes writing code easier, 2) less chance of running afoul of the engine parsers.
Nov 11 '12 #13
zmbd I will take everthing you have said under advisement and get back to you. I really appreciate your detailed attention to this matter.
Nov 12 '12 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Alan Mackenzie | last post by:
To all those who use (X)Emacs's CC Mode to edit C, C++, Java, Objective-C, Pike, AWK or IDL: To help direct the development of CC Mode, it would be useful to find out how people use the...
20
by: Vijay Kumar R. Zanvar | last post by:
Hello, Unlike register, auto keyword can not be used to declare formal parameter(s). Is there any specific reason for this? Kind regards, Vijay Kumar R. Zanvar
7
by: Brian | last post by:
hello, I am looking for a way to auto refresh a web page that I created, but also let the user choose to stop the auto refresh. I can not figure out how to stop the auto refresh. Any help would...
5
by: Phil Latio | last post by:
I have 2 virtually identical tables and wish to move data between them. Basically one table is called "live_table" and the other is named "suspended_table" and the only difference is that the...
5
by: maya | last post by:
at work they decided to center divs thus: body {text-align:center} #content {width: 612px; text-align:left; margin: 0 auto 0 auto; } this works fine in IE & FF, EXCEPT in FF it doesn't work if...
9
by: Beowulf | last post by:
I was having this problem: http://groups.google.com/group/microsoft.public.sqlserver.server/msg/e36e423972323378?dmode=source with it taking an inordinate amount of time to enumerate the...
2
by: Piotr K | last post by:
Hi, I've encountered a strange problem with Firefox which I don't have any idea how to resolve. To the point: I've <divelement with a style "height: auto" and I want to retrieve this value...
1
by: neridaj | last post by:
Hello, I've found a few postings of this problem but none of the answers seem to fix my problem. I have a content div wrapped around a left floated image and a right floated table. I want the...
1
by: pravinnweb | last post by:
can anyone tell me how to set auto height to outer div that is in green box id "gray-background" it should increase relatively to inner div "smbox" here is the css and html code it should work in...
21
by: JOYCE | last post by:
Look the subject,that's my problem! I hope someone can help me, thanks
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.