Designate a default list name, from a table of list names

[sterlecki]

I have two tables: ContactLists and ContactListDetails. They are in a one-to-many relationship. Both are user created. A user inputs via a form:

OperationsGroup
ProjectName
Listname

And via a button generates an auto number ListID . In a similar fashion ContactIDs are added to the ContactListDetails table.

This table is further restricted through an index (ContactListIndex)where ListName,OperationsGroup, ProjectName are a unique row. ie an OperationsGroup can have more than one ProjectName. An OperationsGroup, ProjectName combination can have multiple Listnames. This same combination however CANNOT have duplicate Listnames although the table can have duplicate Listnames with other combinations of OperationsGroup and ProjectName.

I would like the user to assign a listname to be their default but make sure that no combination of OperationsGroup and ProjectName can have more than one ListName assigned as default.

The Table might look something like this

OperationsGroup[tab]ProjectName[tab]ListName[tab]Default
1[tab][tab][tab][tab][tab][tab]alpha[tab][tab][tab][tab]Bob[tab][tab][tab]True

2[tab][tab][tab][tab][tab][tab]baker[tab][tab][tab][tab]Bob
2[tab][tab][tab][tab][tab][tab]baker[tab][tab][tab][tab]Jim
2[tab][tab][tab][tab][tab][tab]baker[tab][tab][tab][tab]Ann[tab][tab][tab]True

3[tab][tab][tab][tab][tab][tab]bravo[tab][tab][tab][tab]Ann[tab][tab][tab]True

3[tab][tab][tab][tab][tab][tab]gamma[tab][tab][tab]Bob[tab][tab][tab]True
3[tab][tab][tab][tab][tab][tab]gamma[tab][tab][tab]Sal[tab][tab][tab]

I am looking for a method where if OperatonsGroup and ProjectName matched and contained multiple rows with different Listnames that only one(1) row would be allowed to be checked as the default.

This is a multiple user application and it needs to be dynamic and user controlled. The default designation would determine which contact list was printed in a report. They would also have the option of changing from the default list to their preferred list.

Should I consider using a third table as a Defaults Table containing the OperationsGroup, ProjectName and ListName?

If that combination of fields were restricted to being unique then when a user designated a list to be the default I could programatically lookup those field values and if found delete that row or if NOT found add the new row? Maybe I can do that in the existing table already and just check or uncheck the default field based on the results?

I have seen other applications where user preferences are set as defaults but have never built any such item.

Your thoughts and suggestions please?

 

[SeadnaS]

I'm thinking along the lines of maybe an append query that changes all the values of the Default field to false where the OperationsGroup and ProjectName matchthe record.

In other words when you select which one is true the query makes all the other ones "false" on redcords where their OperationsGroup and ProjectName match the record where you just changed the Default field to "true".

Hope you understand. I'm sure somebody can give a better explanation of what i'm trying to say.

 

[MazeWorX]

how about a uniqueID field in the table based on the 3 criteria with no duplicates

UniqueID = [OperationsGroup] & [ListName] & [ListName]

if the name exists force them to select another?

Thoughts ?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MajP]

I would do like you said and create the third table. Now the form to do this needs a few tricks.

1) The table should be a local table and not a linked table to the back end. That way each user can set their own defaults

2) When you open the form run an append query to ensure that you have a record for every op group, program combination. If there is a record in "defaults" table already it appends nothing, if there is a not a record then it adds the first list from your other table (as a place holder). Next run a delete query to delete any op group, program name no longer in your main table.

So now you will always have one and only one record for each op group, program name combination.

3)Now you could have a continous form with operations group locked, program name locked, and an editable combo to choose the default list. The query for the list would be limited to only those names for that op group, program. So you can pick from the available. (There is a little trick where you can put a textbox over the combobox showing only the combo pull down, because when you filter the combo other programs may go blank).

 

[sterlecki]

