Need to "rehide" a listbox (1 Viewer)

[Divit11]

Hello All,

After much hunting I must come back to the Forum for further direction. I am trying to add a listbox to my form (ACC2000) which will pop up when a particular condition occurs. I have a combobox, cboPCID, that lists cities from tblPostalCodes table. I added a listbox, lstZip, with properties RowSource blank and with Visible set to No. In the Private Sub cboPCID_AfterUpdate() routine I have code to create a SQL SELECT statement and append it to lstZip.RowSource.

This all works fine but once a selection is made in the listbox and focus moves to another control or the record is saved and the form refreshes that listbox is still in view with the old list.

Any hints or direction to clarify this problem will be greatly appreciated.

D

 

[boblarson]

In the list box's Lost Focus event put

Me.YourListBoxName.Visible = False

 

[Divit11]

Thanks Bob for your response.

I am still very new at Access and do not really understands the order of events when clicking from object to object in a form.
This is the code I currently am using and perhaps you or another reader can explain how this list box functions as a "subroutine" of my combo box.

Private Sub cboPCID_AfterUpdate()
    On Error Resume Next
    Dim strSelectedCity As String
    Dim NbrCities As Variant
    Dim strSQL As String
       
    strSelectedCity = StrConv(cboPCID.Text, vbProperCase)
    NbrCities = DCount("[tblPostalCodes].[pcCity]", "tblPostalCodes", "[tblPostalCodes].[pcCity] = '" & strSelectedCity & "'")
    If NbrCities > 1 Then
        ' Display Listbox containing selected name and associated zipcodes
        Beep
        lstZip.Visible = True
        lstZip.SetFocus
        strSQL = "SELECT tblPostalCodes.pcID, tblPostalCodes.pcCity, tblPostalCodes.pcStProv, tblPostalCodes.pcZip " & _
        "FROM tblPostalCodes " & _
        "WHERE (((tblPostalCodes.pcCity) = '" & strSelectedCity & "')) " & _
        "ORDER BY tblPostalCodes.pcZip;"
        lstZip.RowSource = strSQL
        
    End If
    
End Sub

Private Sub lstZip_AfterUpdate()
    On Error Resume Next
    
    MsgBox "Sub lstZip_AfterUpdate"  'used to help during debug process
    Me.lstZip.Visible = False
    
End Sub

Private Sub lstZip_LostFocus()
    Me.lstZip.Visible = False       ' This is where the Run Time error occurs
End Sub

Currently I get a Run-Time error, 2165 "You can't hide a control that has the focus".

D

 

[battenberg]

set the focus to the next appropriate control:

someothercontrol.setfocus

 

[Divit11]

Thanks Battenberg,

That works!

Now I need to find a brief summary of event explainations and in what order they and be fired as one tabs between controls of these forms.

D