Results 1 to 12 of 12
  1. #1
    Join Date
    May 2010
    Posts
    67

    Unanswered: Problem with Multiple Instances of a form

    Hello,

    I have an application that allows for multiple instances of a forms. The form has fields that when updated or changed, will trigger the recordsource of other fields on the form to change because of a requery.

    The problem occurs, when I open another instance of the form, the fields of the second form inherits the query results of the first form. I thought that the forms were independent of each other.

    Can anyone tell what I am not doing. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. How do you create the other instances of the form from the firts one? The answer can explain the behaviour you describe.

    2. If all instances have the same query as RecordSource, they also have the same data set, except if you use a filter on the RecordSet of an instance, where it will depend on the answer to the first question.
    Have a nice day!

  3. #3
    Join Date
    May 2010
    Posts
    67
    Thank you for your assistance Sinndho.

    Answer to question 1:
    Other instances of the form are created from a main menu screen, if the users wants another instance there is a button on the main menu.

    On the form there is a Client Name field and a Job Number field. When the user selects a Client Name (from a drop down list) on the form, a query that is triggered selects all Job Numbers associated with the Client Name. The results are in a drop down list in the Job Number field. From there the user can select the Job Number he wants to view and all detail information is then displayed on the form.

    The problem is when subsequent instances of the same form is opened, the Job Number drop down list contains the results of the first form instance.

    I have tried the following code in the current event of the form, but to no avail:

    If CurrentProject.AllForms("frmartwork").IsLoaded Then
    JobNumber.Requery
    ClientID.Requery
    End If

    Any suggestions will truly be appreciated. Thanks.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I asked how the other instances are created, not how the user opens them. Which code is used to re-instanciate the already open form, if you prefer.

    2. This:
    Code:
    If CurrentProject.AllForms("frmartwork").IsLoaded Then
    simply tests whether the first instance of the form is open or not, which is useless in these circumstances as you perform the test in the class module of the form (i.e. the form does not need to ask itself if it's open).

    3. Do all instances have the same Query as RecordSource. I mean query as a permanent object in the database, not a SQL statement directly stored in the RecordSource property of the form. If yes, please post it's SQL statement.

    4. If 3. hereabove is true and no filter are used (i.e. if the query contains references to controls the form in its WHERE clause), the behaviour you describe is normal and you'll have to redesign your system to provide an independant data source (at least in it's WHERE clause) for each instance of the form, or use filters.
    Have a nice day!

  5. #5
    Join Date
    May 2010
    Posts
    67
    Hi Sinndho, again thanks for your assistance.

    Answers:
    1. Other instances are created by a called function (code below):
    PHP Code:
    Public Function OpenFormInstance(FormName As StringWhereCondition As Variant)

        
    Dim frm As Form
        Dim FormInstance 
    As Boolean
        Dim strJobNumber 
    As String
        FormInstance 
    True
        
        Select 
    Case FormName
            
    Case "frmClientMailSchedule"
                 
    Set frm = New Form_frmClientMailSchedule
            
    Case "client"
                 
    Set frm = New Form_Client
            
    Case "frmartwork"
                 
    Set frm = New Form_frmArtwork
            
    Case Else
                 
    FormInstance False
        End Select
        
        
    If FormInstance False 
           
    Exit Function
        
    End If
        
        
    IntMoveWindow IntMoveWindow 1
        
        
    If WhereCondition <> "" Then
           NewFormID 
    WhereCondition
           
           
    If FormName "frmClientMailSchedule" Then
              strJobNumber 
    DLookup("[jobnumber]""mailschedule""[scheduleid] = " WhereCondition)
              
    frm.Caption "Client Mail Schedule - Job  " strJobNumber
              frm
    .Filter "scheduleid = " WhereCondition
           End 
    If
           
           
    DoCmd.MoveSize (IntMoveWindow 1) * 80, (IntMoveWindow 1) * 350
           
           frm
    .FilterOn True
        
    Else
           
    DoCmd.MoveSize (IntMoveWindow 1) * 80, (IntMoveWindow 1) * 350
           frm
    .FilterOn False
        End 
    If
        
        
    frm.Visible True
        mcolFormInstance
    .Add Item:=frmKey:=CStr(frm.hwnd)    
        
    Set frm Nothing

    End 
    Function 
    2. You are so right, the code I placed in the current event is useless....DUH!

    3. The SQL is stored in RecordSource property of the form, the query is not a permanent object.

    4. With this form, I will not be using a filter. So a slight re-design my be necessary.

    Any other suggestions would be appreciated!

    Thanks a bunch~

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you post the code that changes the data set of a form (by altering its RecordSource property, as Filters are not used)?

    Also, what are these used for in the Function OpenFormInstance?
    Code:
    frm.Filter = "scheduleid = " & WhereCondition 
    frm.FilterOn = True
    Have a nice day!

  7. #7
    Join Date
    May 2010
    Posts
    67
    Hi Sinndho,

    In the After Update Event of the Client Name field, the RowSource property is changed to (this code only selects all job numbers associated with the Client Name (i.e. ClientID):
    Code:
    Forms!frmartwork!JobNumber.RowSource = "SELECT MailSchedule.ScheduleID, MailSchedule.JobNumber FROM MailSchedule WHERE (((MailSchedule.ClientID) = [Forms]![frmartwork]![ClientID])) ORDER BY MailSchedule.JobNumber DESC;"
    The OpenFormInstance function will be executed to open new instances of all the forms in the application. Some forms that call this function can pass data to the WhereConditon argument for filtering purposes. The form that I have a problem with (frmArtwork) do not pass data to the WhereCondition argument.

    The code below, the WhereCondition argument contains data used to filter on. ScheduleID is the primary key in the MailSchedule table. When executed, the Mail Schedule form opens on that specific record.
    Code:
    frm.Filter = "scheduleid = " & WhereCondition 
    frm.FilterOn = True
    I hope this makes sense. Your assistance is very much appreciated!!!!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try replacing the reference to the form (Forms!frmartwork!...) by Me. which is a shortcut to the current instance of the form.
    Have a nice day!

  9. #9
    Join Date
    May 2010
    Posts
    67
    Sinndho

    Tried the Me. and got the same results. I am going to have to think this through. Any other suggestions are definitely appreciated.

    I am a Mainframer (i.e. mainframe programmer), I just started coding in Access \VBA about a year and a half. Still learning, especially from invaluable sites such as this one and dedicated people like yourself. Thanks for the mentoring!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I know what you mean: a long time ago, I used to program in Fortran, Cobol and C on VAX computers.

    Anyway, I think I've perhaps identified the culprit. There is a reference using the Forms collection in the WHERE clause too:
    Code:
    ...WHERE (((MailSchedule.ClientID) = [Forms]![frmartwork]![ClientID]))...
    You should try:
    Code:
    Me.!JobNumber.RowSource = "SELECT MailSchedule.ScheduleID, MailSchedule.JobNumber FROM MailSchedule WHERE (((MailSchedule.ClientID) = " & Me.[ClientID] & ")) ORDER BY MailSchedule.JobNumber DESC;"
    Have a nice day!

  11. #11
    Join Date
    May 2010
    Posts
    67
    Hi Siindho,

    Thanks so much for your help. All your suggestions worked, but I changed the Me.ClientID to ClientID.value and it works perfectly.

    Code:
    Me.JobNumber.RowSource = "SELECT MailSchedule.ScheduleID, MailSchedule.JobNumber FROM MailSchedule WHERE (((MailSchedule.ClientID) = " & ClientID.value & ")) ORDER BY MailSchedule.JobNumber DESC;"
    Whew! Wow!.....what a week!

    Have a Great Day!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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