Vba?

[hi there]

hi yall,

i'm fairly new to access, but i've been creating some dbases for a little while now to help me organize and manage the information i have to keep track of on a daily basis. currently i'm using a lot of macros on forms. i'm interested in learning about VBA and modules, but i don't have real good idea about when it's supposed to be used or really what it is. are there some general rules or guidelines for when VBA is needed?

Thanks

 

[Pat Hartman]

Whenever you find yourself creating a macro to do something, you should be writing VBA instead. Macros are a poor choice for a database (unless you are the only user) because they do not provide any error handling capabilities.

To get yourself started with VBA, you can create a macro to accomplish your task and then convert it to code using the menu option. The conversion process adds error handling to the resulting sub. Study the generated code.

Try this reference for books that might help:

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=113115#post113115

 

[hi there]

Thanks Pat

 

[Cosmos75]

Call function from converted Macro?

I had a macro that I converted to VBA.

Function ConfirmEdit()
On Error GoTo ConfirmEdit_Err

    If (MsgBox("Accept changesl? If NO, then click Cancel and then ESC. Pressing EQC clears the changes made and allows you to move to another record.", 1, "Are you sure?") = 2) Then
        DoCmd.CancelEvent
        ' Perform the action on this  line if the condition on the preceding line is true
        SendKeys "{ESC}", False
    End If


ConfirmEdit_Exit:
    Exit Function

ConfirmEdit_Err:
    MsgBox Error$
    Resume ConfirmEdit_Exit

End Function

How do I call this for my almost all my forms? I originally just added the macro to the Before Update form properties. Do I know have to copy this into each and every form or can I just call the function?

 

[Mile-O]

I'd suggest moving the code into a module and, since the function does not return a value called ConfirmEdit, you can change the word Function to Sub.

To call it within code, just type Call ConfirmEdit

 

[Cosmos75]

Mile-O-Phile,

Changed the word Sub to Function.

Sub ConfirmEdit()
On Error GoTo ConfirmEdit_Err

    If (MsgBox...
    End If


ConfirmEdit_Exit:
    Exit Sub

ConfirmEdit_Err:
    MsgBox Error$
    Resume ConfirmEdit_Exit

End Sub

Changed form code to

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call ConfirmEdit

End Sub

Doesn't work?
Am I doing something wrong?

I get a compile error: Expected variable or procedure, not modul

 

[Cosmos75]

Don't ask me way but this works!?!?!?!

I changed

Sub ConfirmEdit()
On Error GoTo ConfirmEdit_Err

to

Public Sub ConfirmEdit()
On Error GoTo ConfirmEdit_Err

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call ConfirmEdit.ConfirmEdit

End Sub

 

[Mile-O]

Sorry, my fault:

Putting Public at the start indicates that the procedure can be called from anywhere in the database.

 

[Cosmos75]

Anyone know why this works?

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call ConfirmEdit.ConfirmEdit

End Sub

and not

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call ConfirmEdit

End Sub

??

 

[Rakier]

My guess is that you named the module ConfirmEdit as well as naming the sub ConfirmEdit.

When you call a module you do it in this format:

Call MODULENAME.SUBNAME (arguments)

To call the ConfirmEdit sub in the ConfirmEdit module you have to code it as:

Call ConfirmEdit.ConfirmEdit

Typing

Call ConfirmEdit just calls the module name and doesn't refer to any procedure within the module.

HTH

 

[Cosmos75]

Just in case anybody was wondering, Rakier was right!