General Code

[maxmangion]

I have some forms, which i have an Add Record on them. Now in order to avoid putting the code on each and every button, i am thinking of writing a public sub and then simply call this sub from each command button. I tried using the following code, but it is not working:

Public Sub AddRecord (FocusField As String)
On Error Goto Err_AddRecord
DoCmd.GoToRecord , , acNew
FocusField.SetFocus
Exit_AddRecord:
Exit Sub
Err_AddRecord:
MsgBox Err.Description, vbExclamation + vbOkOnly, "Error #" & Err.Number & " - " & Err.Source
Resume Exit_AddRecord
End Sub

Eventually, i substituted the following line:

Public Sub AddRecord (FocusField As String)
with
Public Sub AddRecord (FocusField As Control)

and it seems to work fine now. However, do you think that the above code is correct or shall i amend something ?

Thank You

 

[Argonak]

personally in that situation i prefer to make functions and place them in a module so that any sub can access them. but then i have a habbit of using a lot of subforms.

Function delete_system(systemname As String) As Boolean
On Error GoTo ErrorHandler
' 
' 
'
delete_system = true
ErrorHandlerExit:
    Exit Function
ErrorHandler:
    MsgBox "Error in delete_system: " & Err.description
    Resume ErrorHandlerExit
End Function

you can also just use functions within the form's code segment, it should operate exactly like what you seem to be doing. I haven't used public sub.

 

[maxmangion]

actually as i've explained above, i do not have any problems with that code as it runs fine. My only query is whether there are any problems with the line FocusField.SetFocus, because in my previous attempts i was trying to use the keyword "Me", but did not work at all ... eventually when in the arguments section of the sub, i declared the FieldFocus as String, it didn't work either .... finally, i declated it - FieldFocus As Control, and now it seems that it is working exactly as i wanter ... however, i wanted to have a feedback whether my finally code have any pitfalls, which i am missing

Public Sub AddRecord (FocusField As Control)
On Error Goto Err_AddRecord
DoCmd.GoToRecord , , acNew
FocusField.SetFocus
Exit_AddRecord:
Exit Sub
Err_AddRecord:
MsgBox Err.Description, vbExclamation + vbOkOnly, "Error #" & Err.Number & " - " & Err.Source
Resume Exit_AddRecord
End Sub

Any feedback is appreciated

Thank You

 

[maxmangion]

any suggestions pls ?

 

[ChrisO]

I don’t see anything technically wrong with it so this is just personal opinion.

Public Sub AddRecord(ByRef ctlFocusField As Control)
    
    On Error GoTo ErrorHandler
    
    DoCmd.GoToRecord , , acNew
    ctlFocusField.SetFocus
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description, vbExclamation + vbOKOnly, "Error #" & Err.Number & " - " & Err.Source
    Resume ExitProcedure
    
End Sub

It doesn’t hurt to spell out the fact that the Control is being passed by reference and also use a naming convention.
The scope of the two labels ErrorHandler and ExitProcedure is restricted to within the procedure and therefore makes for easier copy/paste of standard error handling.

Without testing it the rest looks fine.

Regards,
Chris.

 

[maxmangion]

Thank you very much for your reply ... you were right in suggesting me to name the exit and error handlers, for faster copy - paste

Btw, what's exactly the purpose of adding ByRef ... as i see it often, however, i've never used it.

Thank You

 

[Mile-O]

ByVal and ByRef are the two ways you can pass a value - by value and by reference respectively.

The easiest way is to follow this code:

Public Function Example1() As Boolean
    Dim a As Long, x As Long, y As Long
    a = 1
    Call ChangeByVal(a)
    MsgBox "After ByVal - a = " & a
    Call ChangeByRef(a)
    MsgBox "After ByRef - a = " & a
End Function

Private Sub ChangeByVal(ByVal a As Long)
    a = a + 10
End Sub

Private Sub ChangeByRef(ByRef a As Long)
    a = a + 10
End Sub

ByVal passes the value of the variable. ByRef passes the whole variable.

Test it out and see what happens.

 

[maxmangion]

Thank you for your code, i will test it out to understand the difference

 

[maxmangion]

after running your code, the difference is more clear now, i got 1 ByVal and 11 ByRef.

Thank You

 

[ChrisO]

When we pass arguments to a procedure they can generally be passed by reference ByRef or by value ByVal.

When we pass by reference we are actually passing a pointer to the thing which is being passed. This means that any changes made to that thing will reflect back to the source.

When we pass by value we are passing a copy of that thing and any changes made to it will not reflect back to the source.

The reason I’m using the word ‘thing’ is because Objects are always passed by reference even if we attempt to receive them by value. If that happens the compiler just ignores the attempt to receive by value.

The opposite is not true however. If we attempt to force the passing of an object by value it will get an error.

SJ’s very quick tonight/today.

 

[maxmangion]

thank you for the clear explanation

 

[maxmangion]

one last question, using your suggested general names ExitProcedure and ErrorHandler, if i will have two or more subs in a module (for example one to add a record, and one to delete a record), is it ok to have these repeated procedure names in both subs ?

Thanks

 

[ChrisO]

Sure is, the only line you will need to change is the Exit Sub if in fact the procedure is a Function.

Regards,
Chris.

 

[Mile-O]

Yes, it is fine.

 

[maxmangion]

thanks for the confirmation, i was confused that if i might have different error trapping code in the ErrorHandlers for example, i might confuse which one to run, since they will be in the same module.