Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have this great form for my contracts with all the fields in the world on it. This is great for use after the contract is initially entered for viewing or even editing by an administrator but for initial entry there are a half dozen or so fields I'd like to hide. I can make a copy of the form and hide or remove the fields manually, but there must be a way to use one form and programmatically change the properties of the fields I want to hide. I have searched for every combination of words I can think of but I'm not finding what I need.
    I have separate buttons on my switchboard for "Add a new contract", "Edit a contract that is pending", "View all contracts", and "Edit anything (for admin use only)". I'd like to do all this with one form. I can specify (in the switchboard) macros that I built as macro objects to handle the filtering, opening, and views but I'd like to be able to add code to hide the fields I don't need for the first two buttons (I'm assuming that this would only affect the instance of the form while it is open and not change the base form permanently). I had assumed these macros were subroutines in a module somewhere that I could add to but I can't find them.
    Can you please point me in the right direction? Am I on the right track or have I missed something big?
    I thought about using the "On open" event for the form but how would I know which button of the switchboard invoked it?
    Thanks for any hints you can give me.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is some code I have used to d something similar. I put this in the On Current event of the form.

    I use the Tag property of controls to indicate the ones I want hidden. In this case I put the word Hide in this property.

    This code assumes you always want them hidden when you are adding a new record.

    Dim ctrl As Control
    For Each ctrl In Me.Controls
    If ctrl.Tag = "Hide" Then
    ctrl.Visible = Not Me.NewRecord
    End If
    Next ctrl
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='VBA-Bumbler' post='770541' date='15-Apr-2009 00:44']I thought about using the "On open" event for the form but how would I know which button of the switchboard invoked it?[/quote]
    Do you use Docmd.OpenForm to open your forms ? Then you can use the OpenArgs argument to pass a parameter different for each button. And like you say, in the On Open or On Load Event you can make some controls visible or not, depending on the passed argument.
    Francois

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    John,
    I really like the Tag idea! Thanks for the code. Now I need to figure out where to put it!

    Francois,
    Since the open is handled by the macro object I built in design view for the switchboard I didn't need to specify "DoCmd.", I just picked "OpenForm" from the drop down. Unfortunately, there is no way to enter the "OpenArg" parameter this way. Based on those two facts, I think you are telling me I should have code in a module to open the form and pass the arguments. Is there a way to specify a subroutine from a module using the Switchboard Manager Database Utility (only macro objects are showing up for me, not subroutines in modules) or do I need to figure out where to put this in the HandleButtonClick function of the Switchboard code?

    Thank you both for your help!

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='VBA-Bumbler' post='770630' date='15-Apr-2009 17:06']Since the open is handled by the macro object I built in design view for the switchboard I didn't need to specify "DoCmd.", I just picked "OpenForm" from the drop down. Unfortunately, there is no way to enter the "OpenArg" parameter this way. Based on those two facts, I think you are telling me I should have code in a module to open the form and pass the arguments. Is there a way to specify a subroutine from a module using the Switchboard Manager Database Utility (only macro objects are showing up for me, not subroutines in modules) or do I need to figure out where to put this in the HandleButtonClick function of the Switchboard code?[/quote]
    I don't think you will be able to add a OpenArgs with a switchboard build by Switchboard Manager Database Utility.
    I was thinking of a self build switchboard with command buttons and VBA code, like in the sample in the attachment.

    [attachment=83344atabase1.zip]
    Attached Files Attached Files
    Francois

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK, thanks Francois. By the way, I wasn't able to open the attachment. It has an extension of accdb. I tried changing it to mdb but that didn't work either. No Problem though, I think I know what you mean.

    Maybe I'll try adding to the switchboard code. Maybe I can store the IntBtn parameter in the tag of the Switchboard form at the top of the handleButtonClick function. I can see that it remains open while the other form is opened. The On Open code of the contract form should be able to see it, I think.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='VBA-Bumbler' post='770640' date='15-Apr-2009 18:04']By the way, I wasn't able to open the attachment. It has an extension of accdb. I tried changing it to mdb but that didn't work either.[/quote]

    Sorry, it was a Access 2007 format, here you have a access 2000 mdb.
    [attachment=83345b1.zip]
    Attached Files Attached Files
    Francois

  8. #8
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Francois. I see what you mean. That is pretty clean.
    I added a line in the Switchboard code to store the button number in the tag and then coded up the OnOpen code for the contract form. It is working fine but I'm a little nervous about that change to the switchboard code. I may end up going your route later.
    Thanks for the help.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='VBA-Bumbler' post='770630' date='16-Apr-2009 03:06']John,
    I really like the Tag idea! Thanks for the code. Now I need to figure out where to put it![/quote]

    My code goes in the On Current event of the form, as I said in the previous post.
    Regards
    John



  10. #10
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks John.

Posting Permissions

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