why is my VBA runmacro not working

[Ted]

hi,

i'm using a2k...

i have a form called 'Screening Log' and on it there's an options group
called Frame1 and i've written some vba to use the select case structure. a
portion of it which isn't working yet is below

Private Sub Frame1_AfterUpdate()
Dim Response as Long
Dim Results as Integer

Select Case Frame1.Value

.........


Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.Save acForm, "Screening Log (Edit Only)"
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")

ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunMacro ("Delete Off Study Pxs--Edit Form")
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
End If

is this NOT the place to put the docmd.runmacro statement. the object it's
using is a macro with a query to append a portion of the current record to
another table.

when i found that it wasn't, i inserted the docmd.save above it on the
assumption that the changes the user makes before clicking the Case 5 option
button needed to be 'saved' to the underlying control source. i'm new at all
this, and so i aren't sure *when* and *where* the changes are applied to the
control source of the form. i figured (after looking at the Help doc'n) the
docmd.save acform would help things, but given it's not working, i guess it's
not based on the fact that the messages appearing on the screen which tell me
how many records are being appended tell me that '0' (ZERO) are getting
appended :-(

any clues to offer?

-ted

 

[Steve Schapel]

Ted,

No, DoCmd.Save does not achieve anything here, as this method applies to
design changes to the form, and has nothing to do with the data.

If you mean that the macro is actually running, but not appending the
expected data because of the fact that the form's current record has not
been updated to the table, then you could try putting this line at the
beginning of your code...
DoCmd.RunCommand acCmdSaveRecord

By the way, as an aside, it is not a good idea to put characters like (
and ) and - as part of the names of fields or database objects.

I would also remove the ()s from around the macro name, e.g.
DoCmd.RunMacro "Delete Off Study Pxs--Edit Form"

 

[Ted]

that looks super, steve! thanks a heap

all the best,

-ted