Run a macro after append query.

Question

Hi all

I've been playing with this for several hours but just not getting there.

I have a control button on my main form that runs an On Click append query and a second control button that runs an On Click open form macro created with Access's macro wizard to open a popup subform related to the main form linking fields are AssessmentSessionID=AssessmentSessionID.

I would like to combine the two procedures into a single On Click procedure/event.  I added the DoCmd.RunMacro command at the end of the append procedure but am getting Argument not Optional error when I click the button.  Am I even close to the correct syntax?

Private Sub SaveSessionInfoButton_Click()

On Error GoTo ErrHandler

Dim qd As DAO.QueryDef

    Dim prm As Parameter

    Me.Dirty = False

    Set qd = CurrentDb.QueryDefs("QuerytoUpdateSubForm")

    For Each prm In qd.Parameters

    prm = Eval(prm.Name)

    Next prm

    qd.Execute dbFailOnError

      Forms("frmAssessmentDetailforSubForm").Requery

ExitHere:

    Exit Sub

ErrHandler:

    If Err.Number = 2450 Then

    Else

    MsgBox Err.Description, vbExclamation, "Error"

    End If

    Resume ExitHere

    DoCmd.SetWarnings False

    DoCmd.RunMacro , (OpenAssessment), 1

End Sub

Thanks

Rob

Answer 1

So we are still in the dark as to the reason for the error which you experienced.  My concern is that you have treated the symptom rather than the disease.  In your shoes I would temporarily change the button's On Click event property to an event procedure and put the code in the event procedure:

       DoCmd.OpenForm "frmAssessmentDetailforSubForm", _

            WhereCondition:="[AssessmentSessionID] = " & Me.[AssessmentSessionID]

Don't save the form, just switch back to form view and click the button.  You'll then be able to close it without saving and it will revert back to using the macro.  If you get the error again it points to a more fundamental problem, which could cause trouble in other contexts.  If it opens the form successfully, then I'd breathe more easily.

BTW, in case you're wondering why the above code is spread over two lines, this is purely for readability.  The underscore character at the end of the first line is a continuation character and allows you to put the remaining code on a separate line.  It executes as a single line, however.

One other point: the term 'subform' has a very specific meaning; it's a form which is embedded in a parent for as the source object of a subform control.  What you now have are what might be termed 'linked forms' although the term has no formal meaning.

Answer 2

Thanks Scott - I should have mentioned that I am not a coder :( I am taking a beginner VBA course so hopefully I will have a better understanding as I move through the course. Until then I am fumbling around so your patience is appreciated.

A small edit from my first post is that the form being called is frmAssessmentDetailforSubForm.

I googled the syntax for the OpenForm command and came up with this but the form is not opening and I am not getting an error.  Can you give me a suggestion as to where I am going wrong?

("frmAssessmentDetailforSubForm").Requery

   DoCmd.OpenForm "frmAssessmentDetailforSubForm", acNormal, , "[AssessmentSessionID]" = [AssessmentSessionID], acFormEdit, acWindowNormal

ExitHere:

    Exit Sub

Thanks

Rob

Answer 3

Why bother with a macro?

Just add a DoCmd.OpenForm method to your VBA module.

Answer 4

Hi,

why you have:

    DoCmd.SetWarnings False

    DoCmd.RunMacro , (OpenAssessment), 1

in ErrHandler routine?

I think you must put them after

      Forms("frmAssessmentDetailforSubForm").Requery

Ciao Mimmo

Answer 5

Your DoCmd.RunMacro statement is not correctly written, and also it's in the wrong place -- it's in the error-handling block, and after the Resume statement, so it will never be executed at all.  You should place it like this:

    qd.Execute dbFailonError

    Forms("frmAssessmentDetailforSubForm").Requery

    DoCmd.RunMacro "OpenAssessment"

Although it may be that the macro needs to run before you requery frmAssessmentDetailforSubForm; I can't tell from the information provided.  If the macro opens form frmAssessmentDetailforSubForm, then there's no point to requerying it if it isn't already open.