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
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