What is causing this OnClick event?

[Database Administrator]

In an earlier thread I recounted a problem I was having, but it took a
while to reduce the problem to it's basic components and the issue
became somewhat confused in reaching that point. I hope you will
excuse me for bringing it up again but this issue is very frustrating.

I created a new form in a brand new (Access 2003) database. This
example does nothing useful other than to illustrate the problem.
I've explained what I want to do in my real application at the bottom
of this post.

My problem is that, if I click on List2, it generates an OnClick event
in List1 which prevents me from selecting any item in List2.

The form has no record source; default view is 'Single Form'; record
selectors, scroll bars, naigation bars etc are off.

The form has only three controls:
- List1 is extended-multi-select with RowSource of "Line 1";"Line
2";"Line 3";"Line 4"
- List2 has no multi-select. The RowSourceType is Value List and the
list is empty.
- cmdButton is a command button.

Here is the entire code:

Option Compare Database
Option Explicit

Private Sub cmdButton_Click()
MsgBox ("OnClick - Button")
End Sub

Private Sub List2_Click()
MsgBox ("OnClick - List 2")
End Sub

Private Sub List1_Click()
Dim i As Integer
Dim strRowSource As String
Const QT = """"

MsgBox ("OnClick - List 1")
For i = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(i) Then
If Len(strRowSource) > 0 Then
strRowSource = strRowSource & ";"
End If
strRowSource = strRowSource & QT & Me.List1.Column(0, i) & QT
End If
Next
Me.List2.RowSource = strRowSource
Me.List2.Selected(0) = True '<############ this is the problem
End Sub

If I remove the second-last line the form works OK, but not quite the
way I want. But why would this line cause a click on List2 or the
cmdButton to generate an OnClick event on List1?

A possible clue? If Record Selectors are on, selecting this 'record'
allows List2 and cmdButton to work, until a new selection is made from
List1 at which point it locks up again.

For background information, in my real-life application List1 is a
list of types of events. List2 is a list of events of that type and I
have a subform which lists all the people attending the event selected
in List2. However, everytime I select an event (List2) it generates
an OnClick event on List1 which rebuilds List2 and automatically
selects the top item in the list. It is therefore impossible to
select an event from List2.

[Database Administrator]

I have been receiving a lot of help from Salad on this issue and I'm
delighted to say that he has come up with the answer!

When Salad looked at the problem using Access97 and could not
reproduce the problem; the form worked exactly as one would expect.
However a form created in Access 2003 exhibited the strange behaviour
I described. But the best bit is that, even when he imported this
Access 97 form into Access 2003 it still worked!

Armed with this knowledge, and a form created in Access 97 I imported
the form into my application and then copied the ListBox into my
application. It works brilliantly.

Thank you Salad!

[Database Administrator]

DavidGeorge wrote:
> I have been receiving a lot of help from Salad on this issue and I'm
> delighted to say that he has come up with the answer!
>
> When Salad looked at the problem using Access97 and could not
> reproduce the problem; the form worked exactly as one would expect.
> However a form created in Access 2003 exhibited the strange behaviour
> I described. But the best bit is that, even when he imported this
> Access 97 form into Access 2003 it still worked!
>
> Armed with this knowledge, and a form created in Access 97 I imported
> the form into my application and then copied the ListBox into my
> application. It works brilliantly.
>
> Thank you Salad!

You are welcome.

I have no idea what I did so that it would work on some forms vs not
work on others.

If anyone wants to see this in action...here's what you do.
Create a new form
Create a listbox, List0, and type in the values
one
two
three
four
Set the listbox's Multi-select to extended.

Drop another listbox (List2) onto the form. Hit cancel.
Set the rowsource to ValueList.

Drop this code into the form's module
Option Compare Database
Option Explicit
Const QT = """"

Private Sub List0_Click()
Dim var As Variant
Dim strRow As String

For Each var In Me.List0.ItemsSelected
strRow = strRow & QT & Me.List0.Column(0, var) & QT & ";"
Next
If strRow > "" Then strRow = Left(strRow, Len(strRow) - 1)
Me.List2.RowSource = strRow
If strRow > "" Then Me.List2.Selected(0) = True

End Sub

Now run the form. Select some items in List0. You will not be able to
set focus to List2. Somehow I got this to work tho but I have no idea
why. There appears to be no difference between the form that works and
the form that doesn't if I compare the property sheets.

Oh well.

[mryoung2u]

Hello Salad,

I have posted a couple of threads but I haven't gotten any response. I hope that you can help please!

Here is my situation - I just started a new job that is Access intensive. I wasn't really told how important Access would be when I interviewed but now it is very important. I am not smart at Access at all. I know enough to get around but on a scale of 1-10 I am about a 4.

I am having a problem with getting rid of multiple listings from a query.

History: I go to a national db to get company names, what they have ordered and the price they paid. Upon getting the info, I converted it to an Access document and created a query that has a link from the company name to the part number.

Once I run the query, it shows every single part. For example, a company buys 500 specialized screws, I have the one part number over and over and over.

My question to you is is there any way I can make the query capture only one of the specific part per company instead of every single part redundantly?

Your help would be a great help and if you want you can email me at mryoung2u@yahoo.com

Thanks!