Pause a Function in Access 2010

Question

I have a function in my Orders form that test several of the fields to make sure the data in them is correct and creates ancillary records (such as a tracking record) if they haven't been created already.  We have recently started co-locating with another show and sharing revenue for some (but not all) of our exhibitors with this show.  I have a table linked to my Companies table that indicates which show we are sharing revenue with for that company and what percentage of revenue they get.  I did it that way to have the flexibility to have similar agreements with multiple shows.  Because we may or may not be doing this in any given year, I also have a similar table linked to the orders table.  I want Access to prompt the user when an order is created for a company with the shared designation giving them the option to create a record or not depending on whether we are co-locating and select which show we are sharing with (in the event that someone is shared with more than one show).  I created a dialog box to collect this information and put code in to open that form and populate the fields with the available choices.  The problem is that this function closes the form when it's done if there are no problems.  I thought that by making the dialog box modal, it would stop the function until the dialog box is closed, but that doesn't seem to be the case, so I'm losing the RowSource for my combo box.  I need a way to get the function to wait until this form is closed (and any record saved) before continuing and closing the form.  My code is below:

      'Check to see if this is a revenue-share company

      If Me.SplitChecked = False Then 'Hasn't been checked yet.

        Dim rsSplits As DAO.Recordset

        Set rsSplits = CurrentDb.OpenRecordset("SELECT tblSplits.MasID, tblSplits.SisterShowID, tlkpSisterShows.SisterShowName, tblSplits.SplitPercent" & _

          " FROM tblSplits INNER JOIN tlkpSisterShows ON tblSplits.SisterShowID = tlkpSisterShows.SisterShowID WHERE MasID = " & Me.exh_masid, dbOpenDynaset, dbSeeChanges)

        If rsSplits.RecordCount > 0 Then 'Company is designated as a shared company

          DoCmd.OpenForm "fdlgCreateOrderSplit"

          'Pass the OrderID

          Forms!fdlgCreateOrderSplit!OrderID = Me.OrderID

          'Set the record source for the Combo Box

          Forms!fdlgCreateOrderSplit!cboSisterShow.RowSource = "SELECT SisterShowID, SisterShowName, SplitPercent FROM rsSplits UNION " & _

            "SELECT 0 AS SisterShowID, 'None' AS SisterShowName, 0 AS SplitPercent ORDER BY SisterShowID"

          Forms!fdlgCreateOrderSplit!cboSisterShow.SetFocus

        End If

        Me.SplitChecked = True 'We don't need to check this record again.

      End If

Answer 1

Ah! That's because you are using your recordset as a table in the query. You can't do that. You will need to save the SQL as a query. You can put the first form's control in the query's criteria as a parameter (prompt).

PARAMETERS [Forms]![YourFormName]![exh_masid] Long;

SELECT tblSplits.MasID, tblSplits.SisterShowID, 

    tlkpSisterShows.SisterShowName, tblSplits.SplitPercent

FROM tblSplits INNER JOIN tlkpSisterShows 

    ON tblSplits.SisterShowID = tlkpSisterShows.SisterShowID 

WHERE MasID = [Forms]![YourFormName]![exh_masid]

Then change rsSplits to the name of your new query.

Answer 2

It's populating the Order ID, but the combo box still gives me the attached error message.  It looks like it is pushing the SQL statement to the dialog, but the combo box doesn't appear to be able to resolve it into something useful.

Answer 3

Amy

Use the OpeningArgs property separating the two values with a pipe.

From the first form:

      Dim strSQL As String

      strSQL= "SELECT SisterShowID, SisterShowName, SplitPercent " _

            & "FROM rsSplits UNION " & _

            & "SELECT 0 AS SisterShowID, 'None' AS SisterShowName, " _

            & "0 AS SplitPercent ORDER BY SisterShowID"

      DoCmd.OpenForm FormName:="fdlgCreateOrderSplit", _

            WindowMode:=acDialog, _

            OpenArgs:=Me.OrderID & "|" & strsql

Then in the dialog form's Load event:

Private Sub Form_Load()

    Dim aryOpenArgs As Variant

    'Fill array with openargs

    aryOpenArgs = Split(Me.OpenArgs, "|")

    Me.OrdetID = aryOpenArgs(0)

    Me.cboSisterShow.RowSource = aryOpenArgs(1)

End Sub

Answer 4

How do I get it to pass the value for the OrderID and the RowSource for cboSisterShow?  I'm assuming the code will stop when the form opens, and I'm passing the values after that.

Answer 5

Amy

A form opened in Dialog mode will stop the code, but you have to specify that with your DoCmd.

DoCmd.OpenForm FormName:="fdlgCreateOrderSplit", WindowMode:=acDialog 

There is no need to open it modal.