Big scale check-box input logging? (1 Viewer)

mamradzelvy

Member
Local time
Today, 14:12
Joined
Apr 14, 2020
Messages
145
Hi,
I've got a form for tracking repairs etc. done to PCs in our shop and i'd like to track who's checked a check-box. i got some 45-ish check boxes in there and i got a table column extra for each of these to store the data in of who did what, namely TempVars UserName, however, i figured there must be an easier way of doing this rather than putting a piece of code in each of the box's on change events, or is there not?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:12
Joined
May 7, 2009
Messages
19,169
put the code to the Form's Before Update event
so you only do it one time.

check each checkbox.OldValue against it current value, checkbox.Value.
if they are not same, add the name of the culprit.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:12
Joined
Oct 29, 2018
Messages
21,357
Just FYI, crossposted at UA.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:12
Joined
May 7, 2009
Messages
19,169
Not for me since i seldom X-contribute.
 

plog

Banishment Pending
Local time
Today, 08:12
Joined
May 11, 2011
Messages
11,611
The data for those 45 check boxes need to go into a different table with just 4 fields. Let's call the existing table tblRepairs and assume it has a primary key called 'repair_ID' as long as other fields about the repair (customer, date, etc.). This is what that new table should look like to accomodate your 45 check box fields:

tblRepairItems
ri_ID, autonumber, primary key
ID_repair, number, foreign key to tblRepairs
repair_Item, string, this will hold what is now in the names of those 45 fields in tblRepair
repair_Person, string, this will hold who did it

That's it, those 4 fields will now accomodoate the data for your 45 checkboxes as well as any more checkboxes that get added in the future.

From a functional standpoint you will also need a new table that lists those 45 values that are now field names in tblRepairs. With that you can create an INSERT INTO query that will run and populate tblRepairItems each time a new repair is done and you need to create those 45 records in tblRepairItems.
 

Micron

AWF VIP
Local time
Today, 09:12
Joined
Oct 20, 2018
Messages
3,476
Is that a problem?
Well, a lot of the time around here lately I see that sort of post by the DB Guy but without what used to be the custom of pointing to an article about cross posting. If you care to read it, it is here
https://www.excelguru.ca/content.php?184

EDIT - I don't mean the dbg posts like that all the time; I mean I see that sort of post done by several different people but without the link.
Sorry if I rankled you a bit there, DBG.
 

bob fitz

AWF VIP
Local time
Today, 13:12
Joined
May 23, 2011
Messages
4,717
Is that a problem?
I find it very irritating sometimes, after I've spent my free time working on a solution for someone, to fine that they've already been given a solution by somebody in another post. If they had given a link to the other post, I would check to see if a solution had already been posted before wasting my time with it.
Even more irritating, is when they cross-post the problem, without a link to the original because they don't like the answer they've been given. They want the help but think they know better.

Sorry. Rant over.

Please don't crosspost without a link to the other post.

