Public Sub to run Private Function

[TrickDownie]

Newbie - I know the code isn't too pretty but basically I'm wondering
why my Private Function Update_End() won't run in my below;

Private Sub Command12_Click()

I get a compile error "Variable not defined"

with x= highlighted.


'UPDATE START DATETIME
Private Sub Command12_Click()

Dim SQLString As String
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cmd2 As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

DoCmd.SetWarnings False

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn
Set cmd2.ActiveConnection = cnn

SQLString = "SELECT * "
SQLString = SQLString & "FROM jobs "
SQLString = SQLString & "WHERE
(((jobs.active_jobs_msglog_crtdt) Is Null));"

' MsgBox (SQLString)

cmd.CommandText = SQLString

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenKeyset, adLockOptimistic
' MsgBox (rst!active_jobs_msglog_crtdt & ", " & rst!Branch_Number)

If rst.RecordCount > 0 Then
Do While Not rst.EOF
' MsgBox (rst!active_jobs_msglog_crtdt & ", " & rst!Item_id)

SQLString = "SELECT downloads.Branch_Number,
downloads.msglog_text, downloads.item_id, Min(downloads.msglog_crtdt)
AS MinOfmsglog_crtdt "
SQLString = SQLString & "FROM downloads "
SQLString = SQLString & "GROUP BY
downloads.Branch_Number, downloads.msglog_text, downloads.item_id "
SQLString = SQLString & "HAVING
(((downloads.Branch_Number)= " & rst!Branch_Number & ") AND
((downloads.msglog_text) Like '%active%') AND ((downloads.item_id)= " &
rst!item_id & "));"

cmd2.CommandText = SQLString

rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenKeyset, adLockOptimistic

'MsgBox (rst2.GetString)

If rst2.RecordCount > 0 Then

SQLString = "UPDATE jobs SET
jobs.active_jobs_msglog_crtdt = (#" & rst2!MinOfmsglog_crtdt & "#) "
SQLString = SQLString & "WHERE (((jobs.qrt)
= " & rst!qrt & ") AND ((jobs.year) = " & rst!Year & ") AND
((jobs.Branch_Number) = " & rst!Branch_Number & ") AND ((jobs.item_id)
= " & rst!item_id & "));"
DoCmd.RunSQL (SQLString)


End If
rst.MoveNext
rst2.Close
Loop
End If

x = Update_End()

DoCmd.SetWarnings True
End Sub


'UPDATE END DATETIME
Private Function Update_End()

Dim SQLString As String
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cmd2 As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

DoCmd.SetWarnings False

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn
Set cmd2.ActiveConnection = cnn

SQLString = "SELECT * "
SQLString = SQLString & "FROM jobs "
SQLString = SQLString & "WHERE
(((jobs.completed_jobs_msglog_crtdt) Is Null));"

' MsgBox (SQLString)

cmd.CommandText = SQLString

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenKeyset, adLockOptimistic
' MsgBox (rst!completed_jobs_msglog_crtdt & ", " & rst!Branch_Number)

If rst.RecordCount > 0 Then
Do While Not rst.EOF
' MsgBox (rst!completed_jobs_msglog_crtdt & ", " & rst!Item_id)

SQLString = "SELECT downloads.Branch_Number,
downloads.msglog_text, downloads.item_id, Max(downloads.msglog_crtdt)
AS MaxOfmsglog_crtdt "
SQLString = SQLString & "FROM downloads "
SQLString = SQLString & "GROUP BY
downloads.Branch_Number, downloads.msglog_text, downloads.item_id "
SQLString = SQLString & "HAVING
(((downloads.Branch_Number)= " & rst!Branch_Number & ") AND
((downloads.msglog_text) Like '%completed normally%') AND
((downloads.item_id)= " & rst!item_id & "));"

cmd2.CommandText = SQLString

rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenKeyset, adLockOptimistic

'MsgBox (rst2.GetString)

If rst2.RecordCount > 0 Then

SQLString = "UPDATE jobs SET
jobs.completed_jobs_msglog_crtdt = (#" & rst2!MaxOfmsglog_crtdt & "#) "
SQLString = SQLString & "WHERE (((jobs.qrt)
= " & rst!qrt & ") AND ((jobs.year) = " & rst!Year & ") AND
((jobs.Branch_Number) = " & rst!Branch_Number & ") AND ((jobs.item_id)
= " & rst!item_id & "));"
DoCmd.RunSQL (SQLString)


End If
rst.MoveNext
rst2.Close
Loop
End If
DoCmd.SetWarnings True
End Function

 

[Jayyde]

Why do you have it as a function when it's not returning anything (or is it
and I'm just not seeing it)? Change it to a sub and instead of doing

x = Update_End()

just do

Call Update_End


hth
-Jayyde