Unbound Checkbox and timestamp

[BlueJacket]

I think this should be relatively easy, but I still don't have a grasp of VBA/Macros enough know exactly how to do it myself. I want to be able to click a checkbox and then for the form to put in today's date in a related record.

For example: "Paperwork X Filed:" *clicks checkbox*, then in the "Paperwork X Filed Date" text box, today's date is automatically recorded. I don't want the checkbox itself bound to any table to be recorded.

I feel like a few If Then statements are all that is needed, right?

If "Paperwork X Filed Date" is null, then checkbox = null.

If "Paperwork X Filed Date" is not null, then disable checkbox.

On checkbox_click, if "Paperwork X Filed Date" is null, then "Paperwork X Filed Date" = now()

Of course, the actual names of the checkbox and text fields will not have any spaces in them and be properly named.

 

[pbaldy]

Does this help?

http://www.baldyweb.com/ConditionalVisibility.htm

 

[BlueJacket]

Yes, that does help. Thank you.

So I have

Private Sub chkMotPub_Click()

If Me.chkMotPub = True Then
    Me.txtPublicationMotionFiled = Now()

End Sub

Is this the correct code so the form checks to see if a date is already entered, so that when I change records or load the form, it doesn't start with an unchecked box?

If Me.txtPublicationMotionFiled = Null Then
    Me.chkMotPub = False
Else
    Me.chkMotPub = True

Would I put it on the form's On Load or On Current event?

I also don't want a new date to be entered if someone accidentally unchecks the box and then checks it again. If a date is entered, I don't want the checkbox to work anymore. Should I lock the checkbox if a date is already entered or can I make it so no matter how many times the person clicks the checkbox, it won't update if a date is already entered?

 

[pbaldy]

You can use IsNull() to test for Null; you can't use =. I'd use the form's current event. You can check for a date and set the Enabled property of the checkbox to No.

 

[BlueJacket]

Can I combine the two? For instance

Private Sub Form_Current()
        
    If IsNull(Me.txtPublicationMotionFiled) Then
        Me.chkMotPub = False
    Else
        Me.chkMotPub = True
        Me.chkMotPub.Enabled = False
    End If
    
End Sub

 

[BlueJacket]

That worked beautifully. Thank you for your help pbaldy. You helped me figure it out without just giving me the answer. I appreciate it.

 

[BlueJacket]

One more question. It doesn't seem like I could do this for the If Then statements that are click events, but if I wanted to do this for multiple checkboxes, is there a way to combine all those If Then or Else statements in the form's current event? Or do I have to make new statements for each checkbox? Just seems like there might be a cleaner way of doing it.

 

[pbaldy]

You mean the test behind each checkbox is the same, and includes other checkboxes? I'd create a form-level function and call it from each of the checkboxes, and the current event if appropriate.

 

[BlueJacket]

Each checkbox would correspond with a different record.

So, CheckboxA would record current date for FieldA
CheckboxB would record current date for FieldB
Checkbox C would record current date for FieldC, etc.

 

[pbaldy]

What do the fields represent? It sounds like you may have a normalization problem. In any case, if each checkbox only refers to itself, you'd have code behind each.

 

[BlueJacket]

Without going into too much detail, we are a law firm and investment company. Our investments mature over the course of three stages. The first stage requires research and acquiring a piece of land, then waiting for a certain length of time. The second requires just a little legal work. The third stage requires lots of legal work and then selling the property.

The fields represent different sets of legal paperwork that needs to be filed or recorded with the court. They serve to show us where we are in the process of that particular case and I hope to use them later on to be able to add dates to a calendar to remind the office staff that such and such needs to be sent out today.

 

[pbaldy]

I could make a case where the 3 stages would be 3 records in a related table rather than fields in this one. The concept is called normalization:

http://www.r937.com/Relational.html

If you ever require a 4th stage, you'll wish you had set up this way.

 

[BlueJacket]

I do actually have the stages separated out into different tables already. If anything, I think I've actually over normalized some of the tables, creating multiple tables with one-to-one relationships. For instance, I have a table (tblRedeemed) for when an investment gets redeemed, or bought from us, before it reaches full maturity. A property can only be redeemed once and a redemption can only encompass one property. In this table I have: PropertyID(PK), DateRedeemed, AmountReceived, RecordedDate. I think this makes sense to separate out for categorical reasons, but it would be something that I could put into the main table, since it would still be a 1-1 relationship.