Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Open form based on query results

Discussion in 'Information Technology' started by JIPA, Jul 28, 2006.

  1. JIPA

    JIPA
    Expand Collapse
    Guest

    Hello,

    I have created a form which gets its data from a parameter query. The
    user opens the form which prompts them for a string which they type in
    (employee number). The form opens and displays data which matches the string
    (employee number). This same form also has a subform which displays other
    data from a linked table that matches the string (employee number) originally
    defined via the parameter query. This all works great as long as the string
    (employee number) is already in the table to which the parameter query is
    looking. If it is not things go awry.

    Is there an easy way to have the original parameter query check to see if
    any results are returned and if not instead open a different form (one which
    will allow the user to add a new string (employee number) to the table?
    However, if the parameter query does match results for the string it would
    open the original form as usual?

    Thanks in advance
     
  2. Loading...


  3. fredg

    fredg
    Expand Collapse
    Guest

    On Wed, 21 Jun 2006 07:49:01 -0700, JIPA wrote:

    > Hello,
    >
    > I have created a form which gets its data from a parameter query. The
    > user opens the form which prompts them for a string which they type in
    > (employee number). The form opens and displays data which matches the string
    > (employee number). This same form also has a subform which displays other
    > data from a linked table that matches the string (employee number) originally
    > defined via the parameter query. This all works great as long as the string
    > (employee number) is already in the table to which the parameter query is
    > looking. If it is not things go awry.
    >
    > Is there an easy way to have the original parameter query check to see if
    > any results are returned and if not instead open a different form (one which
    > will allow the user to add a new string (employee number) to the table?
    > However, if the parameter query does match results for the string it would
    > open the original form as usual?
    >
    > Thanks in advance


    Code the form's Open event:

    If Me.RecordsetClone.RecordCount = 0 Then
    MsgBox "No records"
    Cancel = True
    DoCmd.OpenForm "OtherForm"
    End if

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  4. schasteen

    schasteen
    Expand Collapse
    Guest

    A way to do what you are looking for would be to create a form with a combo
    box. Set the control source to pull the list of employee numbers something
    like:
    select distinct [employee number] from [Your Table Name]
    or the wizard can help you with this function. This will prevent typos and
    other data entry errors. Then place a button on the form and set the code of
    the on-click event to be something like:
    If isnull(Me![ComboBoxName]) then
    Docmd.Openform "The Form name to add new"
    else
    Docmd.Openform "Form Name"
    end if

    Me.visible = false

    Then in the queary where the prompt is in the criteria, change that to:
    Forms![YourNewFormName]![ComboBoxName]


    Then on the close events of the other form add the cosed
    Forms![YourNewFormName].visible = true

    This is one way to do this. The use of the combo box is much prefered to
    the prompt from the query.


    "JIPA" wrote:

    > Hello,
    >
    > I have created a form which gets its data from a parameter query. The
    > user opens the form which prompts them for a string which they type in
    > (employee number). The form opens and displays data which matches the string
    > (employee number). This same form also has a subform which displays other
    > data from a linked table that matches the string (employee number) originally
    > defined via the parameter query. This all works great as long as the string
    > (employee number) is already in the table to which the parameter query is
    > looking. If it is not things go awry.
    >
    > Is there an easy way to have the original parameter query check to see if
    > any results are returned and if not instead open a different form (one which
    > will allow the user to add a new string (employee number) to the table?
    > However, if the parameter query does match results for the string it would
    > open the original form as usual?
    >
    > Thanks in advance
     
  5. JIPA

    JIPA
    Expand Collapse
    Guest

    I’m almost there but I must be missing something.

    I did as “schasteen†suggested and created a new form (I called it selector
    for now) with a combo box and a command button and coded as specified.

    I then took “your/fredg†advice and coded the forms’ open event as suggested.

    When I open the new “selector†form, choose or type a string which is in the
    combo box/linked table and click the command button, it opens the form that I
    desire. If I enter nothing into the combo box (isnull) and click the
    command button, it opens the other form which I want. That is all perfect,
    so far.

    If I enter a string which is not in the combo box (not in the linked table)
    I do get the msgbox (No records) however upon clicking the Ok in that message
    box I get a Run-time error 2501 – The Openform action was cancelled and an
    option to debug. Debug is showing the second of the two “DoCmd.openFormâ€
    lines in “schasteens†code/suggestions as being the culprit (although this
    obviously works fine when that particular code is being accessed if the
    string entered into the combo box is valid and matches something in the
    linked table).

    What am I missing?


    "fredg" wrote:

    > On Wed, 21 Jun 2006 07:49:01 -0700, JIPA wrote:
    >
    > > Hello,
    > >
    > > I have created a form which gets its data from a parameter query. The
    > > user opens the form which prompts them for a string which they type in
    > > (employee number). The form opens and displays data which matches the string
    > > (employee number). This same form also has a subform which displays other
    > > data from a linked table that matches the string (employee number) originally
    > > defined via the parameter query. This all works great as long as the string
    > > (employee number) is already in the table to which the parameter query is
    > > looking. If it is not things go awry.
    > >
    > > Is there an easy way to have the original parameter query check to see if
    > > any results are returned and if not instead open a different form (one which
    > > will allow the user to add a new string (employee number) to the table?
    > > However, if the parameter query does match results for the string it would
    > > open the original form as usual?
    > >
    > > Thanks in advance

    >
    > Code the form's Open event:
    >
    > If Me.RecordsetClone.RecordCount = 0 Then
    > MsgBox "No records"
    > Cancel = True
    > DoCmd.OpenForm "OtherForm"
    > End if
    >
    > --
    > Fred
    > Please respond only to this newsgroup.
    > I do not reply to personal e-mail
    >
     
  6. fredg

    fredg
    Expand Collapse
    Guest

    On Wed, 21 Jun 2006 11:45:02 -0700, JIPA wrote:

    > I¡¦m almost there but I must be missing something.
    >
    > I did as ¡§schasteen¡¨ suggested and created a new form (I called it selector
    > for now) with a combo box and a command button and coded as specified.
    >
    > I then took ¡§your/fredg¡¨ advice and coded the forms¡¦ open event as suggested.
    >
    > When I open the new ¡§selector¡¨ form, choose or type a string which is in the
    > combo box/linked table and click the command button, it opens the form that I
    > desire. If I enter nothing into the combo box (isnull) and click the
    > command button, it opens the other form which I want. That is all perfect,
    > so far.
    >
    > If I enter a string which is not in the combo box (not in the linked table)
    > I do get the msgbox (No records) however upon clicking the Ok in that message
    > box I get a Run-time error 2501 ¡V The Openform action was cancelled and an
    > option to debug. Debug is showing the second of the two ¡§DoCmd.openForm¡¨
    > lines in ¡§schasteens¡¨ code/suggestions as being the culprit (although this
    > obviously works fine when that particular code is being accessed if the
    > string entered into the combo box is valid and matches something in the
    > linked table).
    >
    > What am I missing?
    >
    > "fredg" wrote:
    >
    >> On Wed, 21 Jun 2006 07:49:01 -0700, JIPA wrote:
    >>
    >>> Hello,
    >>>
    >>> I have created a form which gets its data from a parameter query. The
    >>> user opens the form which prompts them for a string which they type in
    >>> (employee number). The form opens and displays data which matches the string
    >>> (employee number). This same form also has a subform which displays other
    >>> data from a linked table that matches the string (employee number) originally
    >>> defined via the parameter query. This all works great as long as the string
    >>> (employee number) is already in the table to which the parameter query is
    >>> looking. If it is not things go awry.
    >>>
    >>> Is there an easy way to have the original parameter query check to see if
    >>> any results are returned and if not instead open a different form (one which
    >>> will allow the user to add a new string (employee number) to the table?
    >>> However, if the parameter query does match results for the string it would
    >>> open the original form as usual?
    >>>
    >>> Thanks in advance

    >>
    >> Code the form's Open event:
    >>
    >> If Me.RecordsetClone.RecordCount = 0 Then
    >> MsgBox "No records"
    >> Cancel = True
    >> DoCmd.OpenForm "OtherForm"
    >> End if
    >>
    >> --
    >> Fred
    >> Please respond only to this newsgroup.
    >> I do not reply to personal e-mail
    >>


    Error 2501 is a common error generated when a form or report is opened
    by code, but then canceled.
    You need to trap that error in the error handling portion of the event
    procedure.

    On Error Goto Err_Handler
    ' Your code here....
    DoCmd.OpenForm "OtherForm"

    Exit_Sub:
    Exit Sub
    Err_Handler:
    If Err = 2501 Then
    Else
    MsgBox "Error# " & Err.Number & " " & Err.Description
    End If
    Resume Exit_Sub

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     

Share This Page