Listbox only makes Exclamation Sound (1 Viewer)

[Divit11]

Hi All,

I have been trying to improve a form used to add/edit names in a mailing list. What I want to do is check the database for possible duplicate names and alert the user to the fact that their new person may already be in the db.

I used the following VBA code in the After Update event as follows:

Private Sub txtLastName_AfterUpdate()

    On Error Resume Next
    Dim strLastName As String
    Dim NbrNames As Variant
    Dim strNameSQL As String
    
    txtLastName = Trim(txtLastName)
    If Not Mid(txtLastName, 1, 1) = "=" Then
        txtLastName = StrConv(txtLastName, vbProperCase)
    Else
        txtLastName = Mid(txtLastName, 2, 999)
    End If
    
    ' Test for duplicate last name in data base
    NbrNames = DCount("[tblMemberListings].[mlLastName]", "tblMemberListings", "[tblMemberListings].[mlLastName]= '" & txtLastName & " '")
    If NbrNames > 0 Then
        strNameSQL = "SELECT ALL tblMemberListings.mlID, tblMemberListings.mlLastName, tblMemberListings.mlFirstName, tblMemberListings.mlSpouseSO, tblMemberListings.mlAddress " & _
                     "FROM tblMemberListings " & _
                     "WHERE ((tblMemberListings.mlLastName) LIKE '" & txtLastName & "*') " & _
                     "ORDER BY tblMemberListings.mlLastName, tblMemberListings.mlFirstName, tblMemberListings.mlSpouseSO;"
        lstDuplicateNames.RowSource = strNameSQL
        Beep
        lstDuplicateNames.Visible = True
        
    End If
    
    
End Sub

This generates a useful list of potential persons already in the db. The user then has the choice of continuing to add the new name and address or they should be able to select one of the names in the listbox and refresh the form with that person's data for possible modification.

I currently have these two Event Procedures in place:

Private Sub lstDuplicateNames_AfterUpdate()
    On Error Resume Next
    'MsgBox "Sub lstDuplicateNames_Click value = " & lstDuplicateNames.Column(0)
    
    If Not IsNull(lstDuplicateNames.Column(0)) Then
        ' User chooses and existing record
        Me.RecordsetClone.FindFirst "[mlID] = " & lstDuplicateNames.Column(0)
        If Not Me.RecordsetClone.NoMatch Then
            Me.Bookmark = Me.RecordsetClone.Bookmark
        End If
        Me.lstDuplicateNames.Visible = False
        Me.Refresh
    End If
    
End Sub

and

Private Sub lstDuplicateNames_Click()
    Me.txtHidden.SetFocus
    Me.lstDuplicateNames.Visible = False
    
End Sub

The problem is that when one clicks on one of the listed names just two things happen:

1 - a fine dotted box outline appears around the *first* record in the listbox
2 - the MS Exclamation wav sound is generated each time the mouse button is clicked.

Short of enrolling in a local junior college that offers Access 101 what can I do to get this addition working? I'm also not sure that my code to refresh the form is going to work, but right now I don't ever get there.

Oh, this problem is occurring in MS Access 2000.

Any help would be greatly appreciated by this NOOB.

Divit11

 

[RuralGuy]

Here's a couple of items I noticed in your code. Verification code should *really* be in the BeforeUpdate event rather than the AfterUpdate event as you have it. The BeforeUpdate event can be cancelled by setting Cancel = True and hold the focus in the current control. Also, I doubt you can set the control with the focus to Invisible.

 

[Divit11]

Soved Exclamation Problem

Thanks for the response RG.

I finally created a new test form with a listbox and some text controls and those worked as expected. After a little comparison of the listbox property sheets I found that I had the control source bound to mlID instead of making it unbound.

I took your suggestion and changed the AfterUpdate to BeforeUpdate. Now my popup feature works swell.

D

 

[RuralGuy]

Excellent! Thanks for posting back with your success.