Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    various forms questions (97)

    I have a form which has basic school information on it. Since I don't want to have millions of bits of info on the form, I want to use a command button to open another form for reporting "concerns". The problem that I'm having is making sure that the entry of the concerns relates to the school shown on the main form. I have no problem filtering existing data, but I want to be able to add new concerns as well. I'm using a code to relate the two tables that are accessed, which the user shouldn't need to see.

    The second difficulty that I'm having is that I'm using a combo box and subform to show the delivery information for an order (A single school can have multiple orders in our system, each going to a different teacher, but schools hardly ever change addresses), and I want to be able to use that as a search or filter field. Again, I'm using a code to connect the two tables that the data comes from.

    When I use the combo box to select a school, is there a way to make the subform update, preferably immediately, or if not, when the combo box loses focus?

    Additionally, I have a command button on the form that will allow the user to enter a new school (since this happens periodically) but the combo box doesn't update when I go back to the main form - in fact, the only way to get the combo box to update is to close and reopen the main form. Is there a way to make that update when the secondary form is closed? (Even if I need to put a "close and update" command button that can be coded to update the combo box)

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: various forms questions (97)

    To answer your second two questions:
    In the after update event for the combo box, put in code to requery the subform.

    To force the combo box to update, put code in the onactivate event for the form to requery the combo box. When the new school form closes and focus returns to the main form, the on activate event will happen so the combo will be updated.
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: various forms questions (97)

    John has answered your other two questions well.

    In regards to your first question, you obviously don't have a subform for the concerns, maybe you should, it would simplify it somewhat.

    Why doesn't your form allow you to add concerns? Have you got allow new records set?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: various forms questions (97)

    Additional to the replies already posted: if you have a lot of information to display, you can use the tab control to organize the information into groups. By dividing the data into logically related groups on a tab page, and giving the tab pages captions that are clear to understand, you will give your users an easy way to display the data they need to see or edit.

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: various forms questions (97)

    The form does allow additions, it just doesn't automatically grab the number for the SchoolID, which leaves the concern floating around unattached.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: various forms questions (97)

    You could set the SchoolID in the BeforeInsert event of the Concerns form. This event fires as soon as the user starts to type in a new record:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.SchoolID = Forms!frmSchools!SchoolID
    End Sub

    You must replace frmSchools by the name of your schools form. This code will cause an error if you open the concerns form by itself and try to add a new record. If you only open the concerns form from the schools form, there is no problem. Otherwise, you must build in a check:

    Function IsFormLoaded(ByVal strName As String) As Boolean
    ' Returns True if the specified form is open in Form view or Datasheet view.
    If SysCmd(acSysCmdGetObjectState, acForm, strName) Then IsFormLoaded = Forms(strName).CurrentView
    End Function

    Private Sub Form_BeforeInsert(Cancel As Integer)
    If IsFormLoaded("frmSchools") Then
    Me.SchoolID = Forms!frmSchools!SchoolID
    End If
    End Sub

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: various forms questions (97)

    Excellent, Hans, I'll try that.

    The concerns form shouldn't ever be opened without the school form already being open, as it would be meaningless information that way (the schoolID never gets displayed, and that's the only thing associating the information with the correct location), but can I use something similar to that code to tell someone they can't open the form without first opening the school form? I won't be able to package this database when I'm done with it because they want to be able to expand it after I'm gone. (And I'm going to be gone in the forseeable future.)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: various forms questions (97)

    You can use the IsFormLoaded function I posted in my previous reply to prevent the Concerns form from being opened unless the Schools form is open. Put the following code in the OnOpen event of the Concerns form:

    Private Sub Form_Open(Cancel As Integer)
    If IsFormLoaded("frmSchools") = False Then
    MsgBox "Open this form from the Schools form, please.", vbExclamation
    Cancel = True
    End If
    End Sub

    Setting Cancel = True in the OnOpen event cancels the opening of the form.

    Note: I often use the IsFormLoaded function in several places in my database. Therefore, I put it in a standard module, not in the class module belonging to a form or report.

  9. #9
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: various forms questions (97)

    When I entered this code, and tried to run it, I got a sub or function not defined for IsFormLoaded

    The exact code, as I have it is:

    Private Sub Form_Open(Cancel As Integer)
    If IsFormLoaded("School") = False Then
    MsgBox "You must open this form from the School form.", vbExclamation
    Cancel = True
    End If
    End Sub

    The name of the form is simply School to stave off confusion for my users.

    I hardly know anything about VB, except that I can usually follow what I see, when I'm told how it's supposed to work.

  10. #10
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: various forms questions (97)

    John,

    Being that I know enough about VB to know that I don't know much about VB, I have no idea what the code would be, could you give me an example (I can see where it should go, just not what to put there.

    Thanks!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: various forms questions (97)

    You must put the IsFormLoaded function into a module before it will be recognized. Let's do it step by step:
    1. <LI>In the database window, click the Modules tab.
      <LI>Click the New button on the right hand side of the database window. You can also select Insert/Module from the menu.
      <LI>You will see a new, (almost) empty module window.
      <LI>Copy the following code from this post and paste it into the module window:

      Function IsFormLoaded(ByVal strName As String) As Boolean
      ' Returns True if the specified form is open in Form view or Datasheet view.
      If SysCmd(acSysCmdGetObjectState, acForm, strName) Then IsFormLoaded = Forms(strName).CurrentView
      End Function

      <LI>Save and close the module window. Name it for instance basUtilities.
    Now, the code in the Open event of the Concerns form should recognize the IsFormLoaded function.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: various forms questions (97)

    The requery the subform you just need this single line in the afterupdate event for the combo:

    Me!subform.Requery
    where subform is replaced by the name of the subform as it appears in the main form.

    To requery the combo box just have this line in the onactivate event:

    me!combo1.requery
    where combo1 is replaced by the name of the combo box
    Regards
    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
  •