Click ListBox executes the wrong code.

[OldBirdman]

I have 2 ListBoxes, ColumnCount=1 with lbxAlpha1.RowSo urce = "A";"C";"E" ; . . . and lbxAlpha2.RowSo urce = "B";"D";"F" ; . . . All works fine as long as I only use one of the ListBoxes. When I have used one, and click the other, I get run-time error '94', and the value for the ListBox is Null.

The error occurs at the Call NewLetter() for the WRONG ListBox (See Code Below).

I have skipped the Call statement and with 'Step Into' determined that the form/click called the _click procedure, not elsewhere in the code.

If I remove the clearing of the other box by removing the lbxAlpha1 & 2 statements, then both ListBoxes have Null values at the point of error, even though the correct letter is highlighted on the form in each ListBox.

Find for entire project for 'lbxAlpha' does not find any other occurrances except initialization in Form_Load.

Any suggestions?

Windows XP & Access 2002

Private Sub lbxAlpha1_Click ()
lbxAlpha2 = ""
Call NewLetter(lbxAl pha1)
End Sub 'lbxAlpha1_Clic k
Private Sub lbxAlpha2_Click ()
lbxAlpha1 = ""
Call NewLetter(lbxAl pha2)
End Sub 'lbxAlpha2_Clic k

From the form, I click lbxAlpha1=A, then C, then E, then F and error occurs @Call NewLetter(lbxAl pha1) in Sub lbxAlpha1_Click (), but I should be in Sub lbxAlpha2_Click ()

[MMcCarthy]

Is the control source of the listboxes bound to anything?

Mary

[OldBirdman]

Is the control source of the listboxes bound to anything?

Mary

No, both are Unbound, Enabled=True, Visible=True, Locked=False, RowSourceType=V alue List

[MMcCarthy]

 
Private Sub lbxAlpha1_Click()
    lbxAlpha2 = ""
    Call NewLetter(lbxAlpha1)
End Sub 'lbxAlpha1_Click
Private Sub lbxAlpha2_Click()
    lbxAlpha1 = ""
    Call NewLetter(lbxAlpha2)
End Sub 'lbxAlpha2_Click
 

Can you explain in English exactly what this event code is trying to do and post the code for the Newletter() function.

From the form, I click lbxAlpha1=A, then C, then E, then F and error occurs @Call NewLetter(lbxAl pha1) in Sub lbxAlpha1_Click (), but I should be in Sub lbxAlpha2_Click ()

I don't really understand what you mean here but answering my first question should clarify the situation.

Mary

[OldBirdman]

Explanation in Plain English (probably too long):

A common problem is to display a record from a table with a form with DefaultView = 'Single Form' MS Access supplies Navigation Buttons (with a Record Number Box), but this is not a useful tool. I am trying to write a generic program to use as a starting point whenever this same problem occurs. As an older person, I want larger buttons and text, making it faster to use the mouse accurately, especially a touchpad on a laptop. I dislike switching from mouse to keyboard for a single task, such as find & display a record, and dislike the scrollbar with long lists.

lbxAlpha is a ListBox on the left edge of the form, containing in Value List * 9 A B C . . . Y Z (*=All & 9=starts with a number).

lbxSelect is a ListBox immediately to the right containing a list to choose from, whether an 'Address Book', a list of DVDs rented so I don't pay for something I've already seen, a list of the bird species of the world (my hobby), or any other such list, such as Employees, Products, Suppliers, Countries or States, etc.

The remainder of the form contains controls bound to a table.

Using movies rented as an example, with a table named tMovies and a form named fMain. User makes a choice in lbxAlpha, say the letter 'K' The procedure 'NewLetter' generates an SQL statement for lbxSelect. The WHERE clause would be 'WHERE (tMovies.Title LIKE "K*") ' Then lbxSelect.RowSo urce=SQL. By default, form displays the first record in the list. The essense of NewLetter is:

