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

[Ever Improving]

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:

Private Sub List114_Click()
Me![Upline ID] = Me!List114.Column(1)
Me![Upline First Name] = Me!List114.Column(2)
Me![Upline Last Name] = Me!List114.Column(3) 
End Sub

What I am trying to use now in Access 2007, since the previous code no longer works is:

Private Sub List114_Click()

Me.[Upline ID] = Me.List114.Column(1)
Me.[Upline First Name] = Me.List114.Column(2)
Me.[Upline Last Name] = Me.List114.Column(3)

 
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:

=[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?

[zmbd]

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

[NeoPa]

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.

[Ever Improving]

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

[zmbd]

Are the fields bound to a table?

[NeoPa]

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.

[zmbd]

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.

Option Compare Database
Option Explicit

Private Sub z_lstbx_people_Click()
Dim zselectedrow As Integer, z1stclm As Integer, z2ndclm As String, z3rdclm As String
On Error GoTo z_error_trap
'
'Fetch the details about the selected item in the list box
zselectedrow = Me.z_lstbx_people.ListIndex
z1stclm = Me.z_lstbx_people.Column(0, zselectedrow)
z2ndclm = Me.z_lstbx_people.Column(1, zselectedrow)
z3rdclm = Me.z_lstbx_people.Column(2, zselectedrow)
'
'Set the textbox control values to match the information selected in the list box
Me.z_txt_peoplepk = z1stclm
Me.z_people_firstname = z2ndclm
Me.z_txt_people_lastname = z3rdclm
'
'Make sure the form has updated
Me.Repaint
'
'Should be it.
'
'on error clean up
z_return_from_error:
'no cleanup
'
Exit Sub
z_error_trap:
MsgBox "Please report the following error to the DBA:" _
 & vbCrLf & Me.Name _
 & vbCrLf & Me.ActiveControl.Name _
 & vbCrLf & "Error Number: " & Err.Number _
 & vbCrLf & "Error Detail: " & Err.Description
Resume z_return_from_error
End Sub

Ah there it is:

=[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...

[Ever Improving]

zmbd I appreciate your contribution to this dialogue. To answer your earlier question: Yes, the fields are bound to a table.

[Ever Improving]

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.

[zmbd]

my solution should work for either bound or unbound.

[Ever Improving]

Thanks zmbd. Will let you know how it turns out.

[Ever Improving]

zmbd: Used the code you suggested, adjusted to fit my intended application, as follows:

Private Sub List114_Click()
Dim selectedrow As Integer, Upline ID As Integer, Upline First Name As String, Upline Last Name As String
On Error GoTo List114_error_trap
'
'Fetch the details about the selected item in the listbox
selectedrow = Me.List114.ListIndex
[Upline ID] = Me.List114.Column(0, selectedrow)
[Upline First Name] = Me.List114.Column(1, selectedrow)
[Upline First Name] = Me.List114.Column(2, selectedrow)
'
'Set the textbox control values to match the information selected in the listbox

Me.[Upline ID] = List114.Column(0)
Me.[Upline First Name] = List114.Column(1)
Me.[Upline Last Name] = List114.Column(2)
'
'Make sure the form has updated

Me.Repaint
'
'Should it be
'
'on error cleanup
List114_return_from_error
'
'no cleanup
'
End Sub

List114_error_trap:
MsgBox "Please report the following error to the DBA:" _
& vbCrLf & Me.Name_ & vbCrLf & Me.ActiveControl.Name _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Detail: " & Err.Description
Resume List114_return_from_error
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?

[zmbd]

(...) 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.

[Ever Improving]

zmbd I will take everthing you have said under advisement and get back to you. I really appreciate your detailed attention to this matter.