Preventing Duplicate value to be entered into Combobox

[Rx_]

[solved] Access 2007 The other post were helpful but the search on keywords was not that straighforward. Just posting another solution that might be a little easier for people to find. Also, in three weeks, I will forget the error trap numbers and want to find this information again.

Others are encouraged to add links and solutions here.
There are probably much better examples than mine!

Steps:
A Combo Box has a set of values that list Well Pads (i.e. group)
An option allows the user to add a new Well Pad.
An Inputbox appears (sorry, it's my customer's preferance to use an Inputbox) to enter the new Well Pad name.
If the new Well Pad name is blank or less than 2 characters, or the Cancel button is selected (empy string) then cancel the operation.
If the new Well Pad name submitted already exist, notify the user and cancel the operation.

Solution:
On the underlying data table, set an Index for the Pad Name and select No Duplicates.
A duplicate name will cause an Error: Case 3022 or 3251
Look at the Microsoft Web site for DAO and Jet errors:
http://msdn.microsoft.com/en-us/library/bb221208(v=office.12).aspx

3022 appeared to be the logical choice for Access 2007.
Instead 3251 "operation not supported for this object type" worked.

Trap the error in the Combo Box Before Update event.
Use the Undo to return to the previous value

Private Sub Wells_Pad_Group_BeforeUpdate(Cancel As Integer)
      ' If this is updated - log the change to the Wells_ProgSentTofield_History via function Log_ProgSentToField
      Dim Answer                  As String
      Dim Result                  As Boolean
      Dim ID_PAD_Name_Selected    As Integer
      Dim NewPADName              As String
      Dim NewID_Area              As Integer ' last minute request in design - when creating  new pad - copy area
      'Dim NewPADID                As Integer  ' can' update here - move to form level variable
10    On Error GoTo Err_Wells_Pad_Group_BeforeUpdate
20     ChangePadGroupToBlank = False    ' sorry to do it this way - it was a last minute change order
30    ID_PAD_Name_Selected = Me.Wells_Pad_Group.Value
40    NewPADID = Me.Wells_Pad_Group.Value   ' keep value unless changed
50        Select Case ID_PAD_Name_Selected
          ' table Wells_PAD_Name - ID_PAD_Name    - 1 (blank no pad) 2 Create New Pad - the rest are PAD names
          Case 1      ' not part of any group (blank text value) but list box will need refreshed
60            Me.Refresh
70            Exit Sub
80        Case 3      ' a change after remove - can't use the blank added a "remove"
90             ChangePadGroupToBlank = True  ' the blank became a 3 "change to none"
               ' can't change data during this event -
               'Me.Wells_Pad_Group.Value = 1  ' they pick "remove" it is actually empty i.e. 1
 
100       Case 2      ' Add a new addition to the list
110            Answer = MsgBox("Do you want to add a new Well PAD Group to the List?", vbYesNo, "Confirm Add New Group")
120               If Answer = vbYes Then
                          ' What is the name of your new group?
130                       NewPADName = Trim(Left(InputBox("Enter the new PAD Group Name", "Add New PAD Group"), 20))
140                           If Len(Trim(NewPADName)) < 2 Then
150                               Cancel = True ' this test for InputBox cancel (i.e. "") or too short of name
160                                 Me.Wells_Pad_Group.Undo
170                               Exit Sub
180                           End If
 
                          ' call custom function to add new name - return the new ID number
                          ' if input box cancles - exit routine
                          ' last minute design request
190                       NewID_Area = Me.ID_Area.Value
200                       NewPADID = Log_Wells_Create_New_Pad(NewPADName, NewID_Area) ' Custom function adds new values to table
                          ' Set me.wells_Pad_Group.value to this new number
                          'Me.Wells_Pad_Group.Value = NewPADID     ' can't change value during update - moved to after update
210                       Call LogUsage("Well Pad Group", "Additional Well PAD Group", "Successful update New Well PAD Group")
220               Else
230                       Cancel = True
240                       Me.Wells_Pad_Group.Undo
250                       Call LogUsage("Well Pad Group", "Additional Well PAD Group", "cancled update New Well PAD Group")
260               End If
270       Case Else     ' A ID_PAD_Name from the list was chosen - go with it
 
280       End Select
290       Me.lstAdditionalWellsOnPad.Requery
Exit_Wells_Pad_Group_BeforeUpdate:
300       On Error Resume Next
310       Exit Sub
 
Err_Wells_Pad_Group_BeforeUpdate:
320       Select Case Err.Number
              Case 3022, 3251     ' duplicate value being entered on indexed field in table (Rx)
330                 MsgBox "The new name enterd for a  Well Pad Group already exist", vbOKOnly, "New Name Validation"
340                 Cancel = True
350                 Me.Wells_Pad_Group.Undo
360                 Err.Clear
370                 Call LogUsage("Well Pad Group", "Additional Well PAD Group", "canceledd due to duplicate new name")
380                 Exit Sub
                    ' Note: the above is trapped because the table's field PAD Name   is set to "indexed, No Duplicates"
390           Case 2450
                  'the home form isn't open, so it can't do a security check
400           Case 2115
                  ' can't change the value during BeforeUpdate - do it in the next event
410               Err.Clear
420           Case Else
 
430       End Select
 
440       Resume Exit_Wells_Pad_Group_BeforeUpdate
End Sub

 

[ghudson]

How do your users edit a keyed Well Pad if they noticed they misspelled it or want to change it? Or delete it?

 

[Rx_]

Excellent Point: Short answer... they don't
After creating a new Well Pad group with a bad name, they could remove the well from that group and try again, and again to spell it right!

How do your users edit a keyed Well Pad if they noticed they misspelled it or want to change it? Or delete it?

After inheriting a large databae written over time by several people with different styles ... this seemed to be a better solution.

A different Application Admin Maintenance front end that links to the tame Back End was created for maintenance purposes. It allows the "Appointed Application Admin" to change all the Well Pad names since they are (always) in the middle of coming up with a naming convention(s) and rules.

The application is used nationally. In the past, they had users dueling with constant name changes. It is much more under control now with a desginated Application Admin.

Instead of Deleting a Record, the Application Admin has is a column for Activity - mark each record as Active or Inactive. This will prevent Inactive ones from showin up on a combobox. Only after it is verified that no wells are associated with that inactive pad name will the option to delete be enabled to delete the record with the mis-spelled name.

You made me laugh. I was actually worried that you were going to comment that my code postings were "way too long" instead of incomplete.