Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jan 2012
    Posts
    16

    Unanswered: how to run code in Current record ?

    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.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2012
    Posts
    16
    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.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Both true, and I based my answer on what looks to me like a historical table, not where he's adding records.

    Sam

  7. #7
    Join Date
    Jan 2012
    Posts
    16
    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.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Then use expressions such as:
    Code:
    Me.Risk.Visible = Me.RiskAssessment.Value
    Have a nice day!

  9. #9
    Join Date
    Jan 2012
    Posts
    16
    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.

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I always default the display to a Single Record. That solves problems such as this.

    Sam

  11. #11
    Join Date
    Jan 2012
    Posts
    16
    Could you possibly explain how you do that please.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select the forms properties
    switch from continuous forms to single form
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jan 2012
    Posts
    16
    If you mean default view as single form this doesn't work the objects are still visible on all records.

  14. #14
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Are these objects part of the form, or are they actually stored in the record in the table?

    Sam

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    my reading is the OP is using continuous forms and trying to apply logic to a single row, which as you correctly point out / infer cannot be done.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •