Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Question Unanswered: Command Button & Form

    Hi Everyone,

    I am a self taught Access User and have made several realativly small databases with it. I have one that I am having trouble with.

    The form opens in Datasheet view and is very long. I was hoping to be able to break out different sections into 3 seperate forms. These 3 seperate forms would all be based on fields from the same table.

    I've tried using a Command Button with an Open Form Operation, and "Open form and find specific data to display". However, this does not seem to work well in the same table particularly while the Record is still being added to the table.

    Is there something I am missing on how to get several forms to open with a button, but show the same record that is being added on the main form?

    Doing this would help my users greatly as the forms would be more concise and smaller, plus I could use a normal form latyout rather then Datasheet.

    Thank you.
    Brian

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you simply open your form in form view ?
    You can apply a filter to your form if you want it to display a subset of the contents of the table.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    Quote Originally Posted by Sinndho
    Why don't you simply open your form in form view ?
    You can apply a filter to your form if you want it to display a subset of the contents of the table.

    Have a nice day!
    Form View wont allow the number of fields, there are to many and the users didn't like it that way with all those fields.

    I don't know how to use filters or subsets. I am self taught.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To add a filter to a form, use this code:

    Code:
       Me.Filter = "SysCounter < 5"
        Me.FilterOn = True
    In this example the table on which the form is based has a column named SysCounter. With the code I specify that I want the form to display records with a value < 5 in the SysCounter column.

    You can dynamically change the filter everytime you want.

    If you want the form to show all records, simply use:
    Code:
    Me.FilterOn = False
    If you still want to have several forms open, you can create multiple instances of the same form and assign a different filter to each of them:

    1) First add this in the declaration section of a module:
    Code:
    Declare Function DestroyWindow Lib "user32" (ByVal hwnd As Long) As Long
    Public clnClient As New Collection  'Instances of frmClient.
    2) Create a function like this:
    Code:
    Function OpenFormInstance(FormCaption As String, FormFilter As String)
        '
        'Purpose:   Open an independent instance of Form <MyForm>.
        '
        Dim frm As Form
        Static x As Long
        Static y As Long
        '
        'Open a new instance, show it, and set a caption.
        '
        Set frm = New Form_<MyForm>
        frm.Filter = FormFilter
        frm.FilterOn = True
        frm.Visible = True
        frm.Caption = FormCaption
        '
        'Append it to our collection.
        '
        clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)
        Set frm = Nothing
        
    End Function
    Of Course you need a function to close all the open forms:
    Code:
    Function CloseAllClients()
    
        'Purpose: Close all instances in the clnClient collection.
        'Note: Leaves the copy opened directly from database window.
        
        Dim lngKt As Long
        Dim lngI As Long
        Dim hwnd As Long
        Dim Action As Long
        
        On Error Resume Next
        lngKt = clnClient.count
        For lngI = 1 To lngKt
            hwnd = CLng(clnClient(1).hwnd)
            Action = DestroyWindow(hwnd)
            clnClient.Remove 1
        Next
        
    End Function
    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Thanks for all that, but I have no idea how to use any of it. I am not that advanced with Access. I do everything the simple way and have no idea how to use SQL.

  6. #6
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379
    Quote Originally Posted by CHI Brian
    Thanks for all that, but I have no idea how to use any of it. I am not that advanced with Access. I do everything the simple way and have no idea how to use SQL.
    C,

    Try a subform, which you can design a specific query for. Since you are using the same table and want 2-3 views of the same table with diffent filters, copy both the subform and the query and change the subsequent queries to filter the way you want for the secondary subforms, assigning there recordsource to the correct query.

    A subform can be sized for using scrollbars, so your user will not see too much data and you can work with the user to get the size right.

    BTW: Just for your reference, S did not give you any SQL, just VBA, which is the code that ACCESS uses and generates automatically. To see it open your form in the design mode and slowly run your cursor accross the options in your toolbar menu. As you do one of the items in the tool bar will display the annotation or help text of "Code". Click on that an the code window will open and you will see what S was trying to get you to do. You can then try, in a second copy of your form (backup copy) to add his code with cut-n-paste and see how it runs for you.

    Another way to access the code window in declare an event in your properties popup, which you get with "right click"+"properties" on any object in your form or the form itself. I suggest you start by creating the event for "On Click" for your button.

    DBS4M
    Last edited by dbsupport4me; 03-26-09 at 10:04.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You need to save the record on one of your forms before you can ever see it in any other form.

    I wouldn't do it that way though, I would have one form with the fields spread over multiple pages of a tab control.

    By the sound of it you should probably lash out a little and get yourself some training so that you can cope with instructions from forums and generally make life easier for yourself.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Mar 2009
    Posts
    120

    Smile

    Quote Originally Posted by StarTrekker
    You need to save the record on one of your forms before you can ever see it in any other form.

    I wouldn't do it that way though, I would have one form with the fields spread over multiple pages of a tab control.

    By the sound of it you should probably lash out a little and get yourself some training so that you can cope with instructions from forums and generally make life easier for yourself.
    Saving the record is what I thought would need to happen the way I was doing it. Thank you.

    Not sure what a Tab Control is, but sure its a bit more advanced then I am right now, like I said am self taught.

    I've tried to get some more advanced training but sadly, where I work is in a crunch with cash right now so it isn't that important, plus its not somthing widly used within the facility.

    I'll get there eventually. Thank you all for the assistance. Its appreciated.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Stop saying that you are "self taught" as an excuse!! I am self taught as well!! With the right resources, you can teach yourself anything -- and usually faster than structured training courses. However, structured training courses tend to get done whereas the self taught often need to "get a round tuit"...

    Anyway, tab controls are easy. It's just another tool in the toolbox. Give it a go. Create a tab control and then cut and paste objects into the pages. Be brave, but do it on a copy of a form first
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Some Help?

    Quote Originally Posted by CHI Brian
    Not sure what a Tab Control is, but sure its a bit more advanced then I am right now, like I said am self taught.
    Chi,

    First:
    Most users here are self taught, so do not put yourself down about that, just hang in there and watch the board and see what others are doing and you will learn. Remember the only dumb question is the one you did not ask.

    Next:
    Do you need to see an example of a TAB control, as in a thumbnail?

    Would an example of TAB controls help?

    DBS4M

  11. #11
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Closed ??

    Do we assume, since there is no action on this thread, it is closed?

    DBS4M

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Pretty much.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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