ComboBox problem: choosing item 'dirties' the form, so won't move off record

[julietbrown]

I was scolded on "Bytes" a couple of weeks back for writing all my own Save/Delete/Next etc buttons, so I have completely redone my main "Contacts" form using the button wizard. (I've also converted all the Macros to VBA so that I can more or less understand what they are actually doing!) Must admit this has cleaned things up a lot, but I have hit a problem ... as follows

There is an unbound "Find Contact" combo box on the form which lists all the Contact last names, where the user USED TO be able to pick a surname, and have that record appear on the form. There is code behind this which says "if the form isn't dirty, display new record for the name chosen". However, NOW this doesn't work because the action of choosing a name off the list "dirties" the form and Access won't move to a new record if the form is dirty. (If I take out the "If not dirty ..." bit of code, it just crashes - of course!)

I can think of some revolting possible ways around this, but it seems such an obvious sort of problem there's probably an obvious and simple solution which all you guys will know about????

[julietbrown]

Replying to myself!

I solved this problem in the wee small hours after rather a lot of alcohol! The solution may be useful to someone else, and anyway it will stop you bothering to answer the question.

You need to 'clean' the FindContact combo-box after the selection has been made ... so store the ContactID in a local variable, set the combo-box back to its old value, and THEN find the new record. Here's the code ...

Private Sub FindContactCombo_Change()
    Dim contID As Long
    Dim pos As Integer
    pos = Me.FindContactCombo.ListIndex
    If pos <> -1 Then
            'save the ContactID in the var contID
        contID = Me.FindContactCombo.Column(0, pos)
            'reinstate the old value for FindContactCombo,
            'so that this field is 'clean'
        Me.FindContactCombo.Value = Me.FindContactCombo.OldValue
           'if no other changes have been made ...
        If Not (Me.Dirty) Then
               'bookmark the record for contID
            Dim rst As Recordset
            Set rst = Me.RecordsetClone
            rst.FindFirst "ContactID = " & contID
                If rst.NoMatch Then
                    MsgBox "Record not found"
                Else
                    Me.Bookmark = rst.Bookmark
                End If
            rst.Close
        Else
               'changes have been made to the current record
            MsgBox ("Please either save or cancel the changes " _
                & "you've made before moving away from this Contact.")
        End If
    End If
End Sub

Sorry to have bothered you with this. I'm very grateful for all the help I've had from Bytes, as now the code behind this ContactForm is about 1/4 of what it was before when I was coding all the buttons myself, and about 4 times more comprehensible/maintainable.

[ADezii]

Forgive me if I am not correct in my assumption, but I do not think that selecting a Value in an 'Unbound' Combo Box will Dirty the Form.

[julietbrown]

That's EXACTLY what I thought. It really is unbound, but the row source is using the same table as the one the form is bound to ... i.e. the records for the form are the "Contacts" from the CONTACT_table, and the row source for the ComboBod is "SELECT ContactId, FamilyName FROM CONTACT_table". Is that the problem? I feel SURE that in the dim-distant past I didn't have this problem, but it was 10 years ago, so maybe I've just forgotten about it!

It's annoying, actually, because altho' my solution "works" it doesn't quite ... Obviously it precludes the user typing in the first few letters and getting the name "predicted" , so he/she has to scroll down the combo-box ... I'm still pondering on whether there's another solution. Any thoughts? Or maybe your "thoughts" will be that I've just done something dumb ... I hope so!

[ADezii]

You could dynamically populate a Temporary Table from the 2 Fields from the Contacts Table, then use that as the Source for the Combo Box.

[julietbrown]

Yes, thank you, that's a good idea, and I'll probably end up doing that. It just seems a bit labour intensive, doesn't it!

[ADezii]

It just seems a bit labour intensive, doesn't it!

Yes, but if you current solution does not agree with you, then it is simply a matter of priorities.

[julietbrown]

Yup! Done it! (I sometimes feel like I'm back at school ... these gentle reprimands!)