Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Need method to jump to another procedure

    Hi,

    Is it possible in VBA to jump to a line in another procedure?

    I have two functions running on a search form, and would like to jump to a line in a command button procedure. I can't use the GoTo statement for this, is there another way?

    Here's an example of what I'm trying to do, using the GoTo statement to make it clearer:

    Code:
    Dim QDef As QueryDef
    Dim rst As DAO.Recordset
    Dim stDocName As String
    Dim stLinkCriteria As String
     
    Public Function NPTCSearch()
    Set QDef = CurrentDb.QueryDefs("qrySearchNPTC")
     
    QDef.Parameters("[Forms]![frmSearch]![txtEmpName]").Value = [Forms]![frmSearch]![txtEmpName]
    QDef.Parameters("[Forms]![frmSearch]![txtTown]").Value = [Forms]![frmSearch]![txtTown]
    QDef.Parameters("[Forms]![frmSearch]![txtCounty]").Value = [Forms]![frmSearch]![txtCounty]
    QDef.Parameters("[Forms]![frmSearch]![txtPCode]").Value = [Forms]![frmSearch]![txtPCode]
     
    Set rst = QDef.OpenRecordset
     
        stDocName = "frmNPTCEmployee"
     
        If rst.EOF Then
            MsgBox "No employers found.  Please add this employer.", vbOKOnly, "RECEPE"
        Else
            GoTo Search
        End If
     
    End Function
     
    Public Function RECEPESearch()
    Set QDef = CurrentDb.QueryDefs("qrySearchRECEPE")
     
    QDef.Parameters("[Forms]![frmSearch]![txtEmpName]").Value = [Forms]![frmSearch]![txtEmpName]
    QDef.Parameters("[Forms]![frmSearch]![txtTown]").Value = [Forms]![frmSearch]![txtTown]
    QDef.Parameters("[Forms]![frmSearch]![txtCounty]").Value = [Forms]![frmSearch]![txtCounty]
    QDef.Parameters("[Forms]![frmSearch]![txtCountry]").Value = [Forms]![frmSearch]![txtCountry]
    QDef.Parameters("[Forms]![frmSearch]![txtPCode]").Value = [Forms]![frmSearch]![txtPCode]
    QDef.Parameters("[Forms]![frmSearch]![cmboBArea]").Value = [Forms]![frmSearch]![cmboBArea]
    QDef.Parameters("[Forms]![frmSearch]![chkOnStop]").Value = [Forms]![frmSearch]![chkOnStop]
    QDef.Parameters("[Forms]![frmSearch]![chkT2G]").Value = [Forms]![frmSearch]![chkT2G]
    QDef.Parameters("[Forms]![frmSearch]![txtNoEmps]").Value = [Forms]![frmSearch]![txtNoEmps]
     
    Set rst = QDef.OpenRecordset
     
        stDocName = "frmEmployers"
     
        If rst.EOF Then
            MsgBox "No employers found.  Please add this employer.", vbOKOnly, "RECEPE"
            DoCmd.OpenForm "frmEmpMenu"
        Else
            GoTo Search
        End If
     
    End Function
     
    Private Sub cmdSearch_Click()
    On Error GoTo Err_cmdSearch_Click
     
    Search:
     
        DoCmd.OpenForm stDocName, , , stLinkCriteria
     
    Exit_cmdSearch_Click:
        Exit Sub
     
    Err_cmdSearch_Click:
        MsgBox Err.Description
        Resume Exit_cmdSearch_Click
     
    End Sub
     
    Private Sub Form_Load()
    DoCmd.Close acForm, "frmRefreshLinks"
    If Me!chkNPTC = True Then
        Call NPTCSearch
    Else
        Call RECEPESearch
    End If
     
    End Sub
    Thanks

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Acccording to your code listed, you don't really need to go to a line in your search sub, but rather to simply call the sub the line is in! In your code replace the instances of GoTo Search with cmdSearch_Click
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Trust me to post a question with the most obvious answer that sticks out like a sore thumb

    Thanks again.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I keep coming back to this posting not wanting to comment but I have to ...

    This kind of question is the VB 101 "not knowing any better" stupid question where everyone (who has taken a programming class) knows THAT YOU CANNOT JUMP INTO THE MIDDLE OF ANOTHER PROCEDURE FROM WHERE YOU ARE.

    Either invoke the sub/function you want to call or redesign the to-be-called sub/function to do what you want then invoke it ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    May 2009
    Posts
    12

    Red face How to call one procedure from another procedure in a select block.

    Hi all,
    How to call one procedure from another procedure in a select block.
    Here is the code for reference

    CREATE PROCEDURE PROC1 (IN i_perinc VARCHAR(2),OUT result float)
    LANGUAGE SQL
    Begin
    declare cost float;
    DECLARE c1 CURSOR WITH RETURN FOR
    SELECT product_id,item_cost cost1 FROM product where product_id = i_perinc;
    SET cost = cost1;
    OPEN c1;
    END


    Now I am trying to call this SP from another SP given below:

    CREATE PROCEDURE PROC2 ( IN Name CHAR(4) )
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    -- Declare cursor
    Declare v1 VARCHAR(2);
    DECLARE cursor1 CURSOR FOR
    SELECT
    AIRPORT_CODE,
    call PROC1(AIRPORT.i_perinc)
    FROM AIRPORT AS AIRPORT WHERE AIRPORT.AIRPORT_CODE = Name;
    OPEN cursor1;
    fetch cursor1 into v1;
    END P1


    Regards
    Vishal

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well THAT sure doesn't look like VBA
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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