CHECK BOX PROBLEM 1

[desikan]

Hi, I just want to know if there is any way by which we can open a form and reset values of check box of all records to False.

I want to do this because I have another form which opens with a new record and I am having the afterupdate function run an append query to append the record just entered to another table. Now I have kept a check box on this form to locate the new record.

After the append action is completed, I want the check box to be made false.

Appreciate if any one can tell me whether this is possible?

Thanks in advance

 

[Frink]

Hallo,

You can use an Update Query to update a recordset.
Paste the following function into a module:

Function ysnRunSQL(ByVal pstrSQL As String) As Boolean
On Error GoTo Err_ysnRunSQL
  ' Comments  : Runs a SQL string action query
  '           : Note: SetWarnings is set to True by this function
  ' Parameter : strSQL - SQL string to execute
  ' Returns   : True if successful, False otherwise
  '
  DoCmd.SetWarnings False
  DoCmd.RunSQL pstrSQL, True
  ysnRunSQL = True
Exit_ysnRunSQL:
  DoCmd.SetWarnings True
  Exit Function
Err_ysnRunSQL:
  ysnRunSQL = False
  Resume Exit_ysnRunSQL
End Function

In your code put:

ysnRunSQL("UPDATE tblRecords SET ysnLatest=False WHERE ysnLatest=True")

You can check the return value if you like.


- Frink

 

[desikan]

Hi Frink,
Your solution was a miracle for me since I had somehow overlooked the power of the update query.

I was breaking my head over more complicated solutions like using primary index field or a separate ref no to be entered by the user and so on.

Thanks a million for your brilliant suggestion.

Star for you!!

 

[mph1]

You could also set the default value of each check box to 0 (zero)

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[desikan]

Hi Frink,
What I am doing is (as you have suggested) to keep the default value of check box to zero and then make it to True in the form with the new record and in the after update function run the append query using the Check box "high".

After the append query is completed I run the update query to make the check box back to zero so that when the new record is created again the check box "high" will point to the latest record entered only.

I have checked it and it is working fine thanks to your suggestions.