Double click on list box item

[Deniseobx]

Hi,
I have a form with an unbound listbox (InquiriesList), the listbox is getting
its data from a query. I want the user to double click on an item in the list
and have the Inquiry Information to populate automatically the other controls on my form based on the specific record
from the listbox.
The criteria that it should be looking up is "InquiryID" (Primary Key) . The listbox has 3 columns the first is the InquiryID.
I have found information on double clicking an item from the list box and opening a second form, but I need this functionality within one form.
I can provide more detail information if needed

Any help would be greatly appreciated.

 

[boblarson]

In the double-click event you just use

Me.YourTextBoxName1 = Me.YourListBoxName(1) 
Me.YourTextBoxName2 = Me.YourListBoxName(2)

That would populate two text boxes with columns 2 and 3 of the list box (remember it is zero based)

 

[pbaldy]

If you use the combo or list box wizards and choose the 3 option, "Find a record...", it will build the relevant code for you.

 

[Deniseobx]

In the double-click event you just use

Me.YourTextBoxName1 = Me.YourListBoxName(1) 
Me.YourTextBoxName2 = Me.YourListBoxName(2)

That would populate two text boxes with columns 2 and 3 of the list box (remember it is zero based)

Thanks Boblarson, I was struggling thinking I had to code more than that.
However, I am still getting an error "Type Mismatch". I have taken into account that the list box column starts at 0. Do I need to set some properties in the listbox?

Here is how I have set it:
Name:ViewInquiries
RowSource: SELECT Inquiries.Terminal, Inquiries.TrainNo FROM Inquiries;
BoundColumn: 1
ColumnCount:2
...




An the code that I added to the Double click event of my list is:

Private Sub ViewInquiries_DblClick(Cancel As Integer)

Me.Terminal = Me.ViewInquiries(0)
Me.Train = Me.ViewInquiries(1)

End Sub


I don't know what I am doing wrong?

 

[Deniseobx]

Paul,

Thanks for your reply, I have also tried what you suggested but I get an error, "Data Cannot be retrieved from the source you have selected. You must select a different table or quey to continue with the wizard"
But first of all, it doesn give me the option to even choose a query or table.
My form is unbound, would this have anything to do with the message I get?

 

[pbaldy]

Yes, that method would only work for a bound form. With an unbound form, either use Bob's method, or if there are fields not in the listbox, open a recordset on the key value from the listbox, and get the values that way. I think Bob had a brain cramp, as it would be:

Me.YourTextBoxName1 = Me.YourListBoxName.Column(1)

 

[boblarson]

I think Bob had a brain cramp,

Brain fart - hmm, have been suffering from a lot of those lately

 

[Deniseobx]

Thanks the two of you Paul and Boblarson, It works beautifully.

 

[antidote]

Hello... sorry to dig up an old thread but I was wondering if you can use this method to display fields from a query where some fields are not "shown"

I have tried the above method, but when I put my index on 8, there is no data since the field is set to not shown in the query. Any ideas?

 

[georgedwilkinson]

Create a new control on the form and bind it to that/those column(s). If the control already exists, go into its properties and set it to visible.

 

[antidote]

Right, but if i set it to visible in the query, that field will show up in my list box, and I do not want that field in my list box.

 

[georgedwilkinson]

Sorry antidote, I guess I wasn't paying close enough attention. Since I wasn't involved in this, I clearly don't understand what is going on and am going to back out.

Someone who is familiar please take a look and help.

 

[John Big Booty]

How about setting the column you don't want to see as 0 width?

 

[boblarson]

To follow up with John Big Booty's comment - if you have 8 fields in your query but you only want to SEE 2 of them, let's say, for example, you want to see the 2nd and 3rd columns but not the 1st, nor the 4th through the 8th you would set the column width property in the control's properties dialog to:

Number of Columns - 8
Column Widths - 0";1";1";0";0";0";0";0";

(I used 1" for the visible but you can do whatever is needed - .5 or 2 or 3.4, etc.)