how to run code in Current record ?

oMADMANo · #1 (**permalink**) 01-29-12, 03:22

Okay i have a tick box called "Riskassessment" and on it being clicked I would like some items to be visible on that record. I have set a control source for this tick box to yes/no option in my Membership_Database and I am trying to make this code:

Code:

Private Sub RiskAssesment_Click()

 If rs!RiskAssesment = False Then

rs!Risk.Visible = False


 ElseIf rs!RiskAssesment = True Then

rs!Risk.Visible = True

only run on current record. also applied this to my form current sub.
Thanks.

Missinglinq · #2 (**permalink**) 01-29-12, 11:28

Your use of rs in rs!RiskAssesment would suggest you're copping code from somewhere that uses a RecordSet, which really isn't appropriate here. This is the code you need to accomplish your goal:

Code:

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

Notice that I have RiskAssesment written with one s. This is misspelled, in English, but I left it as it is given in your example. The important thing is that it is spelled the same way everywhere!

Linq ;0)>

oMADMANo · #3 (**permalink**) 01-29-12, 13:51

The code you provided does now does not work

i have also tried

Code:

Private Sub RiskAssesment_Click()

 Set db = CurrentDb
 Set rs = db.OpenRecordset("tblMembership_Database", dbOpenDynaset)

 If rs.RiskAssesment = False Then


rs.Risk.Visible = False
rs.Risk1.Visible = False
rs.RiskText.Visible = False
rs.Risk1text.Visible = False
rs.RiskButton.Visible = False

 ElseIf rs.RiskAssesment = True Then

rs.Risk.Visible = True
rs.Risk1.Visible = True
rs.RiskText.Visible = True
rs.Risk1text.Visible = True
rs.RiskButton.Visible = True
rs.Update

End If

End Sub

with the name of my table being Membership Database.
It gives me the error cant find table/query Membership database.

Sam Landy · #4 (**permalink**) 01-30-12, 12:22

Mind if I comment?

Remove the code from the Sub RiskAssesment_Click() event altogether. The only code you need is in the Form_Current() event. Add the code there as follows:

Code:

rs!Risk.Visible = Me.RiskAssessment

As Missinglinq pointed out, make sure you spell it consistently. Of course you have to open the table there in order to access the data. You also need the same code in the Sub RiskAssesment_Update() event.

However, I'm confused about something altogether different. Your VBA code is accessing rs!RiskAssesment, etc., but there's no code pointing to a record. Your cursor is still at BOF() (beginning of file), and is not pointing to any data at all.

Also, the code

Code:

If rs.RiskAssesment = False Then


rs.Risk.Visible = False
rs.Risk1.Visible = False
rs.RiskText.Visible = False
rs.Risk1text.Visible = False
rs.RiskButton.Visible = False

 ElseIf rs.RiskAssesment = True Then

rs.Risk.Visible = True
rs.Risk1.Visible = True
rs.RiskText.Visible = True
rs.Risk1text.Visible = True
rs.RiskButton.Visible = True
rs.Update

is almost completely redundant. All you need is

Code:

rs.Edit (which you also don't have anywhere in the presented code snippet)
rs!Risk.Visible = rs!RiskAssesment
rs!Risk1.Visible = rs!RiskAssesment
rs!RiskText.Visible = rs!RiskAssesment
rs!Risk1text.Visible = rs!RiskAssesment
rs!RiskButton.Visible = rs!RiskAssesment
rs.Update

Dataset fields are delineated by "!", not ".", so you should have, for example,

Code:

rs!Risk.Visible = rs!RiskAssesment

"." is used to delineate methods (such as ".Update") or properties (such as ".Visible"), not field names.

Also, the whole snippet looks suspicious to me. Are using "rs." to refer to the form, and not the dataset? The form object is identified by "Me", not by the table object's name.

By the way, what is the real name of the table? Using reserved words such as "Database" as part of an object's name is just begging for trouble. Access has its own meaning for those words, and couldn't care less how the programmer means it.

Good luck,

Sam

healdem · #5 (**permalink**) 01-30-12, 12:54

if you put the code in the on current event I thought that only triggered when a new record is displayed, either an existing one that is being edited or a new one just added granted iof you want to display that information on histroic records then thats a good solution

however if a user clicks a button to toggle the display on or off then you need code behind that button

Sam Landy · #6 (**permalink**) 01-30-12, 16:35

Both true, and I based my answer on what looks to me like a historical table, not where he's adding records.

Sam

oMADMANo · #7 (**permalink**) 01-31-12, 04:21

The problem I am having now is the risk , risk1 , risktext , risk1text , risk button are not data fields in my "membership database" they are controls on my form.
so I am still having trouble with this.

Sinndho · #8 (**permalink**) 01-31-12, 05:00

Then use expressions such as:

Code:

Me.Risk.Visible = Me.RiskAssessment.Value

oMADMANo · #9 (**permalink**) 01-31-12, 11:14

I have tried the Me. expression but it applies the code to all records. I only want to make the objects visible on the current record if the tick box is selected.