Please read the link Micron has given you on the subject :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:12
Joined
Oct 29, 2018
Messages
21,357
EDIT - I don't mean the dbg posts like that all the time; I mean I see that sort of post done by several different people but without the link.
Sorry if I rankled you a bit there, DBG.
@Micron No worries. I just didn't have that link handy. Over at UA, there's an explicit mention of "crossposting" in the Guidelines page (under the DON'Ts section). I couldn't find a similar entry here at AWF.

Is that a problem?
@mamradzelvy My post was directed at everyone who may read this thread (not just for the OP). Some people care and some people don't. However, there are certain protocols people have come to expect when communicating with others through discussion forums. Micron already posted a link to some of those expectations, so I don't need to repeat them. Just as an example, here's a sample post that we prefer to see.

Just to clarify, I wasn't trying to get anyone in trouble or intended to offend anyone. It was just a matter of courtesy and information dissemination. Cheers!
 

mamradzelvy

Member
Local time
Today, 14:12
Joined
Apr 14, 2020
Messages
145
Sorry to take this one out of retirement, however i found the answer from @cheekybuddha to result in an error once i tried to apply it.


Code:
Function UpdatedBy(ctlName As String) As Boolean

  Me(Me(ctlName).ControlSource & "X") = TempVars("UserName")

End Function

Code:
  Dim ctl As Control

  For Each ctl In Me
    If ctl.ControlType = acCheckbox Then
      ctl.AfterUpdate = "=UpdatedBy(" & ctl.Name & ")"
    End If
  Next

This is @cheekybuddha 's code from Utter Access on my cross post.


adameror.PNG


It appears to look for fields named after my check box value, either 0 or -1 . After toying around with it for some time now, i still wasn't able to figure it out.
Does anybody know why?
 

Micron

AWF VIP
Local time
Today, 09:12
Joined
Oct 20, 2018
Messages
3,476
"=UpdatedBy(" & ctl.Name & ")"
Maybe "=UpdatedBy('" & ctl.Name & "')"
You're passing a string but you're not enclosing it in quotes. Also, maybe controls is the default collection of Me and that will work - I don't know. My habit is always
Me.Controls. M$ has a knack for updating Access in such a way that code that works stops working because of code that is less than perfectly written.
 

cheekybuddha

AWF VIP
Local time
Today, 13:12
Joined
Jul 21, 2014
Messages
2,237
I think Micron's on the money.

Try:
Code:
' ...
      ctl.AfterUpdate = "=UpdatedBy(" & Chr(34) & ctl.Name & Chr(34) & ")"
' ...
 

Micron

AWF VIP
Local time
Today, 09:12
Joined
Oct 20, 2018
Messages
3,476
Single quotes that I entered won't work? It appears to be a concatenated value as part of the syntax of a reference (e.g. Me.Controls("nameHere") so I don't know?
 

cheekybuddha

AWF VIP
Local time
Today, 13:12
Joined
Jul 21, 2014
Messages
2,237
It might have worked - I don't know.

It's the equivalent of entering a function call in the event property in the events tab of the properties sheet. (where you would more normally see [Event Procedure])
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:12
Joined
May 7, 2009
Messages
19,169
you make sure that the corresponding "X" field of the checkbox is In the Form and only hidden (Visible=No).
Code:
...
...
For Each ctl In Me.Controls
    If TypeOf ctl Is CheckBox Then
        ctl.AfterUpdate = "=UpdatedBy()"
    End If
Next

End Sub

Public Function UpdatedBy()
    Dim tbxControlSource As String
    Dim ctl As Control
    tbxControlSource = Me(Screen.ActiveControl.Name).ControlSource & "X"
    For Each ctl In Me.Controls
        If TypeOf ctl Is textbox Then
            If ctl.ControlSource = tbxControlSource Then
                ctl.value = TempVars("UserName").value
                Exit For
            End If
        End If
    Next
End Function
 

mamradzelvy

Member
Local time
Today, 14:12
Joined
Apr 14, 2020
Messages
145
@cheekybuddha thanks once more, the new code now does exactly what i had imagined!!

I just stumbled upon a new "error" message, which i have not encountered yet:
adamerror2.PNG


For clarification: i got a button that updates the selected record's boolean status field, does a timestamp of the time it's been "finished" and also logs the name of the person doing it.
So this write conflict happens when i click any of the check boxes which i have now Cheeky's code for to log the tempvars name into a diferent field and then click my button in the same instance. When i select "save record" on this here window, it does in fact not save any of the actions associated with my button.

This here is the code behind the button:
Code:
Private Sub btnFinalise_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE dbServis SET SerStatus = '-1' WHERE ID LIKE '" & Me!txtID.Value & "';"
DoCmd.RunSQL "UPDATE dbServis SET SerDatum2 = Now() WHERE ID LIKE '" & Me!txtID.Value & "';"
DoCmd.RunSQL "UPDATE dbServis Set SerFinishedBy = '" & TempVars("UserName").Value & "' WHERE ID LIKE '" & Me!txtID.Value & "';"
DoCmd.SetWarnings True
End Sub

Is this a common problem when using standalone sql commands on a bound form?

(It does work when i open the window again and only use my button, i'm just confused as of why there's this conflict)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:12
Joined
May 7, 2009
Messages
19,169
thats the common Error programmers do.
the Record is on the the Form in front of you and you are using SQL Update to update same Record.
The form has a Memory copy of un-altered record.
use the Form's Recordset to update the underlying record:

Code:
Private Sub btnFinalise_Click()
With Me.RecordsetClone
    .FindFirst "ID = " & Me!txtID
    If Not .NoMatch Then
        .Edit
        !SerStatus = -1
        !SerDatum2 = Now
        !SerFinishedBy = Tempvars("UserName").Value
        .Update
    End If
End With
End Sub
 

Users who are viewing this thread

Top Bottom