Do it without an Event

[michi.for]

Hi all, ty for all the help!


My question is simple:
is it possible run subroutines or functions, without a specified event?
Or i need to recognize the MOMENT IN WHICH the code is executed?

Francesco

 

[sneuberg]

You can put the code in a module, put the cursor in it, and press F5, and of course procedures independent of any event can be called from event procedures, but I don't understand what you need to do. What are you trying to accomplish?

 

[marlan]

Hi,

I would say the answer is no.
What you could do is have a OnTimer event on a form, and have a process run without other Human involvement.
but as far as I understand computing, there is a basic need for a human decision before software starts running... this decision is captured as an event.

 

[jdraw]

Francesco,

Perhaps you could give us an example of what you are trying to do.
As sneuberg said, you could have a public sub and press run.

You could have something in an autoexec file that would run when the application started.

 

[The_Doc_Man]

There IS such a thing as a Macro that does a RunCode, something you could select manually if you so desired. The code has to be a FUNCTION, not a subroutine, but you can diddle up something from the function that calls the real subroutine and just use the function as a shell. See, for example, command-line switch /X:macroname, which would allow you to execute a macro when launching Access. You can edit your launcher icon to include this option.

One can get pedantic and argue that the act of selecting and running the macro triggers a "macro run" event, but let's not split hairs. No matter how the macro gets run, it can call a function via RunCode.

The problem that you need to understand here is that you could compare Access VBA to some other VB, such as VisualBasic (from Visual Studio or purchased as a separate product). If you do, you would discover something really different between the two VB environments. VB allows you to create a MAIN segment that is your application's main sequencer. For VBA, MSAccess.EXE is the main sequencer and you can't build another MAIN segment. (Can't have two main segments in one image - has to do with the required uniqueness of the startup entry point.)

If you don't have a MAIN segment that you can modify, then you can't write a subroutine that gets called by that MAIN segment "just because it is there." You will need something to trigger MSAccess.EXE to execute your code, and that USUALLY translates to an event.

The next biggie is that VB actually produces machine code and uses a linker to produce a new .EXE file for you. VBA pseudo-compiles what it gets from you and, when an event enters your environment and you have a declared event procedure, you INTERPRET the code that VBA pseudo-compiled earlier. That code is actually DATA in some obscure part of your .MDB or .ACCDB, whatever you have. (More specifically, in the front end portion of your application if you have a split FE/BE file.) So, when you are executing your VBA code, you are actually executing the MSAccess code emulator/interpreter. (Which is one reason why event code routines usually can't interrupt each other.)

You CAN create your (pseudo-)compiled functions that are then callable using the features of SQL execution. There, the event would be the "SQL execute" event and the code operation, analogous to functions in a Macro stream, would not be launched directly in response to an event. Your functions can in turn call subroutines, but the VBA SQL interface requires a function in queries because the reference can ONLY occur in a field specification for a "SELECT list-of-fields-or-values" or a value specification for an "UPDATE MyTable SET field = value" or an "INSERT INTO MyTable (Fields-list) VALUES(values-list)". I.e. the function has to return a value for use in a field.)

Then, there is this pedantic little quibble - even your VB program responds to an event - the Launch Executable event. But that's outside the scope of this discussion. So technically, everything we write is responsive to some external event. In practice, some functions can be invoked by methods that are not immediately considered as events. NO subroutines can be written that run without some other context (such as event context) around them.

 

[michi.for]

Wow, ty for all your suggestion.
I'll study mine case and your answers, so I can explain better what i need.

 

[michi.for]

This is the example:

in a data entry form, i have 2 numbers fields (A and B) and a calculated field C (A+B).

I need to store all the values in a table.
I need to show the result only if A and B are not missing.

Which "event" can i connect to the "calculus"?

TY

 

[sneuberg]

You could add the calculated field to the table. Since you want the field to be null if A or B are null you could use iif something like:

IIf(IsNull([A]) Or IsNull(),Null,[A]+)

I noticed that Access adds a default value of 0 for numeric field. You will want to remove that from fields A and B if you want to define "missing" as being null.

Note that this doesn't actually store the data in the table, but it acts like it was. Also some will probably say you shouldn't do this as it violates normal form, but I can't see how this would create a problem. If you want to be a purist you should do the calculation in the forms.

Oh about the event. Any event that changes A or B will change C

 

[michi.for]

You could add the calculated field to the table. Since you want the field to be null if A or B are null you could use iif something like:

IIf(IsNull([A]) Or IsNull(),Null,[A]+)

I noticed that Access adds a default value of 0 for numeric field. You will want to remove that from fields A and B if you want to define "missing" as being null.

Note that this doesn't actually store the data in the table, but it acts like it was. Also some will probably say you shouldn't do this as it violates normal form, but I can't see how this would create a problem. If you want to be a purist you should do the calculation in the forms.

Oh about the event. Any event that changes A or B will change C

Now calculations are in VBA, inside the form.
That's why i wnat to know what "event" can fire the code.

...i could think about move from vba to calculated filed table...

Other suggestions?

 

[sneuberg]

I think the before updates of A and B would work. You could make a sub something like

Private Sub UpdateC()

If Not (IsNull(Me.A) Or IsNull(Me.B)) Then
    Me.C = Me.A + Me.B
Else
    Me.C = Null
End If

End Sub

And then call it in both before updates like:

Private Sub A_BeforeUpdate(Cancel As Integer)

UpdateC

End Sub

and

Private Sub B_BeforeUpdate(Cancel As Integer)

UpdateC

End Sub

 

[marlan]

Now calculations are in VBA, inside the form.
That's why i wnat to know what "event" can fire the code.

...i could think about move from vba to calculated filed table...

Other suggestions?

Enter the formula to the control's ControlSource property:

=IIf(IsNull([A]) Or IsNull(),Null,[A]+)

 

[sneuberg]

Enter the formula to the control's ControlSource property:

=IIf(IsNull([A]) Or IsNull(),Null,[A]+)

That would display it, but wouldn't update the table. If you make a calculation the control source of the control you've removed the binding to the table.

 

[marlan]

I can't understand why would someone have a calculated field in a table, in my opinion, that's just what views are for... in any case, to the best of my knowledge, these are not optional in 'regular' mdb or accdb files. I would truly reexamine the requirements: field C should be calculated in a view, otherwise in a form.

BTW, if you don't need the oldValue of A and B, I would call in the AfterUpdate Event handler.

ATB