Macro button versus Procedure button

[Chris Guynn]

I had a problem that I've worked out, but I was hoping that someone could
shed some light on *why* I had the problem in the first place.

Here's the situation:

I've got a list of items in a form that include a unique descriptor and a
"type" field. For any group of items I can only have a "1" in the type field
one time. I want my users to be able to update which item has a type of "1"
and the software (SQL based) will not allow them to do that. I've got
everything set up so that they can do it and I decided that I wanted to do
the updating (setting the old "1" value to "4" and setting the new item as
"1") in a macro because I'm not that comfortable with VBA.

The macro flows thusly:
Control Macro (<- just the name)
setvalue-> Sets a "control" value equal to the unique identifier of the item
to set as the the new "1" type
GoToRecord:First - repositions the focus to begin the process of updating
the records
Setvalue-> Sets an iteration count so the macro knows when to stop
RunMacro:Updater - Continues while the iteration value <> record count
repeats the steps in the Updater minus the gotorecord (to make sure that the
final record is updated)

Updater Macro
If unique identifier(record)=unique identifier(control), setvalue:type=1
If unique identifier(record)<>unique identifier(control) AND type(record)=1,
setvalue:type=4
Setvalue:iteration+1
GoToRecord:Next

Anyway, I got everything to do what I wanted it to with one exception. If I
opened the macro object I could step through it and everything worked like a
charm. However, I've got a button on the form (procedure button to run the
macro) and when I use it to run the macro one of my criteria statements
doesn't work. According to the step function, the condition (If unique
identifier(record)=unique identifier(control), ) is true for every record
even though looking at the form it is obvious that that is not the case. so
it tries to set the type value for every record to "1"

When I set the button to run the macro directly instead of through a
procedure, the macro works exactly as I intended.

Any idea why it would work one way and not the other?

Here's the code that was being used to open the macro before I switched the
button to run the macro directly (this code was created through the button
wizard).

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String

stDocName = "mcrUpdateImages"
DoCmd.RunMacro stDocName

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

 

[Steve Schapel]

Chris,

I have no idea about the specific answer to your question. But I will
say I have never been able to figure out why running a macro via VBA
makes any sense anyway. I mean, you either use a macro, and run it as a
macro, or, if you're going to use VBA, use VBA.

Sorry, I know that doesn't help.

But I will also say that I would tackle your problem differently as
well. If I was using a macro, I would use an OpenQuery action to run an
Update Query to set the value of the Item field as required.

 

[Chris Guynn]

Chris,

But I will also say that I would tackle your problem differently as
well. If I was using a macro, I would use an OpenQuery action to run an
Update Query to set the value of the Item field as required.

I would normally do the same, but I was bored and thought I would enjoy
trying it a different way. The more I try stuff, the more I learn.

One of these days, I'll seriously start loooking into learning VBA, but I'm
not ready for that yet.