Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Question Unanswered: How do I query all forms using VBA

    Question for all of you Access VBA masters...

    I'm designing an Access 2000 database to keep track of tasks relating to nursing. In order to organize the numerous nursing logs, I am using subforms (called "subNursingLog" on one master form. I would like to use a pull-down combo box (named "cboLog") to allow the nursing staff to select which log subform they want to view, but I'm having some trouble. Ideally, the combo box should be populated from entire list of forms in the database, based off of the prefix "LOG". For instance, a form named "LOG - Medical Appointments" would be added to the list. When the nurse selects that item, it should dynamically change the unbounded subform to the selected form. Here is some pseudo-code to illustrate:

    Private Sub Form_Load()
    Dim i as Integer
    For i = 1 to Forms.Count
    If Left(Forms(i-1).Name="LOG") Then
    cboLog.AddItem(Forms(i-1).Name
    End If
    Next i
    End Sub

    Private Sub cboLog_BeforeUpdate(Cancel As Integer)
    Me.subNursingLog.SourceObject = cboLog.Value
    Me.subNursingLog.LinkChildFields = "[Client ID]"
    Me.subNursingLog.LinkMasterFields = "[Client ID]"

    Me.Refresh
    End Sub

    The main problem I'm having with the first subroutine is that the collection Forms.Count only contains the current, loaded forms - not all forms in the Access database. How would I modify this to cycle through all forms?

    Because I haven't solved the first problem, I haven't debugged the second subroutine. However, if anyone notices any glaring problems, please let me know. Thanks in advance.

    Damon

  2. #2
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    You may use

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    For Each obj In dbs.AllForms
    If Left(obj.name)="LOG" then
    cboLog.AddItem(obj.Name)
    End If
    Next obj
    set dbs = nothing

    Chris

  3. #3
    Join Date
    Feb 2003
    Posts
    4

    Thumbs up

    Worked like a charm. Thanks, Chris.

    Damon

Posting Permissions

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