ComboBox Error when choosing with mouse, but not when typing

[KeithIT]

I have a combo box on my bound form which has a control source in the query and a row source of another query. The combo box has the following code associated with it's BeforeUpdate property:

Private Sub cboSiteName_BeforeUpdate(Cancel As Integer)

    Dim rs As Object
    Dim rst As New ADODB.Recordset
    Dim nRecords As Integer
    Dim site As String
    
    site = Me.cboSiteName.Column(0)
    
    Select Case MsgBox("Do you want to search for this record?", vbQuestion + vbYesNoCancel, "Search or Update")
    
    Case vbYes
        nRecords = DCount("*", "qrySiteListing")
        If nRecords > 1 Then
            DoCmd.OpenForm "frmSiteSelect", acNormal, , , , acWindowNormal
            Me.cboSiteName.Undo
            DoCmd.RunCommand acCmdSaveRecord
            Exit Sub
        Else
            Set rs = Me.RecordsetClone
            rs.FindFirst "[lngzSiteID] =" & site
            Me.cboSiteName.Undo
            If Not rs.EOF Then Me.Bookmark = rs.Bookmark
            DoCmd.RunCommand acCmdSaveRecord
        End If
    Case vbNo
        If MsgBox("Confirm you would like to update the information for this entry", vbExclamation + vbOKCancel, "Confirm!") = vbOK Then
            Exit Sub
        Else
        End If
        Cancel = True
    
    Case vbCancel
        Cancel = True
    End Select

End Sub

My problem is that (aside from any problems I may have in the code above) when I open the form and try to select an item in the list using the mouse, the combo box doesn't change the entry, until I select it a second time. When I select it the first time apparently it updates the record in the table, but not the combo box. It's quite strange. When I type the entry into the combo box and then select from the list that comes up after you type in the first few letters, it works fine everytime.

I don't know if the problem is in my code, my placement of the code (i.e.: in the BeforeUpdate property) or elsewhere, but any help or direction would be most appreciated. Thanks in advance!
Keith

 

[KeithIT]

Combo Box updating despite BeforeUpdate Event

Okay,
I had an event in the Click property of the box. So that problem is solved. Now here's the problem... whenever I tell it to search, rather than just searching for the record, it updates the value in the combo box to the record that is selected. Which I thought I had it not doing. Any thoughts?

 

[modest]

it updates the value in the combo box to the record that is selected

Sounds like your box is bound to a table/query field. You'll need an unbound combo/textbox

 

[KeithIT]

Here's an interesting one for you, I have two ways of searching. If there are more than one sites with a record in the query, then the db opens a form which lists all of the sites in detail so that you can select a specific one. I'm using a recordset clone and bookmark from the second form for this function. This works fine, and it does not cause the data in the table to update (I have it triggered in the before update event and I'm using me.combo.undo and cancel = true to prevent the update).

Now if there is only one site then the code opens a recordset clone, finds the first value (findfirst) and then sets the bookmark to move the form to the correct record. Here is where the combo box updates the value in the table rather than just searching like it does if there are multiple entries.

Is this a result of the combo box being bound? If not, then is there something I can set when I open the recordset that prevents this change?

Here is the code I am using behind this portion of the form:

Case vbYes  'To Search
        'determine the number of listings for the site name
        nRecords = DCount("*", "qrySiteListing")
        If nRecords > 1 Then
            'If multiple listing for site then open second form to select specific listing
            Me.cboSiteName.Undo
            Cancel = True
            DoCmd.OpenForm "frmSiteSelect", acNormal
            Exit Sub
        Else
            'If only one listing for the site than go to that listing
            Set rs = Me.RecordsetClone
            rs.FindFirst "[lngzSiteID] =" & Str(Nz(site, 0))
            Me.cboSiteName.Undo
            Cancel = True
            If Not rs.EOF Then Me.Bookmark = rs.Bookmark
        End If

I originally thought it was because the combo box was bound, but since it works when I use the second form I'm not longer sure.