Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Continuous form - making a button visible/invisible

    I knew how to do this once but I'm having a brain-freeze and hoping someone can help.

    I have a continuous form (which has a true/false field in the underlying table called 'ShowButton' for each record.)

    I can't recall on the continous form though on how I made it so my 'Edit Data' button is visible or invisible depending on if the 'showbutton' value is true or false for that record.

    Could someone refresh me on how to make a button visible/invisible (again, on a continous form) depending on a true/false field value for that record?

    I'd rather not create a textbox and then format the conditional formatting so the text box is enabled/disabled depending on the true/false field (which works) - I just would rather have an actual button versus a textbox.

    I once had some code (which I can't seem to locate) which allowed me to do this (ie. make buttons on a continous form visible or invisible for the specific record depending on the value of another field) (for example: the first record on the continous form has false for the 'showbutton' value so the button is not shown for that record but the 2nd record on the continous form has true for the 'showbutton' value so the button then shows for that record on the continous form.

    Can anyone recall how this is done? I know it's possible as I've done it in the past. I just can't recall how I did it.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    try this in the OnCurrent event of the form;

    me.commandbutton.visible=false 'default state
    if me.OtherControl = X then
    me.commandbutton.visible=true
    end if

    if the data is text it would be
    if me.OtherControl like "X" then

    but I know that you know this - let me know if this does not work - I'll dig around further. the key as I remember w/ continuous forms is to remember to return it to its default state as otherwise it is all on or all off in the form...but am working from a memory that is faulty and perhaps that code needs to go elsewhere....let me know....

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the reply NTC. Yes - I tried the code you supplied in the OnCurrent event of the continous form without success.

    ie.

    Private Sub Form_Current()
    If Me!CollectsData = True Then Me.cmdEditData.Visible = True Else Me.cmdEditData.Visible = False
    End Sub
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    Me!CollectsData = True

    ??

    is this a typo ?

  5. #5
    Join Date
    Sep 2006
    Posts
    265
    I never managed to get images to work on a continuous form and a similar problem. The continuous form has a Header you could manage the control cmdEditData.Visible or Invisible up in the Header.

    Simon

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by NTC View Post
    Me!CollectsData = True

    ??

    is this a typo ?
    No. I always use the ! (since it represents the field value) versus . (which represents the control itself). (CollectsData is my fieldname/controlname.)

    I will sometimes use me.collectsdata.value (or Forms!MyFormName!CollectsData if referencing outside the form)

    I notice some developers these days will use: me.collectsdata which in the older MSAccess versions, was problematic for returning field values and automatically distinguishing whether the code is referencing the field value or control itself. Newer versions of MSAccess apparently parse this type of coding correctly to the field value (usually) but there are possibilities of problems depending on the code. I've even seen coding where apparently just: CollectsData = True seemed to work. I avoid this as it's difficult to distinguish between fields and variables used. I've also seen: CollectsData.value = True.

    I've always understood:

    me!FieldName <= value in the field (which older MSAccess versions required)
    me.FieldName.Value <= value in the field (same as above)
    me.FieldName <= references the field (control) itself (but newer MSAccess versions distinguish correctly the use)

    What's oddly funny is that Microsoft was never able to get the auto-fill to work when coding using the ! but it autofills using the . (where I'm guessing they are encouraging the use of me.fieldname.value).
    Last edited by pkstormy; 03-24-10 at 19:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2009
    Posts
    340
    well pop it into single form mode temporarily - and sanity check that this code works fundamentally.... then at least we are certain it is the continuous form - if so it means it is just toggling on/off so quick as to be unseen and probably left in the state of the last record...

    am having trouble digging up my past example;

    but alternatively in design view right click on cmdEditData and use the Conditional Formating feature - probably not your ultimate solution but might be a temp work around - maybe make font color same as background so it wouldn't be seen....

    let me know and tomorrow will check something on another computer.....

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Conditional formatting isn't allowed for a button on a form (at least not in 2003). That would be too simple. I can do conditional formatting on a text box but a text box doesn't look 'exactly' like a button regardless of how you design it.
    Last edited by pkstormy; 03-24-10 at 20:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That correct, and Conditional Formatting doesn't allow controlling of visibilty/invisibility. I've been kind of waiting to see if to see if anyone else would mention this. The fact is that Command Buttons are Unbound and Unbound objects on Continuous Forms cannot be formatted on a record by record basis! The closest you can come to doing this in Access is to use a Textbox and its OnClick event instead of the button, making it's backcolor the same as the form to make it "invisible," which really isn't satisfactory, in my opinion.

    You can use code in the Form_Current event to control the button's Enabled property, and while its appearance on all records will reflect the conditional state of the current record, its functionality will be correct for the current record.

    A workaround for this, leaving the button "normal" looking on all records, would be to have, in the Form_Current event, conditional code for the button

    Code:
    If Me!CollectsData = True Then 
      Have the button do something here
    Else
      Don't have the button do anything here
    End If
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I can manipulate unbound text boxes on the continuous form (using the conditional formatting) so that they are disabled (or the text is blank) and then make them look 'similar' to buttons. This works for continous forms (ie. I use the CollectsData value to manipulate the conditional formatting and also have the textbox open/not open another form depending on the CollectsData value). I can do the same thing with buttons - making it open/not open another form if CollectsData is true/false.

    I just can't make the button visible/invisible for the specific record if the CollectsData value is true/false.

    I once knew a work-around way though that I could make an actual button visible/invisible on a continous form depending on another value. I just can't recall how I did it.
    Last edited by pkstormy; 03-25-10 at 07:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    After typing my last response, I remember some of how I did this.

    Using an unbound (or maybe it was bound) text box on the form (on top of the button) (or a combination of 2 unbound text boxes), I think I can use the conditional formatting of the text box(s) to 'see through' (or not see through) to the command button. It was something like that - I can't recall exactly.

    I'll play around with the design.
    Last edited by pkstormy; 03-25-10 at 07:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That sounds about right, Paul. I think it would have to be bound to work. It's a shame that they don't allow CF on all objects, but you can't have everything, I guess!

    Linq
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes Linq. I'm getting close. I know this can be done somehow as I've done it in the past. (plus I also saw an Acccess application recently which did the same thing (but it unfortunately was an mde so I couldn't see the code.)

    If I figure it out, I'll post an example here and in the code bank.
    Last edited by pkstormy; 03-25-10 at 09:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Oct 2009
    Posts
    340
    here is a snippet of Allen Browne code using boolean that is worth a try I think:

    from Allen's code:
    > > Private Sub Form_Current()
    > > Dim bShow As Boolean
    > > If Me.Combo1 = "X" Then
    > > bShow = True
    > > End If
    > > If Me.TxtBox.Visible <> bShow Then
    >>>>>>> me.combobox.setfocus
    > > Me.TxtBox.Visible = bShow
    > > End If
    > > End Sub

    Obviously in the OnCurrent event - and of course you know that you can ignore the setfocus stuff - but the idea might work.....searched thru my informal library of 'code to remember' on an older PC and this is the closest I could find.....plz post your results...

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Well it's been about 5 years since I posted this question. I'm now using MSAccess 2010/2013. There still isn't a way you can make a BUTTON visible/invisible on a CONTINUOUS form based on logic (ie. a True/False value in a field.)

    You CAN however fake it out and make a Text Control (with wording in the data field such as "Click Here") and then use CONDITIONAL FORMATTING to make the Foreground/Background colors match the background color (thus "faking" it out and make the Text Control LOOK like a button.)

    After 5 years and re-reading the posts, there were a lot of answers to designing the form differently (ie. make it a single form) or answers way off the topic, but there still isn't a true solution to this problem! There may be some API code somewhere to do this but I haven't really come across any API code (nor do I wish to use any API coding.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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