By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,010 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

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

P: 99
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????
Feb 11 '10 #1
Share this Question
Share on Google+
7 Replies

P: 99
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 ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub FindContactCombo_Change()
  2.     Dim contID As Long
  3.     Dim pos As Integer
  4.     pos = Me.FindContactCombo.ListIndex
  5.     If pos <> -1 Then
  6.             'save the ContactID in the var contID
  7.         contID = Me.FindContactCombo.Column(0, pos)
  8.             'reinstate the old value for FindContactCombo,
  9.             'so that this field is 'clean'
  10.         Me.FindContactCombo.Value = Me.FindContactCombo.OldValue
  11.            'if no other changes have been made ...
  12.         If Not (Me.Dirty) Then
  13.                'bookmark the record for contID
  14.             Dim rst As Recordset
  15.             Set rst = Me.RecordsetClone
  16.             rst.FindFirst "ContactID = " & contID
  17.                 If rst.NoMatch Then
  18.                     MsgBox "Record not found"
  19.                 Else
  20.                     Me.Bookmark = rst.Bookmark
  21.                 End If
  22.             rst.Close
  23.         Else
  24.                'changes have been made to the current record
  25.             MsgBox ("Please either save or cancel the changes " _
  26.                 & "you've made before moving away from this Contact.")
  27.         End If
  28.     End If
  29. 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.
Feb 12 '10 #2

Expert 5K+
P: 8,638
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.
Feb 12 '10 #3

P: 99
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!
Feb 12 '10 #4

Expert 5K+
P: 8,638
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.
Feb 12 '10 #5

P: 99
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!
Feb 13 '10 #6

Expert 5K+
P: 8,638
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.
Feb 13 '10 #7

P: 99
Yup! Done it! (I sometimes feel like I'm back at school ... these gentle reprimands!)
Feb 13 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.