- yes, just one person, so no person was actually going to be in the database
- i didn't want to have individual dates associated with each task, avoiding entering in the date over and over
- was trying to avoid rows and rows of duplicate data (so much of this stuff seems to be a judgement call without any hard fast rules)
- yes, realize the database is far from done, but just wanted to resolve the biggest issues first, because so much hinges on how the tables are finally setup.
I'd like to create a form with several checkboxes, but I'm not sure how to structure the tables to capture the checked items
- Thread starter Bettany
- Start date
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 01:49
- Joined
- May 21, 2018
- Messages
- 9,989
I will see if I can demo this. I think you need to see it. There should not be any duplication, that is the beauty of a relational db. You only need a key to relate to the other tables.
The big question I have if those choices are singular within a group. Those look like option groups to me. If that is the case, I know of no way to do this but unbounded (or a lot of code). Again, if you can answer how often (if ever) those choices my change? If extremely rare then I can propose an unbound form. If not you will have to consider a different form design. Instead of clicking a task, I would see combo boxes where you choose a value. In truth the thing you are calling tasks, look more like task values.
The big question I have if those choices are singular within a group. Those look like option groups to me. If that is the case, I know of no way to do this but unbounded (or a lot of code). Again, if you can answer how often (if ever) those choices my change? If extremely rare then I can propose an unbound form. If not you will have to consider a different form design. Instead of clicking a task, I would see combo boxes where you choose a value. In truth the thing you are calling tasks, look more like task values.
That would be amazing
Oh my god! Thank you!!!!! That's EXACTLY what i'm looking for!!! can you share your table structure, and did you have to do a ton of code?
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 01:49
- Joined
- May 21, 2018
- Messages
- 9,989
Actually I do not know what I was thinking. Even if you want to limit the choices to only one value per group, doing that with the subform is easier than I was thinking. In that case I would do it just like Uncle Gizmo did with the 9 subforms and code it to allow one choice per group (if that is a requirement). If you look at my first example the amount of code to do this is very very little. It would be the same regardless of how many copies of the subform. FYI Uncle Gizmo is in UK time, so probably cannot get an upload until the AM.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 01:49
- Joined
- May 21, 2018
- Messages
- 9,989
Here is my attempt. The amount of code is not a lot, however it is not the simplest code because you are working with queries and recordset updates.
I left the last subforms for you. You need to do two things. Fill in the task table and then set the tag property of the subform to be the group name.
FYI, if you want to add a date not in the cmbobox then you can just type it in. If it is todays date then hit the today button. It will move or add the current person and date records.
I left the last subforms for you. You need to do two things. Fill in the task table and then set the tag property of the subform to be the group name.
FYI, if you want to add a date not in the cmbobox then you can just type it in. If it is todays date then hit the today button. It will move or add the current person and date records.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 01:49
- Joined
- May 21, 2018
- Messages
- 9,989
Subform code
mainform code
Code:
Private Sub Selected_Click()
If Selected Then
AddSelection
Else
RemoveSelection
End If
End Sub
Public Sub AddSelection()
Dim strSql As String
Dim DailyDate As Date
Dim SelectionID As Long
Dim PersonID As Long
If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
Else
PersonID = Me.Parent.CmboPerson
SelectionID = Me.TaskSelection_ID
DailyDate = Me.Parent.cmboDate
strSql = "Insert into tblPersonDailyTasks (Person_ID_FK, TaskSelection_ID_FK, TaskDate) VALUES (" & PersonID & ", " & SelectionID & ", #" & Format(DailyDate, "MM/DD/YYYY") & "#)"
Debug.Print strSql
CurrentDb.Execute strSql
End If
End Sub
Public Sub RemoveSelection()
Dim strSql As String
Dim DailyDate As Date
Dim SelectionID As Long
Dim PersonID As Long
If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
Else
PersonID = Me.Parent.CmboPerson
SelectionID = Me.TaskSelection_ID
DailyDate = Me.Parent.cmboDate
strSql = "Delete * from tblPersonDailyTasks WHERE Person_ID_FK = " & PersonID & " AND TaskSelection_ID_FK = " & SelectionID & " AND TaskDate = #" & Format(DailyDate, "MM/DD/YYYY") & "#"
Debug.Print strSql
CurrentDb.Execute strSql
End If
End Submainform code
Code:
Option Compare Database
Option Explicit
Private Sub CmboDate_AfterUpdate()
LoadChecks
Me.cmboDate = cmboDate
End Sub
Private Sub cmboDate_Enter()
Dim strSql
strSql = "SELECT DISTINCT tblPersonDailyTasks.TaskDate FROM tblPersonDailyTasks where PERSON_ID_FK = " & Me.CmboPerson & " ORDER BY TaskDate DESC"
cmboDate.RowSource = strSql
End Sub
Private Sub CmboPerson_AfterUpdate()
LoadChecks
End Sub
Private Sub cmdToday_Click()
Me.cmboDate = Date
LoadChecks
End Sub
Private Sub Form_Current()
LoadChecks
Me.CmboPerson = Me.CmboPerson.ItemData(0)
Me.cmboDate = Date
End Sub
Private Sub Form_Load()
Dim frm As Access.Form
Dim sFrm As Access.SubForm
Dim ctrl As Access.Control
'set filter for each subform
For Each ctrl In Me.Controls
If ctrl.ControlType = acSubform Then
Set frm = ctrl.Form
frm.Filter = "Group = '" & ctrl.Tag & "'"
frm.FilterOn = True
frm.Requery
End If
Next ctrl
Me.CmboPerson = Me.CmboPerson.ItemData(0)
Me.cmboDate = Date
LoadChecks
End Sub
Public Sub LoadChecks()
Dim strSql As String
Dim rs As DAO.Recordset
'First uncheck everything in tblSelections
strSql = "Update tblTaskSelections Set Selected = FALSE"
CurrentDb.Execute strSql
'Add the users selections
strSql = "Select * from tblPersonDailyTasks where Person_ID_FK = " & Me.CmboPerson & " AND TaskDate = #" & Format(Me.cmboDate, "MM/DD/YYYY") & "#"
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
strSql = "Update tblTaskSelections set Selected = true where TaskSelection_ID = " & rs!TaskSelection_ID_FK
CurrentDb.Execute strSql
rs.MoveNext
Loop
Me.Refresh
End Sub
Private Sub ClearChecks()
Dim strSql As String
'First uncheck everything in tblSelections
strSql = "Update tblTaskSelections Set Selected = FALSE"
CurrentDb.Execute strSql
End SubMajP
You've got your good things, and you've got mine.
- Local time
- Today, 01:49
- Joined
- May 21, 2018
- Messages
- 9,989
Also you never answered if they can have multiple checks in a group. Does not make sense to me to do more than one. If they bike 12 minutes no need to save that they did less than that as well. If you want only one that is a pretty simple code.
If you want only one per group use this in subform instead
If you want only one per group use this in subform instead
Code:
Private Sub Selected_Click()
If Selected Then
AddSelection
Else
RemoveSelection
End If
End Sub
Public Sub AddSelection()
Dim strSql As String
Dim DailyDate As Date
Dim SelectionID As Long
Dim PersonID As Long
ClearGroup
If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
Else
PersonID = Me.Parent.CmboPerson
SelectionID = Me.TaskSelection_ID
DailyDate = Me.Parent.cmboDate
strSql = "Insert into tblPersonDailyTasks (Person_ID_FK, TaskSelection_ID_FK, TaskDate) VALUES (" & PersonID & ", " & SelectionID & ", #" & Format(DailyDate, "MM/DD/YYYY") & "#)"
' Debug.Print strSql
CurrentDb.Execute strSql
End If
End Sub
Public Sub RemoveSelection()
Dim strSql As String
Dim DailyDate As Date
Dim SelectionID As Long
Dim PersonID As Long
If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
Else
PersonID = Me.Parent.CmboPerson
SelectionID = Me.TaskSelection_ID
DailyDate = Me.Parent.cmboDate
strSql = "Delete * from tblPersonDailyTasks WHERE Person_ID_FK = " & PersonID & " AND TaskSelection_ID_FK = " & SelectionID & " AND TaskDate = #" & Format(DailyDate, "MM/DD/YYYY") & "#"
Debug.Print strSql
CurrentDb.Execute strSql
End If
End Sub
Private Sub ClearGroup()
Dim strSql As String
strSql = "UPDATE TblTaskSelections SET Selected = False WHERE TaskSelection_ID <> " & Me.TaskSelection_ID & " AND [Group] = '" & Me.Group & "'"
'Debug.Print strSql
CurrentDb.Execute strSql
Me.Refresh
End Sub
Last edited:
Wow, I cannot thank you enough for this. I'm really floored by the generosity of this community, and that people go to these lengths to help others. I know this was a lot of work and I'm extremely grateful! I'm sorry about the delay in replying, dealing with sick family members and trying to help out so things have been extremely hectic.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 01:49
- Joined
- May 21, 2018
- Messages
- 9,989
No problem. A lot of us here have done it before or have a library of code to reuse, so not as much work as it looks.
However, I have to caution you here. You have created a nice user interface, but it is not the traditional out of the box approach and is more complicated than. So you might have trouble changing this. However, it is pretty well designed. If you want to add more groups, change the text or add choices that should no problem. To add just fill in the table just like was done. Add an additional subform control and tag the control with the proper group name.
However, with this table design you will be able to make reports with much more detail of tasks accomplished. So detailed daily logs with numbers, and breakouts of tasks will be easy to do. Good luck.
However, I have to caution you here. You have created a nice user interface, but it is not the traditional out of the box approach and is more complicated than. So you might have trouble changing this. However, it is pretty well designed. If you want to add more groups, change the text or add choices that should no problem. To add just fill in the table just like was done. Add an additional subform control and tag the control with the proper group name.
However, with this table design you will be able to make reports with much more detail of tasks accomplished. So detailed daily logs with numbers, and breakouts of tasks will be easy to do. Good luck.
Thanks so much for your time and effort on this, I really appreciate it. Did you have to use any code to make that form?
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 01:49
- Joined
- May 21, 2018
- Messages
- 9,989
That seems pretty complicated. If you take advantage of the form filter approach on the subforms, I think you would save a lot of code. This was the entire code to set the form up, and does not change regardless of the amount of subforms. No need to create sql strings or recordsets. Just tag each subform control with the group to filter and let the subforms do the work.
