PDA

View Full Version : Sleeper: Error in VBA Excel



johndave10
03-23-2017, 12:59 AM
PLease help me to solve this issue T_T

When I click run the 1st image is my error then when I click debug then thats the error.

Newbie from VBA.

I was I assigned to solve this


1873518736


Public Function PG_GetAcct(Optional FilterCond As String = "")

' If gUserAccs = "" Then
' FrmUsers.Caption = "TAS Log-On"
' FrmUsers.Show
' If Not FrmUsers.OkFlg Then
' ThisWorkbook.Close
' End If
' End If


' chadman faa
If gUserAccs = "" Then
' frmLogin.chkChangePass.Enabled = False
frmLogin.Show
If Not frmLogin.OkFlg Then
ThisWorkbook.Close
End If
End If

Dim rs As ADODB.Recordset
Dim CIDArray() As Variant
Dim i As Long

Set rs = New ADODB.Recordset

PG_CheckTASConn

If gUserBank = "BDO PB" Then
'rs.Open "select acct_code, case position(' ' in acct_name) when 0 then acct_name else substring(acct_name,1,position(' ' in acct_name)-1) end as acct_name from accounts where acct_type='" & gUserAccs & "' and trustee='" & gUserBank & "'" & FilterCond & " and start_date is not null and ((end_date is null and exclude is null) or end_date>='" & Format(Now() - 90, "mm/dd/yyyy") & "') order by acct_code", TAS_Conn, 3, 3
rs.Open "select acct_code, case position(' ' in acct_name) when 0 then acct_name else substring(acct_name,1,position(' ' in acct_name)-1) end as acct_name, end_date from accounts where acct_type='" & gUserAccs & "' and trustee='" & gUserBank & "'" & FilterCond & " and start_date is not null and (end_date IS NULL OR cast(end_date as varchar) = '')order by acct_code", TAS_Conn, 3, 3
Else
rs.Open "select acct_code, acct_name, end_date from accounts where acct_type='" & gUserAccs & "' and trustee='" & gUserBank & "'" & FilterCond & " order by acct_code", TAS_Conn, 3, 3
End If
If rs.RecordCount = 0 Then
MsgBox "No record found !"
rs.Close
If gUserBank = "BDO PB" Then
'rs.Open "select acct_code, case position(' ' in acct_name) when 0 then acct_name else substring(acct_name,1,position(' ' in acct_name)-1) end as acct_name from accounts where acct_type='" & gUserAccs & "' and trustee='" & gUserBank & "'" & " and start_date is not null and ((end_date is null and exclude is null) or end_date<='" & Format(Now() - 90, "mm/dd/yyyy") & "') order by acct_code", TAS_Conn, 3, 3 'and end_date is null and exclude is null
rs.Open "select acct_code, case position(' ' in acct_name) when 0 then acct_name else substring(acct_name,1,position(' ' in acct_name)-1) end as acct_name, end_date from accounts where acct_type='" & gUserAccs & "' and trustee='" & gUserBank & "'" & " and start_date is not null order by acct_code", TAS_Conn, 3, 3 'and end_date is null and exclude is null
Else
rs.Open "select acct_code, acct_name, end_date from accounts where acct_type='" & gUserAccs & "' and trustee='" & gUserBank & "'" & " order by acct_code", TAS_Conn, 3, 3
End If
' If MdlGetFilterStr() = "" Then
' rs.Open "select acct_code from accounts order by acct_code", TAS_Conn, 3, 3
' Else
' rs.Open "select acct_code from accounts where acct_owner='" & MdlGetFilterStr() & "' order by acct_code", TAS_Conn, 3, 3
' End If
End If
ReDim CIDArray(rs.RecordCount, 2)
For i = 1 To rs.RecordCount
CIDArray(i, 0) = rs.Fields(0).Value
CIDArray(i, 1) = rs.Fields(1).Value
CIDArray(i, 2) = rs.Fields(2).Value
rs.MoveNext
Next
rs.Close
PG_GetAcct = CIDArray

End Function

Logit
03-23-2017, 04:28 PM
I don't understand the run time error message you are receiving as it does not compare to the line of code being highlighted.

Usually when you see this highlight for :



If gUserAccs = "" Then
' frmLogin.chkChangePass.Enabled = False
frmLogin.Show '<<<<<<-------------------------------- this command
If Not frmLogin.OkFlg Then
ThisWorkbook.Close
End If
End If


It's because the UserForm "Login" doesn't exist. If it doesn't exist, Excel can't 'show' it ... hence the error.

NoSparks
03-23-2017, 04:38 PM
From the point of the debug if you use the F8 key does the procedure carry on and allow you to see if it's a line in the form initialization that errors ?