Sub NewLetter (strLetter as String)
Forms!fMain.lbx Select.RowSourc e = _
"SELECT tMovies.Key, tMovies.Title " & _
"FROM tMovies " & _
"WHERE (tMovies.Title LIKE """ & strLetter & "*"") " & _
"ORDER BY tMovies.Title;"
Forms!fMain.Fil ter = "Key=" & Forms!fMain.lbx Select.Column(0 ,0)
End Sub 'NewLetter

Sub NewLetter is more complex to deal with letters chosen resulting in lbxSelect being empty (ListCount=0), a "Filter Dialog" (Year>2004; Director=Eastwo od; Starring=Clint; etc), and numbers being sorted with 1 < 2 < 34 < 101, etc.

Selecting any displayed choice in lbxSelect displays the correct record by fMain.Filter="K ey=" & lbxSelect. No need to consider any filter choices by user, as all done by NewLetter and presented in lbxSelect, not by fMain.Filter. I can also add Next & Previous buttons or program the arrow keys, as these use the lbxSelect.Colum n property and fMain.Filter=.

All of the above works fine. The error occurred when I split lbxAlpha into lbxAlpha1 and lbxAlpha2, in order to get larger letters (FontSize=20). As NewLetter is passed a length=1 string containing a letter (or 9 or *), it still works. Starting the program works as long as I select entirely within either lbxAlpha1 or within lbxAlpha2. When I change from lbxAlpha1 to lbxAlpha2, the code for lbxAlpha1_click is executed. It is not called from executing lbxAlpha1 = "", as removing this statement has no effect.

I have re-booted the computer as sometimes Access gets messed up. I use Windows XP (ver 5.1, SP 2) and Access 2002.

[MMcCarthy]

 
Sub NewLetter (strLetter as String)    
   Forms!fMain.lbxSelect.RowSource = _
      "SELECT tMovies.Key, tMovies.Title " & _
      "FROM tMovies " & _
      "WHERE (tMovies.Title LIKE """ & strLetter & "*"") " & _     
      "ORDER BY tMovies.Title;"  
   Forms!fMain.lbxSelect.Requery
   Forms!fMain.Filter = "Key=" & Forms!fMain.lbxSelect.Column(0,0)
 
End Sub 'NewLetter
 

Try adding a requery statement as in the above.

Mary

[OldBirdman]

Doesn't change anything.

lbxAlpha1 will work fine as long as I don't try to use lbxAlpha2. lbxAlpha2 works until I try to use lbxAlpha1.

The code always ends up in the wrong lbxAlpha_Click subroutine, with both lbxAlpha1 and lbxAlpha2 = Null. Clicking lbxSelect produces the same error, code ends up at Call NewLetter(lbxAl pha1) if lbxAlpha1 was the ListBox I started with.

Initialization (Form_Load) calls lbxAlpha1("*"), but this doesn't stop me from clicking lbxAlpha2. So it the click, not the call to lbxAlpha1 that is the problem. I have no events in the Project that capture mouse events.

I very much appreciate your attention to this. Thank you.

[MMcCarthy]

 
Private Sub lbxAlpha1_AfterUpdate()
   lbxAlpha2 = ""
   NewLetter(lbxAlpha1)
End Sub 'lbxAlpha1_Click
 
Private Sub lbxAlpha2_AfterUpdate()
   lbxAlpha1 = ""
   NewLetter(lbxAlpha2)
End Sub 'lbxAlpha2_Click
 

Try putting them in the AfterUpdate instead of the Click event.

Mary

[OldBirdman]

This results in the ListBox clicked having no value, not even Null.

When Private Sub lbxAlpha1_Click () then Immediate Window:
?lbxAlpha1
Null
?lbxAlpha2
Null

When Private Sub lbxAlpha1_After Update() then Immediate Window:
?lbxAlpha1

?lbxAlpha2
Null

[MMcCarthy]

Can you post the row source of lbxAlpha1. Also what is the bound column number.