Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010

    Unanswered: DoCmd OpenForm useage

    I just took over the DBA role for our company and it's been 10 years since I last worked with VBA code.. But, I have a Dbase that is tracking event reports. The form that opens has every different scenario available in text boxes piled on top of one another. There are 7 different events available in the first combo box and once a selection is made the code currently uses a Select Case routine that will either make visible or hide the text boxes associated with the selected case.

    My question is, can I create forms for every case and use the DoCmd to populate the open form with the form associted with the first event selected and have all that data put into the same table?

    I can post some fo the VBA code but it's 18 PAGES long!!! argh...


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Apart from opening a form is different modes (datasheet, normal, icon, add, edit, read-only, ...etc.), one of the easiest thing you can do if the form is based on a data set consists in providing a filter that will limit (filter) the rows of the data set that the form will display. Such a filter can be a query in the current database or, more simply, a Where Condition, i.e. a string containing the 'Where' part of a SQL statement but without the word 'Where'. Ex. "[InvoiceDate] > #1/1/2010#".

    You can also use the last optional parameter of the DoCmd.OpenForm method (OpenArgs) to pass anything you want to the form you open (anything but it must be a string). OpenArgs is a property of the opening form and you can address it using Me.OpenArgs in the form's module. Be aware that it's a Variant of type String, so you must check for Null, not for zero-length string.

    I sometimes use this method with a standard schema: one or several arguments can be passed into the string with the list-separator used to separate the different parts:
    Option Compare Database
    Option Explicit
    Private m_Arguments As Variant
    Private Sub Form_Open(Cancel As Integer)
        If Len(Nz(Me.OpenArgs, "")) > 0 Then m_Arguments = Split(Me.OpenArgs, ";")
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Dec 2010
    If I understand you correctly, I can filter and load only the criteria i'm looking for? If that's the case, I'm not sure how that solves my original issue of a single form holding every piece of info stacked on top of one another. It makes it virtually impossible to manage and update the form.

  4. #4
    Join Date
    Dec 2010

    cestifix bigotry,

    Yes. If you simply want to open a form when the user makes a selection from the combo box then just put in:

    DoCmd.OpenForm "frmName"

    If you want the subform on the main form to change based on the selection, then that can be done too, like so:

    Forms!frmMain!Subform.SourceObject = "frmName"

    Does this answer your question?

  5. #5
    Join Date
    Feb 2004
    Chicago, IL
    Another possibility is to have the combo box on the Main form and then have an Unbound subform on the main form. When the user selects from the combo box you can change the ControlSource for the Unbound subform.

    I have attached a screenshot of a form that I created. The whole right side of the form is an unbound subform and when the user selects from the menu on the left the appropriate subform is displayed.
    Attached Files Attached Files

Posting Permissions

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