Updating a table when a form checkbox is checked with the current date (1 Viewer)

randomtangent

New member
Local time
Today, 15:47
Joined
Dec 5, 2019
Messages
5
Hi, I have a form that has checkboxes for the different ways a customer uses our services and I would like to have it update a field with the current date when it is checked. That way we can later run reports showing that a customer found us one way and then started using another service of ours 3 months later or something. I have the checkboxes and the date fields in the customer table. I have the checkboxes on a form linked to a different table (for orders) and I understand I need to use VBA on the "onclick" event for the checkbox on the form. I just don't know what the VBA code should say. I have only ever used VBA by googling what I need to do and then pasting code :)
Hopefully this makes sense. Thanks for the help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
21,358
Hi. Yes, you can use VBA for this, but I am not yet sure if you should be doing it. For example, I don't know if the checkboxes for your services should be in the customers table? Are you saying you have a few yes/no fields in the customer table? If so, what are their names?
 

Micron

AWF VIP
Local time
Today, 18:47
Joined
Oct 20, 2018
Messages
3,476
Not sure you have taken the right approach. A combo may be more suitable as a way for user to make a selection, unless they need to be able to make several selections at one time or another. Also sounds like the checks don't belong in the customer table; that table would be customer related data and not a set of options they've been making from time to time. Nor does it sound like the date fields belong there either. One or two maybe to show when they became or ceased to become a customer, but not for storing several other dates related to their activity or choices.

You might want to upload a pic of your relationships if you've established them, or more info about your tables. To continue on your path anyway, the event to use would be the AfterUpdate event of a checkbox to write a date (Date() ) to a table field. However, with checks you usually need to cover both situations - if user checks, then later unchecks.
 

randomtangent

New member
Local time
Today, 15:47
Joined
Dec 5, 2019
Messages
5
Hi, the owner wants to know for instance if an eBay customer later uses our Signature Services. So when the customer is originally entered in the customer table it stores the date and it will check the appropriate box, say "eBay". Later if that same customer places an order to have a comic book signed at a convention during the order entry process the owner would like the "Signature" box on the customer table checked and a date added to the "SignatureDate" field without the person entering the order having to go back into the Customer form. That way later he can see the number of eBay customers who later use our other services. If there is a better way to do this, that is fine.

Originally it was just check boxes for the different ways the customer uses our services but now he would like to know what date they first used a new service.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
21,358
Hi. Yes, I think that is the wrong way to design this. So, how do you think the query would look like using the current table structure? I think it would be harder to get answers from your database this way. It's okay to have dates associated with each service, but each service and date should be a record in a child table rather than columns in the customers table.
 

Micron

AWF VIP
Local time
Today, 18:47
Joined
Oct 20, 2018
Messages
3,476
Definitely concur; as I said, it didn't sound right. You need to do what I refer to as an Entity Attribute analysis (some might not like that EA tag). Study normalization and decide what the entities are (a thing or activity for example) that become tables. Attributes become the fields. CustName would be an attribute of Customer. Their activities (in this case anyway) are NOT attributes of the customer. That doesn't mean that every activity requires a table. You might group them in a table as ActivityTypes. The point is, you need to understand normalization and come up with a schema that follows it as much as is practical while making it possible to work with data as easily as possible. It's not an easy task.
 

randomtangent

New member
Local time
Today, 15:47
Joined
Dec 5, 2019
Messages
5
So I should make a customer activity table linked to the customer table to store the activity and dates? I can do that.

So instead of the checkboxes being from the customer table and on the order form they will be on the customer activity table and on the order form, right? The point is that if a user is entering 150 orders they should not also have to go to a different form and enter the activity info.

I can make the new table with the fields located there. I can put the checkboxes on the form. I just don't know the VBA code to fill in the date once the box is checked. I would also like to clear the date field if the user unchecks the box, since someone might check the wrong box by mistake.

Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
21,358
Hi. Just in case it helps, in the AfterUpdate event of the checkbox, you could try something like:
Code:
If Me.CheckboxName=True Then
   Me.DateFieldName = Date()
Else
   Me.DateFieldName = Null
End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
21,358
Awesome, thanks for all the help!
Hi. You're welcome. We're all happy to assist. Good luck with your project. Please let us know if you get stuck normalizing your table structure.
 

Micron

AWF VIP
Local time
Today, 18:47
Joined
Oct 20, 2018
Messages
3,476
they will be on the customer activity table and on the order form, right?
That still sounds like duplication
The point is that if a user is entering 150 orders they should not also have to go to a different form and enter the activity info.
I can't say definitively what I'd do because I haven't grasped what they're doing. Usually a form/subform arrangement takes care of this sort of thing; the main being the one, the sub being the many. Is Activity the main because one activity can generate 150 orders? You don't have to answer that. What you do need to answer is your own questions pertaining to the process and how a properly normalized schema supports that. If you haven't studied normalization yet (and have at least a rudimentary understanding) then that's where you need to go next.
 

Users who are viewing this thread

Top Bottom