End function (1 Viewer)

[Gismo]

Hi All,

I would like to end the function if below results in null value and continue function if WPass is not null

I am not sure how to end the function

Please could you advise?

If Nz(DLookup("[WPass]", "[GMailSettingsQry]")) = "" Then
DoCmd.OpenQuery "Clear Email Setting Tbl Local"
DoCmd.OpenQuery "Update Email Setting Tbl Local"
DoCmd.OpenForm "Airbus Mail Frm - New DAW"
End If
End Function

Dim rs As Recordset
Dim vMsg As String
Dim vSubject As String
Dim vReportPDF As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EmailTBLQry_NewDaw; ")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do.........

 

[The_Doc_Man]

I think you mean that you would like to end the function prematurely. The instruction you seek is Exit Function - which terminates the function immediately without it reaching the End Function instruction. Like perhaps you wanted to test if something is wrong and if it is, then inside an IF/ELSE/END IF block, use Exit Function. Is that what you meant?

NOTE: If you are going to do this, don't forget to assert a value for the function that is consistent with having aborted it, because Access really prefers that you return a value with a function.

 

[Gismo]

I think you mean that you would like to end the function prematurely. The instruction you seek is Exit Function - which terminates the function immediately without it reaching the End Function instruction. Like perhaps you wanted to test if something is wrong and if it is, then inside an IF/ELSE/END IF block, use Exit Function. Is that what you meant?

NOTE: If you are going to do this, don't forget to assert a value for the function that is consistent with having aborted it, because Access really prefers that you return a value with a function.

Yes, that is what i meant
But I tried that and the error is "Exit Function not allowed in Sub or Property

Just a question, if the value in WPass is not null, will the function then continue?

Option Compare Database
Option Explicit
Dim strFilename As String, strMsg As String
Dim aProjectLeaderMail As String
Dim aProductionPlannerMail As String
Dim aCurrentUserMail As String
Dim scc As String
Dim vRecipientList As String
Dim vRecipientListFrom As String


Private Sub Excecute_Click()
Dim SQL As String
Dim WPassStr As String
Dim sSQL As String

'Enter Password
If Nz(DLookup("[WPass]", "[GMailSettingsQry]")) = "" Then
DoCmd.OpenQuery "Clear Email Setting Tbl Local"
DoCmd.OpenQuery "Update Email Setting Tbl Local"
DoCmd.OpenForm "Airbus Mail Frm - New DAW"
Exit Function


Dim rs As Recordset
Dim vMsg As String
Dim vSubject As String
Dim vReportPDF As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EmailTBLQry_NewDaw; ")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do......

 

[The_Doc_Man]

Oh? Try Exit Sub and Exit Property if you have a Sub or Property routine. The Exit xxx instruction has MANY variants that are specific to the context in which they are used. For example there is also an Exit Do and an Exit For. You have to use the right "flavor" of Exit when doing an explicit but out-of-sequence exit.

 

[Gismo]

Excellent, th

Oh? Try Exit Sub and Exit Property if you have a Sub or Property routine. The Exit xxx instruction has MANY variants that are specific to the context in which they are used. For example there is also an Exit Do and an Exit For. You have to use the right "flavor" of Exit when doing an explicit but out-of-sequence exit.

Excellent, thank you

 

[MajP]

Try to avoid using Exit except when it simplifies things. Most of the time I see this is due to poorly constructed code. It can make code logic hard to follow. However, if you have a lot of nested loops and checks, it can simplify things so you have to weigh readability vs logic flow. It flows better if you use a If, ElseIf, Else construct

Dim rs As Recordset
  Dim vMsg As String
  Dim vSubject As String
  Dim vReportPDF As String
If Nz(DLookup("[WPass]", "[GMailSettingsQry]")) = "" Then
  DoCmd.OpenQuery "Clear Email Setting Tbl Local"
  DoCmd.OpenQuery "Update Email Setting Tbl Local"
  DoCmd.OpenForm "Airbus Mail Frm - New DAW"
Else
  Set rs = CurrentDb.OpenRecordset("SELECT * FROM EmailTBLQry_NewDaw; ")
  'Will not work below
  'If rs.RecordCount > 0 Then
  'rs.MoveFirst
  Do......
  loop
end if

1. when you open a recordset using the openrecordset method, it is impossible not to be at the beginning of the recordset. So movefirst is wasted code.

2. However you cannot get an accurate code count without traversing the recordset (move last, move first)
3. To check if the recordset has records
if not rs.EOF then ....
4. If you need the count first ensure it has/does not have records then traverse forward and backward.

if Not rs.EOF then
  rs.moveLast
  rs.moveFirst
  count = rs.Recordcount
end if

 

[theDBguy]

I second that thought. I use Exit Do and Exit For to get out of a Loop, but I try to avoid using Exit Sub and Exit Function. I use an If/Then block instead.

 

[The_Doc_Man]

MajP is right in what he said.

It SHOULD be noted that certain languages were designed by their authors to have no GOTO statements because of their desire to be structured in a way that was mathematically (logically) rigorous. But eventually, reality entered the picture because not everything IS capable of rigorous analysis. The language authors finally developed ways to break out of loops with an appropriate Exit-class verb.

Having given that build-up, I suggest you should use the Exit-class verbs as the thing to use ONLY when stuff just isn't going to work right because it is turning into a catastrophe and you know it. Otherwise, perhaps you should structure things like:

...
    IF error-detected THEN
        return an error-appropriate value
    ELSE
        do the rest of the subroutine or function
        return a success-appropriate value
    END IF

END SUB

 

[The_Doc_Man]

Just for reference:

Exit statement (VBA)

Office VBA reference topic

docs.microsoft.com

Shows you the five "flavors" of Exit.

Just as a side note, it doesn't show you the sixth flavor of Exit because with VBA, you are not writing a main program. Therefore, the "plain" Exit is not used. You cannot do in VBA what you COULD do in "straight-up" VB - exit the task. You have to use Application.Quit to request that action so that Access has a chance to do what it needs for a shutdown. A plain Exit (if it worked) wouldn't allow Access (or any other utility that uses VBA) to do its own cleanup.