how to run code in Current record ? - Page 2

oMADMANo · #16 (**permalink**) 02-01-12, 01:46

The objects are part of a form which does uses single form view. I am trying to apply the code to the current record only to make the objects visible when the tick box is selected on that record. They are not stored values in my table.

Sinndho · #17 (**permalink**) 02-01-12, 02:45

Missinglinq formerly provided the code needed to do what you want:

Code:

Private Sub RiskAssesment_AfterUpdate()
   If Me.RiskAssesment.Value = -1 Then
     Me.Risk.Visible = True
   Else
     Me.Risk.Visible = False
   End If
End Sub

You answered that "the code does not work", which is not very helpful. What kind of problem (error, unexpected result, ...) did you encouter when trying to use it?

oMADMANo · #18 (**permalink**) 02-01-12, 03:41

when I used that code when I select or de-select the tick box it does nothing (including no error). the tick box has a yes/no property and default is zero so I tried using the value of 1 and 2 but still didn't work. I think that code could
work but something must be wrong.

Sinndho · #19 (**permalink**) 02-01-12, 04:50

Please check that the code is effectively linked to the event. In Design view, select the control "RiskAssesment", open the Properties window , select the Event tab and verify that the "After Update" line displays "[Event Procedure]" (see attachment)

oMADMANo · #20 (**permalink**) 02-01-12, 06:20

Yeh its all linked up. I have tried after update and on click with them both in properties (one at a time) I still got no result when I clicked the tick box.

oMADMANo · #21 (**permalink**) 02-02-12, 04:56

please can you lot share some more advice on this I am still stuck

Sinndho · #22 (**permalink**) 02-02-12, 05:36

Quote:

Originally Posted by oMADMANo

when I used that code when I select or de-select the tick box it does nothing (including no error). the tick box has a yes/no property and default is zero so I tried using the value of 1 and 2 but still didn't work. I think that code could work but something must be wrong.

A checkBox in Access (what you name a "tick box") can only have two values: True (= -1) when it is "ticked" (i.e. a check mark is visible) and False (= 0) when it is not "ticked". It can also be Null (which is not a value) when it is greyed. Testing for any other values is meaningless, although any non-zero value can be considered as True.

Please post an exact copy of the code for the Sub procedure "RiskAssesment_AfterUpdate()" as it is in your database.

healdem · #23 (**permalink**) 02-02-12, 06:20

for the sake of pedantry
you shouldn't test check boxes with numeric values. in an ideal world you should test with the intrinsic vb(s) constants
vbtrue
vbfalse
why?
you don't know if Microsoft will change the way boolean values are represented in future
similarly you don't know if other db vendors will change the way they store boolean values. strictly speaking Access is a front end user interface tool, not a database per se, the defautl dataabase in Access is JET but through various reasons not all of 'em good many people assume that Access = JET = Access.
you can test for NULL by using the function isnull()

oMADMANo · #24 (**permalink**) 02-02-12, 14:10

okay i have took your advice to use vbfalse / vbtrue but i don't think i fully understand how to use them. i have tried this

Code:

Private Sub RiskAssesment_AfterUpdate()

 Set db = CurrentDb
 Set rs = db.OpenRecordset("Membership Database", dbOpenDynaset)
    
   
   
   If vbFalse(rs![RiskAssesment]) Then
     Me.Risk.Visible = False
       
   ElseIf vbTrue(rs![RiskAssesment]) Then
     Me.Risk.Visible = True
    
   End If

End Sub

but i get error saying "Expected array"
Please help.

Sinndho · #25 (**permalink**) 02-02-12, 16:10

1. vbFalse and vbTrue are constants, not functions. You use them in comparison expressions such as:

Code:

If MyVariable = vbTrue Then

or:

Code:

If Me.SomeControl.Value = vbFalse Then

2. Why do you try to open a Recordset to retrieve a value from its RiskAssesment field ? Moreover, the value you get is to one of the first row in the Recordset, not the row corresponding to the current record of the form.

Sam Landy · #26 (**permalink**) 02-02-12, 16:59

Code:

   If vbFalse(rs![RiskAssesment]) Then
     Me.Risk.Visible = False
       
   ElseIf vbTrue(rs![RiskAssesment]) Then
     Me.Risk.Visible = True
    
   End If

Why don't you simply say

Code: