VBA to detect which listbox item has been selected

[skidude2000]

Hi all,

I am new to this forum, and I think I'm posting in the right place, but I'm not sure. If I'm not, apologies.

Anyway, I am coding a database that deals with software and computer inventory. My current issue stems from a form designed to take info of newly imaged computers. Here's what I have:

Form: frmNewComp
Fields in question:
Listbox: lstGroup
Fields: txtGroup
txtCreate
txtDelete
txtGroupNotes

Within the lstGroup listbox, I have columns GroupAcctID, GroupName, AcctCreate, AcctDelte, and Comments.

So here's the deal: What I want this section of the form to do is record what group accounts are being put onto the computer as it is imaged. The source of the group accounts is another table called tblGroupAcct; this is the table that gives the listbox its information.

The listbox is a multiselect, since it may be the case that more than one group account is put onto a single pc. Now, I am farily certain I have the code for saving the selection. What I want is this: as a group from lstGroup is selected, its information is displayed below the listbox in txtGroup, txtCreate, txtDelete, and txtGroupNotes. For the onClick event of the listbox, I want VBA to detect the list row last selected, get that row's information, and distribute it to the textboxes. Seems easy enough, but I have no idea how to do this :banghead:.

Please help!

 

[pbaldy]

See if this works for you:

http://www.baldyweb.com/Autofill.htm

 

[skidude2000]

Thank you much, sir. I tried the suggestion, and it seems to be working ok. I will continue to fiddle with it, but looks good now.

Cheers.

 

[skidude2000]

I actually have one question. After further review, I have found that as I select items from the listbox, the data populates the group fields nicely. But if I deselect all of my previous selections so that there are no rows selected in the listbox, the group fields retain the information of the row I clicked on last. I would like the group fields to return to being blank if all rows are deselected. I tried placing the following code in the onClick event of the textbox:

Dim varItem As Variant
Dim nonselect As Integer
With Me.lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
nonselect = 1
End If
Next
End With

If nonselect = 0 Then
Me.txtGroup = null
Me.txtCreate = null
Me.txtDelete = null
Me.txtGroupNotes = null
End If

However, this returns the following error:

Run-time error '-2147352567 (80020009)':
You can't assign a value to this object

It is referring to me trying to assign null to the textboxes that now have the a column in the listbox as their sources.

Any advice?

 

[bob fitz]

Until Paul is back online, You could try using a zero lenght string "" instead of Null.

 

[skidude2000]

Hi Bob,

I had also tried that, but I received the same error message.

 

[pbaldy]

Yeah, that wouldn't work either Bob. I do this type of thing to react to whether the listbox item was selected or unselected:

  If (ctl.Selected(ctl.ListIndex)) Then
    'item was selected
  Else
    'item was deselected
  End If

You could use the other method in the link and place the value in the textbox or clear the textbox as appropriate.

 

[bob fitz]

Hi Paul
Glad you're back with the solution. Just thought I'd toss in a few ideas while you were offline.

 

[pbaldy]

"the solution" is a stretch, so toss in more ideas if you have them!

 

[delikedi]

I'm no expert, but

It is referring to me trying to assign null to the textboxes that now have the a column in the listbox as their sources.

wouldn't you continue to receive this error even if you got the main logic right... I mean your textboxes are calculated controls now, you can't assign values to calculated controls. In order for you not to receive this error, the textboxes should be unbound and the code to populate them should reside in the listbox'es class module (just like the case where you are trying to assign null values to them).

 

[pbaldy]

Yes, which is why I suggested using the other method in the link.

 

[skidude2000]

Yeah, that wouldn't work either Bob. I do this type of thing to react to whether the listbox item was selected or unselected:

  If (ctl.Selected(ctl.ListIndex)) Then
    'item was selected
  Else
    'item was deselected
  End If

You could use the other method in the link and place the value in the textbox or clear the textbox as appropriate.

Hi Paul,

Apologies for not responding on Friday; I had left work for the day. Anyway, I am not sure where to put this code or how to incorporate the method from the other link. Could you explain a bit more?

Thanks.

 

[pbaldy]

I was thinking along the lines of:

If (Me.lstGroup.Selected(Me.lstGroup.ListIndex)) Then
    Me.txtGroup = Me.lstGroup.Column(1)
  Else
    Me.txtGroup = Null
  End If

 

[skidude2000]

Unless I am implementing this code improperly, it does not seem to work. I tried placing it in the onClick event of the listbox. Is that the correct place? Sorry, I'm not that great with VBA; code is far from my forte.