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

Connie:

When you say "species in this list unless they have a different sub species" 
do you really mean list or duplicate in the database?

When the user selects a species is the sub-species box requeried to present 
only those sub-species which are valid for the selected species?

You probably need to check for duplicates in the after update event of the 
sub-species combo box.  If a duplicate is found tell the user and either use 
Me.Undo to undo all the changes or blank out the species and sub-species 
combo boxes and set the focus to the species box.

HTH

Rocky Smolin
Beach Access Software
http://www.e-z-mrp.com
858-259-4334

----- Original Message ----- 
From: <connie.kamrowski at agric.nsw.gov.au>
To: <accessd at databaseadvisors.com>
Sent: Saturday, April 16, 2005 8:48 PM
Subject: [AccessD] How to Question - check for duplicates in cascading 
comboboxes


>
> 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.
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>