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.

[OldBirdman]

lbxAlpha1
.ColumnCount=1
.ColumnWidth=1"
.ControlSource=
.RowSourceType= Value List
.RowSource="*"; "A";"C";"E";"G" ;"I";"K";"M";"O ";"Q";"S";"U";" W";"Y"
.BoundColumn=1

lbxAlpha2 - Same except
.RowSource="9"; "B";"D";"F";"H" ;"J";"L";"N";"P ";"R";"T";"V";" X";"Z"

lbxSelect
.ColumnCount=2
.ColumnWidth=0" ;20"
.ControlSource=
.RowSourceType= Table/Query
.RowSource=
.BoundColumn=1

[MMcCarthy]

Try this ...

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
   
End Sub 'NewLetter

Private Sub lbxAlpha1_AfterUpdate()
   Me.lbxAlpha2 = Null
   NewLetter(Me.lbxAlpha1)
End Sub 'lbxAlpha1_Click

Private Sub lbxAlpha2_AfterUpdate()
   Me.lbxAlpha1 = Null
   NewLetter(Me.lbxAlpha2)
End Sub 'lbxAlpha2_Click

[OldBirdman]

No apparent effect with these changes. It is executing the WRONG code. I click lbxAlpha2, and the error occurs in Private Sub lbxAlpha1_After Update(). In that procedure, lbxAlpha1 is supposed to be Null. That part is OK. The underlying question is "Why does Access execute the wrong code?", which is kind of where I started.

I tried running this on another computer, Access 2000 instead of 2002, and get a message "Update or CancelUpdate without AddNew or Edit."

As I do not believe I have made any changes to a bound control, I should not get that error. As the results are not the same on both computers, either Access has a bug, or some interaction within my code is causing problems. Sometimes I feel like I spend 2/3 of the time just getting around Access problems, and 1/3 writing code. Somewhat frustrating figuring out whether it is my problem or Microsoft's.

There is something beyond these 3 ListBoxes going on here. I will need a day to work on this myself, with the entire code. The code I have presented here is, of course, stripped of all the little details.

I just now started with a new database, 4 controls lbxAlpha1, lbxAlpha2, lbxSelect, and txtTitle bound to Title, the form bound to table tMovie. I copied my original code, as presented in this forum. I added:

Private Sub lbxSelect_Click ()
Forms!fMain.Fil ter = "Key=" & lbxSelect
End Sub 'lbxSelect_Clic k

It works as expected. Although I copied the original tMovies from the old program, the code will work with any table named "tMovies" with a field named "Key" and another named "Title".

[OldBirdman]

I found it!!!!

The offending statement is "Forms!fMain.lb xTitleSelect.Se lected(0) = True" This is to highlight the correct Title in lbxSelect when the selection was not made with the mouse, but programically (arrow keys or clicking lbxAlpha1, etc.). "Forms!fAAA.lbx Select = iixFormKey" where iixFormKey is the key just found in NewLetter is the correct statement.

I can see nothing wrong with the offending statement. I wanted to select the first item (default for new letter). Microsoft Visual Basic Help has:
You can use the Selected property to select items in a list box by using Visual Basic. For example, the following expression selects the fifth item in the list:
Me!Listbox.Sele cted(4) = True


I wish to thank you for all your effort. I feel like I got personal attention and not a cursory response. I will have more questions, asked on a new thread, but not immediately. Thank you. OldBirdman

[MMcCarthy]

I found it!!!!

The offending statement is "Forms!fMain.lb xTitleSelect.Se lected(0) = True" This is to highlight the correct Title in lbxSelect when the selection was not made with the mouse, but programically (arrow keys or clicking lbxAlpha1, etc.). "Forms!fAAA.lbx Select = iixFormKey" where iixFormKey is the key just found in NewLetter is the correct statement.

I can see nothing wrong with the offending statement. I wanted to select the first item (default for new letter). Microsoft Visual Basic Help has:
You can use the Selected property to select items in a list box by using Visual Basic. For example, the following expression selects the fifth item in the list:
Me!Listbox.Sele cted(4) = True


I wish to thank you for all your effort. I feel like I got personal attention and not a cursory response. I will have more questions, asked on a new thread, but not immediately. Thank you. OldBirdman

Delighted to hear you've found your problem. As you probably already knew errors are often in the most unexpected places.

Mary