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?
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?
Talk To Other Members
RE: Designate a default list name, from a table of list names
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
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
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
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
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
RE: Designate a default list name, from a table of list names
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
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