Checkbox that updates 2 tables

[BlueStarBrite]

Ok, I have another newbie checkbox ?

I have a checkbox that once its clicked or unclicked, I want to do 2 things.

For Table #1: I want to update the Yes/No Field and text to flash to a label (This part I know how to do)

For Table #2: I want to insert the date EACH time the checkbox is checked or unchecked. I know I would just use chkbox1 = now() or date()

My question is: How do I get these actions to occur simultaneously?

I'm not familiar with update queries, but I'm thinking this is what I would need. If so, could someone point me in the direction of update queries for newbies or explain in detail exactly how they work in simple terms.

Thanks in advance!

 

[gemma-the-husky]

For Table #2: I want to insert the date EACH time the checkbox is checked or unchecked. I know I would just use chkbox1 = now() or date()

this is not so easy

your chkbox is just true or fasle, so in the afterupdate event for the chkbox you need to say efectively

"updatethedate in table 2" , which we can come back to

However - what date/data are you storing in table 2 - if you are only storing 1 date, then all you can store is the last time it was changed. If you want to store all the times it was ever changed, then you need a more complex beast.

You could either use a memo field which would let you save 65000 characters, therefore many changes, but effectively just as an ote to the record - otherwise you would have to store the change history in a different table - ie a subtable of table2 - So can you clarify what you want to achieve.

 

[BlueStarBrite]

this is not so easy

your chkbox is just true or fasle, so in the afterupdate event for the chkbox you need to say efectively

"updatethedate in table 2" , which we can come back to

However - what date/data are you storing in table 2 - if you are only storing 1 date, then all you can store is the last time it was changed. If you want to store all the times it was ever changed, then you need a more complex beast.

You could either use a memo field which would let you save 65000 characters, therefore many changes, but effectively just as an ote to the record - otherwise you would have to store the change history in a different table - ie a subtable of table2 - So can you clarify what you want to achieve.

In Table 2 I just want to store a clientID and a DateChecked/Unchecked Date.

Ex: Client123 12/01/1999 03:22:00PM
Client123 01/15/2000 04:25:00PM
Client234 03/01/2006 07:51:52AM

I need to do it this way b/c I eventually will need to create a report that calculates the time between when the checkbox was checked and when it was unchecked. Basically the checkbox is an Active vs Inactive. Does that make any sense?

Thanks

 

[Thinh]

i suggested you create a another table to use as a log of user action that looks like this. then you create append query to append the the new records on click event of the chkbox.

Pseudo code
Private sub chkbox_onClick()
docmd.runsql "Insert into log (ClientID,TimeStamp, Action) values ('' & me.clientID & "','" & now() & "','" & me.chkbox & "'")"
end sub

ClientID, TimeStamp, Action
123 2/20/2000 -1

 

[BlueStarBrite]

i suggested you create a another table to use as a log of user action that looks like this. then you create append query to append the the new records on click event of the chkbox.

Pseudo code
Private sub chkbox_onClick()
docmd.runsql "Insert into log (ClientID,TimeStamp, Action) values ('' & me.clientID & "','" & now() & "','" & me.chkbox & "'")"
end sub

ClientID, TimeStamp, Action
123 2/20/2000 -1

Yes, this is essentially what I want my Table2 to be. I will give this method a try and let you know how it goes. Thanks!

 

[BlueStarBrite]

Yes, this is essentially what I want my Table2 to be. I will give this method a try and let you know how it goes. Thanks!

Ok, I could not get it to work. I keep getting an error message:

Run-time error '3075' Syntax error (missing operator) in query expression " '&me.ClientID&'

I looked up the error, but cannot figure out what's wrong. Any suggestions?