Pass value from Listbox 2

[THWatson]

Using Access 2003 (2000 format)

A bound form called frmTrinity. On the form is a command button which, when clicked, opens an unbound form on which there is a listbox.

Behind a double click event for the listbox lies the following code

Private Sub lstRetirementHomes_DblClick(Cancel As Integer)
If CurrentProject.AllForms("frmTrinity").IsLoaded Then
Forms!frmTrinity!HouseNbr = Me.lstRetirementHomes.Column(3)
Forms!frmTrinity!Street = Me.lstRetirementHomes.Column(4)
Forms!frmTrinity!City = Me.lstRetirementHomes.Column(5)
Forms!frmTrinity!Code = Me.lstRetirementHomes.Column(6)
End If
End Sub

When I double click the listbox I want to be able to pull those 4 values and pass them to the relevant text box fields on frmTrinity.

What happens when I pull up the listbox form, and double click a row the 4 fields in frmTrinity go blank, but are not populated with the values from the listbox.

I have tried putting the listbox column values in variables but this doesn't work either.

Suggestions?

Thanks.

Tom

 

[THWatson]

I can solve the problem by putting the Listbox on frmTrinity and hiding it unless it is needed.

However, I would still like to do it the way I outlined in the post.

Tom

 

[TheAceMan1]

Howdy THWatson . . .

On view of your code I see no reason why it shouldn't work. Digging deeper raises a few questions though:
[ol][li]You are aware that column [blue]index starts[/blue] at [blue]0[/blue]? Meaning your indexes should be 2, 3, 4, 5.[/li]
[li]What does it say in the [blue]Name[/blue] property of lstRetirementHome[red]s[/red]? Is it possible you meant [blue]lstRetirementHome[/blue] instead? Doublecheck the spelling of all names.[/li][/ol]
Thats all I can think of at the moment, unless you have have corruption, indicated by:

[blue] ... the 4 fields in frmTrinity go blank.[/blue]

I would expect an error to be raised instead!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I pretty sure the problem is the double click event. I think this is an event on the container and therefore the dbl click can fire before the value is updated, therefore the value is actually null.

Test

msgbox me.listRetirmentHomes.value

see if this gives you a null error.

 

[MajP]

To add a little to this I tried

Private Sub List0_AfterUpdate()
  Debug.Print "afterUpdate"
End Sub

Private Sub List0_BeforeUpdate(Cancel As Integer)
 Debug.Print "before update"
End Sub

Private Sub List0_DblClick(Cancel As Integer)
  Debug.Print "dbl click"
  MsgBox Me.List0
End Sub

it prints
before update
after update
dbl click

But if I remove the before and after update event procedure, I get a null error.

So here is the solution, you need a do events

Private Sub List0_DblClick(Cancel As Integer)
Do Events
Debug.Print "dbl click"
MsgBox Me.List0
End Sub

 

[Mayhem9]

Thanks MajP,

It is past 1am here and I am now continually failing when re-doing some combo boxes that appear to be from when the look-up tables had a autonumber PK. When I click save, I get a Access has stopped working error! I have tried 3 times and same result. I opened the code first and fixed a couple of errors and compile didn't show any errors once finished.

I have uploaded the db with the Tool_Log form that is behaving as described above and the other renamed.

http://www.4shared.com/file/212341182/4d9bab39/Tool_Log.html

Please let me know if you would prefer that I build the form from scratch before posting (I just didn't have time to do it), rather than try to reconstruct from a previous version.

Cheers,
Darren

 

[MajP]

Mayhem,
Looks like you posted in the wrong thread. I will reply in your "Out of Memory" thread.

 

[TheAceMan1]

Howdy MajP . . .

I tried the testing you perscribed (when A2K) 1st came out. The results (logged in my library) show no problem unless the user clicks on a blank area (usually at the bottom when a listbox only has a few values) an returns [blue]Null[/blue].

As a prgrammer I would normally use [blue]AfterUpdate[/blue], but I have several clients who are constantly hiring and firing data entry personnel. Its these clients where I've used the [blue]DoubleClick[/blue] event instead of [blue]AfterUpate[/blue]. This is because newly hired personnel are so unsure of themselves when dealing with combo/list boxes. I've used the [blue]DoubleClick[/blue] event simlpy because [blue]it shows intent of selection[/blue].

I've perform the testing as you've perscribed, particularly with only the [blue]DoubleClick[/blue] event, and I get no problem. Data is returned proper!

What scares me is that this could be [blue]version specific[/blue] ... my platform is A2K.

Your platform is?
[blue]THWatson[/blue] yours is?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

No I believe the case has to be specific like this.
1)List box is unbounded
2)No initial value in listbox
3)Double click on an item

if you first select an item, then double click there will not be an error. Or if it is bound there will not be an error. It is when you come from a null state immediately to a double click event.

This is repeatable in 2003 and 2007.

 

[MajP]

Actually, now I am less sure. But I would recommend.

If IsNull(Me.lstName) Then
MsgBox "No value selected. Reselect value then double click"
Else
your code here
end if

 

[TheAceMan1]

MajP . . .

That is exactly what my library reveals:
[ol][li]DoubleClick a blank area with no initial selection returns [blue]Null[/blue].[/li]
[li]DoubleClick a value with no initial selection returns the value.[/li][/ol]
I've researched my clients who have db's with listboxes triggered by [blue]DoubleClick[/blue] and come up with 6. To this day I've never received a complaint about this [blue]DoubleClick[/blue] issue ... which makes it all kind of moot to me. However I've added notes to alert me of this.




See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[THWatson]

MajP and TheAceman1
Sorry to be so long at getting back to this post.

First of all, to answer the questions:
1. I am using Access 2003 (file saved in 2000 format)
2. The name of the list box I showed is correct, lstRetirementHomes
3. I am aware that the indexing starts at 0. The columns I showed (3,4,5,6) are correct. There are 7 columns in the list box. I am using the 4th, 5th, 6th and 7th.
4. The form that is called, frmRetirementHomeList, is unbound.

I see now why no value is returned upon double click. I can't click on a row to highlight it. So double clicking logically returns no value. Actually, no row is returned upon single clicking either.

That doesn't explain why the relevant field values in frmTrinity go blank when I double click an row in lstRetirementHomes.

When I add MajP's suggestion to the code, i.e.

 If IsNull(Me.lstRetirementHomes) Then
    MsgBox "No value selected. Reselect value then double click"
  Else

I get the message box. My double clicking is ignored. Interestingly enough, no fields go blank in frmTrinity.

Seems kinda weird to me.

Is there something that makes a list box "unclickable?"

Tom

 

[THWatson]

Now I'm more confused.

I had earlier been able to double click a row, and it was highlighted upon double click. And then that quit working, as I explained in my last reply.

However, I decided to make a new form, pull the list box over onto it, and try it.

Now, not only does the row get highlighted upon double clicking...but also now my original code works.

Go figger.

Tom