[AccessD] How to Question - check for duplicates in cascading combo boxes

Hello all

I have a cascading combo box which presents the user with firstly a list of
species on the left and then subsequently a list of sub species on the
right when a species is chosen.

I have to ensure that there are no duplicates of species in this list
unless they have a different sub species. I figured the best way to do this
will be to check for a duplicate species and if there is one then check for
duplicate sub species, if there is then alert the user and set focus to
this duplicate.

I am a little unsure as to the best way to go about this, as they will need
to have chosen a species and a sub species before I do the check should
this be called on the before update of the sub species column? if so there
will already be an entry for species so I will need to delete it as well? I
have attached the code for the combo boxes for the sake of clarifying how
the lists are populated.


Connie Kamrowski

Analyst/Programmer
Information Technology
NSW Department of Primary Industries
Orange


<code> -----------------

Private Sub Species_AfterUpdate()

       Dim db As Database, rs As Recordset
       Dim strDefaultName As String
       Dim IntX As Integer

       If Not IsNull(Me!Species) Then
       IntSpecies = Me!Species

       End If

       Set db = CurrentDb()
       Set rs = db.OpenRecordset("SELECT
[tblCSubSpecies].[SubSpeciesID],[tblCSubSpecies].[SubSpecies],[tblCSubSpecies].[CommonName]"
 _
                              & "FROM [tblCSubSpecies] " _
                              & "WHERE [tblCSubSpecies].[SpeciesID]= " &
IntSpecies & ";")


       IntX = DCount("[SpeciesID]", "tblCSpecies", "[SpeciesID] = " &
IntSpecies & " ")

       Exit Sub

       If Not IsNull(Me.Species) Then
             IntSpecies = Me!Species
             Me!cboSubSpecies.RowSource = "SELECT DISTINCTROW
[qrySubSpeciesSelect].[SubSpeciesID],[qrySubSpeciesSelect].[SubSpecies]&'
('& [qrySubSpeciesSelect].[CommonName]&')' " _
                                        & " FROM [qrySubSpeciesSelect] " _
                                        & " WHERE
[qrySubSpeciesSelect].[SpeciesID] = " & IntSpecies & ";"
       Else
       End If

Exit_Species_AfterUpdate:
       On Error Resume Next
       If Not (rs Is Nothing) Then rs.Close: Set rs = Nothing
       On Error Resume Next
       If Not (db Is Nothing) Then Set db = Nothing

Exit Sub


End Sub
Private Sub cboSubSpeciesSource()
On Error GoTo Err_cboSubSpeciesSource

        If Not IsNull(Me!Species) Then
            IntSpecies = Me!Species
            Me!cboSubSpecies.RowSource = " SELECT DISTINCTROW
[qrySubSpeciesSelect].[SubSpeciesID],[qrySubSpeciesSelect].[SubSpeciesName]&'
  ('& [qrySubSpeciesSelect].[CommonName]&')' " _
                            & " FROM [qrySubSpeciesSelect] " _
                            & " WHERE [qrySubSpeciesSelect].[SpeciesID] = "
& IntSpecies & ";"

        Else
        End If
Exit_cboSubSpeciesSource:
Exit Sub

Err_cboSubSpeciesSource:
    Select Case Err
    Case 0      'insert Errors you wish to ignore here
        Resume Next
    Case Else   'All other errors will trap
        Beep
        MsgBox Err.Description, , "Error in function
Form_sfrmLoad.SubSpeciesSource"
    Resume Exit_cboSubSpeciesSource
    End Select
    Resume 0    'FOR TROUBLESHOOTING

End Sub


Private Sub cboSubSpecies_GotFocus()
    If Not IsNull(Me!Species) Then

            IntSpecies = Me!Species
             Me!cboSubSpecies.RowSource = " SELECT DISTINCTROW
[qrySubSpeciesSelect].[SubSpeciesID],[qrySubSpeciesSelect].[SubSpecies]&'
('& [qrySubSpeciesSelect].[CommonName]&')' " _
                            & " FROM [qrySubSpeciesSelect] " _
                            & " WHERE [qrySubSpeciesSelect].[SpeciesID] = "
& IntSpecies & " ; "
        Else
        End If

        Me.cboSubSpecies.Dropdown
End Sub

Private Sub cboSubSpecies_LostFocus()

            Me!cboSubSpecies.RowSource = " SELECT
[qrySubSpeciesSelect].[SpeciesID],[qrySubSpeciesSelect].[SubSpecies]&'  ('&
[qrySubSpeciesSelect].[CommonName]&')' " _
                            & " FROM [qrySubSpeciesSelect]; "

End Sub

------------------>

This message is intended for the addressee named and may contain
confidential information. If you are not the intended recipient or received
it in error, please delete the message and notify sender. Views expressed
are those of the individual sender and are not necessarily the views of
their organisation.