INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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    ProjectName    ListName    Default
1                        alpha                Bob            True

2                        baker                Bob
2                        baker                Jim                
2                        baker                Ann            True

3                        bravo                Ann            True

3                        gamma            Bob            True
3                        gamma            Sal            
       
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?

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

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.

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

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"

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

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).

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

(OP)
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

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

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"

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

(OP)
Thanks MazeWorX.  I suspected that was what you meant.

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

(OP)
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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close