Thanks to all 3 of you MajP, MazeworX and SeadnaS for 3 Excellent suggestions.

I will have to experiment a little and decide which is the path of least resistance given my coding skills.

I will post my solution in the near future.

Thanks Again

 

[MazeWorX]

sorry it should of read UniqueID = [OperationsGroup] & [ProjectName] & [ListName]. I had ListName twice need to check before i hit the button

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[sterlecki]

Thanks MazeWorX. I suspected that was what you meant.

 

[sterlecki]

As promised here is my solution.

I used SeadnaS suggestion of using an "Update Query" to change all of the rows equal to OperationsGroup and ProjectName and set the Default checkbox to false.

Then a second query matching OperationsGroup, ProjectName AND Listname (which should return only 1 rowsince as an index these three fields are collectively unique) the code changes that Default Checkbox to True and is now set as the default.

My code is below:

Private Sub cmd_MakeListnameDefault_Click()

Dim db As DAO.Database
Dim rstListName As DAO.Recordset
Dim OperationsGroup As String
Dim ProjectName As String
Dim ListName As String
Dim strSQL As String
Dim strCriteria As String


'Assign variables from Form values

OperationsGroup = Me.cbo_OperationsGroup
ProjectName = Me.cbo_ProjectName
ListName = Me.cbo_Listname


'Create SQL string for recordset creation

    strSQL = " SELECT [OperationsGroup],[ProjectName],[ListName], [Default]," & _
                " [UserModified], [DateModified]" & _
                " FROM lkup_ContactLists " & _
                " WHERE [OperationsGroup] = """ & OperationsGroup & """" & _
                " AND [ProjectName] = """ & ProjectName & """"
            
           
    
    Set db = CurrentDb()

    'Open Recordset and Count the Number of Records
    
    Set rstListName = db.OpenRecordset(strSQL, dbOpenDynaset)
        rstListName.MoveLast
        rstListName.MoveFirst

    'MsgBox "The list contains  " & rstListName.RecordCount & " Records."

            strCriteria = "[OperationsGroup] = """ & OperationsGroup & """" & _
                          " AND [ProjectName] = """ & ProjectName & """" & _
                          " AND [Default] =  " & "True & """""
                          'MsgBox strCriteria
           
' Loop through recordset and change all default values to false
         
         With rstListName
           .FindFirst strCriteria
            Do While Not .NoMatch
            .Edit
         If rstListName!Default = True Then
            rstListName!Default = False
            !DateModified = Now()
            !UserModified = fOSUserName()
            .Update
         End If
             .FindNext strCriteria
             Loop
        
            .Close
            End With
    
 'Recreate Recordset to match the list designated for the list default
 
 strSQL = " SELECT [OperationsGroup],[ProjectName],[ListName], [Default]," & _
                " [UserModified], [DateModified]" & _
                " FROM lkup_ContactLists " & _
                " WHERE [OperationsGroup] = """ & OperationsGroup & """" & _
                " AND [ProjectName] = """ & ProjectName & """" & _
                " AND [ListName] = """ & ListName & """"


    Set db = CurrentDb()

    'Open Recordset and Count the Number of Records
    
    Set rstListName = db.OpenRecordset(strSQL, dbOpenDynaset)
        rstListName.MoveLast
        rstListName.MoveFirst

    'MsgBox "The list contains  " & rstListName.RecordCount & " Records."
    
    ' check to make sure there is only 1 record
    'set that record to be the default list for that Project
    
    With rstListName
           If .RecordCount > 0 And .RecordCount < 2 Then
           .MoveLast
           Else
           MsgBox "There cannot be 2 Lists with the same name!"
          Exit Sub
           End If
            .Edit
         If rstListName!Default = False Then
            rstListName!Default = True
            !DateModified = Now()
            !UserModified = fOSUserName()
            .Update
         End If
             
                    
            .Close
            End With
    
    Me.Refresh

End Sub

thanks for everyone's suggestions