Using access 2003.
(1) If i start the form, press btn_clientlist, select a letter im wh_letter then select a client in list_client i will get data in lst_paymentslist.
(2) But if i start the form again, press btn_dateview, put in a date into txt_enterdate, a date which i know will not show data in lst_paymentslsit then i get msg No payments for that date which is correct. If i then press btn_change view to be able to press btn_clientlist select a letter in wh_letter and choose a client in lst_client then the code stops says invalid use of null.
The problem is there should be data in lst_paymentslist because it worked the first time i started the from (1)
the code stops on this line in lst_client_click
listsum = listsum + lst_PaymentsList.Column(8, x)
Thanks for help
Option Compare Database
Option Explicit
Dim cn As ADODB.Connection
Dim rs_Client As ADODB.Recordset
Private Sub btn_ChangeView_Click()
Dim criteria3 As String
criteria3 = ""
lst_PaymentsList.RowSource = criteria3
wh_letter.Visible = False
txt_EnterDate = ""
txt_EnterDate.Visible = False
lst_Client.Visible = False
Detail_Payment.Visible = False
btn_ClientList.Visible = True
btn_DateList.Visible = True
End Sub
Private Sub btn_ClientList_Click()
wh_letter.Visible = True
wh_letter.SetFocus
remove_letter
btn_DateList.Visible = False
btn_ClientList.Visible = False
End Sub
Private Sub btn_DateList_Click()
txt_EnterDate.Visible = True
txt_EnterDate = ""
txt_EnterDate.SetFocus
btn_DateList.Visible = False
btn_ClientList.Visible = False
End Sub
Private Sub cmd_exit_Click()
DoCmd.Close
End Sub
Private Sub Form_Load()
DoCmd.Maximize
Lbl_date.Caption = Date
wh_letter.Visible = False
Set cn = CurrentProject.Connection
Set rs_Client = New ADODB.Recordset
rs_Client.Open "Client", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rs_Client.Index = "PrimaryKey"
Detail_Payment.Visible = False
lst_Client.Visible = False
txt_EnterDate.Visible = False
Label87.Visible = False
Label88.Visible = False
Label89.Visible = False
Label83.Visible = False
Label82.Visible = False
Label84.Visible = False
Label85.Visible = False
btn_ClientList.SetFocus
End Sub
Private Sub Form_Timer()
lbl_time.Caption = Format(Now, "hh:mm:ss")
End Sub
Public Sub remove_letter()
Dim alpha(1 To 26) As Integer
Dim x As Integer
Dim f As Form
Set f = Screen.ActiveForm
For x = 1 To 26
alpha(x) = 0
Next x
With rs_Client
.MoveFirst
Do Until .EOF
alpha(Asc(Left$(!Surname, 1)) - 64) = 1
.MoveNext
Loop
End With
For x = 1 To 26
If alpha(x) = 0 Then
f("toggle" & (x + 9)).Visible = False
End If
Next x
End Sub
Private Sub lst_Client_Click()
Dim criteria2 As String
criteria2 = "SELECT Payments.PaymentNo, Client.ClientNo, [Title] & ' ' & [Forename] & ' ' & [Surname] AS Name, Client.Address1, Client.Address2, Payments.FromDate, Payments.ToDate, Payments.NoWeeks, Payments.Amount FROM Client INNER JOIN Payments ON Client.ClientNo = Payments.ClientNo WHERE (((Client.ClientNo)=[lst_Client]));"
'criteria2 = "SELECT Payments.PaymentNo, Client.ClientNo, [Title] & " " & [Forename] & " " & [Surname] AS Name, Client.Address1, Client.Address2, Payments.FromDate, Payments.ToDate, Payments.NoWeeks, Payments.Amount FROM Client INNER JOIN Payments ON Client.ClientNo = Payments.ClientNo WHERE (((Client.ClientNo)=[lst_Client]));"
lst_PaymentsList.RowSource = criteria2
lst_PaymentsList.ColumnCount = "9"
lst_PaymentsList.ColumnWidths = "0cm;0cm;0cm;0cm;0cm;3cm;3.501cm;2cm;1cm"
lst_PaymentsList.Width = 5790
lst_PaymentsList.Left = 8730
lst_PaymentsList = ""
lst_PaymentsList.Requery
Label87.Visible = False
Label88.Visible = False
Label89.Visible = False
Label83.Visible = True
Label82.Visible = True
Label84.Visible = True
Label85.Visible = True
Detail_Payment.Visible = True
lblClientNo.Caption = lst_Client.Column(0)
lbl_PaymentNo.Caption = ""
Dim listrs As Integer
Dim listsum As Integer
Dim x As Integer
Dim listrs2 As Integer
Dim listsum2 As Integer
Dim y As Integer
listrs = lst_PaymentsList.ListCount
Do
listsum = listsum + lst_PaymentsList.Column(8, x)
x = x + 1
Loop Until x = listrs
lbl_GrandTotal.Caption = listsum
listrs2 = lst_PaymentsList.ListCount
Do
listsum2 = listsum2 + lst_PaymentsList.Column(7, y)
y = y + 1
Loop Until y = listrs2
lbl_TotalSessions.Caption = listsum2
lst_PaymentsList.SetFocus
End Sub
Private Sub lst_PaymentsList_Click()
lblClientNo.Caption = lst_PaymentsList.Column(1)
lbl_PaymentNo.Caption = lst_PaymentsList.Column(0)
cmd_exit.SetFocus
End Sub
Private Sub txt_EnterDate_AfterUpdate()
Dim criteriaList1 As String
criteriaList1 = "SELECT Payments.PaymentNo, Payments.ClientNo, Client.Title & ' ' & [Forename] & ' ' & [Surname] AS Name, Client.Address1, Client.Address2, Payments.FromDate, Payments.ToDate, Payments.NoWeeks, Payments.Amount FROM Client INNER JOIN Payments ON Client.ClientNo=Payments.ClientNo WHERE (((Payments.FromDate)=[txt_EnterDate])) ORDER BY Payments.FromDate; "
lst_PaymentsList.RowSource = criteriaList1
lst_PaymentsList.ColumnCount = "9"
lst_PaymentsList.ColumnWidths = "0cm;0cm;5cm;4cm;2.508cm;3cm;3.501cm;2cm;1cm"
lst_PaymentsList.Width = 12315
lst_PaymentsList.Left = 2205
lst_PaymentsList.Requery
If lst_PaymentsList.ListCount > 0 Then
Label87.Visible = True
Label88.Visible = True
Label89.Visible = True
Label83.Visible = True
Label82.Visible = True
Label84.Visible = True
Label85.Visible = True
Detail_Payment.Visible = True
lst_PaymentsList = ""
'lst_PaymentsList.Requery
lblClientNo.Caption = ""
lbl_PaymentNo.Caption = ""
Dim listrs As Integer
Dim listsum As Integer
Dim x As Integer
Dim listrs2 As Integer
Dim listsum2 As Integer
Dim y As Integer
listrs = lst_PaymentsList.ListCount
Do
listsum = listsum + lst_PaymentsList.Column(8, x)
x = x + 1
Loop Until x = listrs
lbl_GrandTotal.Caption = listsum
listrs2 = lst_PaymentsList.ListCount
Do
listsum2 = listsum2 + lst_PaymentsList.Column(7, y)
y = y + 1
Loop Until y = listrs2
lbl_TotalSessions.Caption = listsum2
lst_PaymentsList.SetFocus
Else
MsgBox "No payments for that date !", vbInformation, "CleanAll Services"
btn_ChangeView.Visible = True
txt_EnterDate = ""
txt_EnterDate.SetFocus
End If
End Sub
Private Sub wh_letter_AfterUpdate()
Dim letter As String
Dim criteria As String
letter = Chr$(wh_letter + 64)
criteria = "SELECT Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo FROM Client LEFT JOIN Payments ON Client.ClientNo = Payments.ClientNo GROUP BY Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo HAVING (((Client.Surname) Like " & "'" & letter & "*')) AND (((Payments.ClientNo) Is Not Null)) ORDER BY Client.Surname;"
'criteria = "SELECT Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo FROM Client LEFT JOIN Payments ON Client.ClientNo = Payments.ClientNo GROUP BY Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo HAVING (((Client.Surname) Like " & "'" & letter & "*')) AND (((Payments.ClientNo) Is Not Null)) ORDER BY Client.Surname;"
lst_Client.RowSource = criteria
lst_Client.Requery
lst_Client.Visible = True
Detail_Payment.Visible = False
lst_Client.SetFocus
End Sub
(1) If i start the form, press btn_clientlist, select a letter im wh_letter then select a client in list_client i will get data in lst_paymentslist.
(2) But if i start the form again, press btn_dateview, put in a date into txt_enterdate, a date which i know will not show data in lst_paymentslsit then i get msg No payments for that date which is correct. If i then press btn_change view to be able to press btn_clientlist select a letter in wh_letter and choose a client in lst_client then the code stops says invalid use of null.
The problem is there should be data in lst_paymentslist because it worked the first time i started the from (1)
the code stops on this line in lst_client_click
listsum = listsum + lst_PaymentsList.Column(8, x)
Thanks for help
Option Compare Database
Option Explicit
Dim cn As ADODB.Connection
Dim rs_Client As ADODB.Recordset
Private Sub btn_ChangeView_Click()
Dim criteria3 As String
criteria3 = ""
lst_PaymentsList.RowSource = criteria3
wh_letter.Visible = False
txt_EnterDate = ""
txt_EnterDate.Visible = False
lst_Client.Visible = False
Detail_Payment.Visible = False
btn_ClientList.Visible = True
btn_DateList.Visible = True
End Sub
Private Sub btn_ClientList_Click()
wh_letter.Visible = True
wh_letter.SetFocus
remove_letter
btn_DateList.Visible = False
btn_ClientList.Visible = False
End Sub
Private Sub btn_DateList_Click()
txt_EnterDate.Visible = True
txt_EnterDate = ""
txt_EnterDate.SetFocus
btn_DateList.Visible = False
btn_ClientList.Visible = False
End Sub
Private Sub cmd_exit_Click()
DoCmd.Close
End Sub
Private Sub Form_Load()
DoCmd.Maximize
Lbl_date.Caption = Date
wh_letter.Visible = False
Set cn = CurrentProject.Connection
Set rs_Client = New ADODB.Recordset
rs_Client.Open "Client", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rs_Client.Index = "PrimaryKey"
Detail_Payment.Visible = False
lst_Client.Visible = False
txt_EnterDate.Visible = False
Label87.Visible = False
Label88.Visible = False
Label89.Visible = False
Label83.Visible = False
Label82.Visible = False
Label84.Visible = False
Label85.Visible = False
btn_ClientList.SetFocus
End Sub
Private Sub Form_Timer()
lbl_time.Caption = Format(Now, "hh:mm:ss")
End Sub
Public Sub remove_letter()
Dim alpha(1 To 26) As Integer
Dim x As Integer
Dim f As Form
Set f = Screen.ActiveForm
For x = 1 To 26
alpha(x) = 0
Next x
With rs_Client
.MoveFirst
Do Until .EOF
alpha(Asc(Left$(!Surname, 1)) - 64) = 1
.MoveNext
Loop
End With
For x = 1 To 26
If alpha(x) = 0 Then
f("toggle" & (x + 9)).Visible = False
End If
Next x
End Sub
Private Sub lst_Client_Click()
Dim criteria2 As String
criteria2 = "SELECT Payments.PaymentNo, Client.ClientNo, [Title] & ' ' & [Forename] & ' ' & [Surname] AS Name, Client.Address1, Client.Address2, Payments.FromDate, Payments.ToDate, Payments.NoWeeks, Payments.Amount FROM Client INNER JOIN Payments ON Client.ClientNo = Payments.ClientNo WHERE (((Client.ClientNo)=[lst_Client]));"
'criteria2 = "SELECT Payments.PaymentNo, Client.ClientNo, [Title] & " " & [Forename] & " " & [Surname] AS Name, Client.Address1, Client.Address2, Payments.FromDate, Payments.ToDate, Payments.NoWeeks, Payments.Amount FROM Client INNER JOIN Payments ON Client.ClientNo = Payments.ClientNo WHERE (((Client.ClientNo)=[lst_Client]));"
lst_PaymentsList.RowSource = criteria2
lst_PaymentsList.ColumnCount = "9"
lst_PaymentsList.ColumnWidths = "0cm;0cm;0cm;0cm;0cm;3cm;3.501cm;2cm;1cm"
lst_PaymentsList.Width = 5790
lst_PaymentsList.Left = 8730
lst_PaymentsList = ""
lst_PaymentsList.Requery
Label87.Visible = False
Label88.Visible = False
Label89.Visible = False
Label83.Visible = True
Label82.Visible = True
Label84.Visible = True
Label85.Visible = True
Detail_Payment.Visible = True
lblClientNo.Caption = lst_Client.Column(0)
lbl_PaymentNo.Caption = ""
Dim listrs As Integer
Dim listsum As Integer
Dim x As Integer
Dim listrs2 As Integer
Dim listsum2 As Integer
Dim y As Integer
listrs = lst_PaymentsList.ListCount
Do
listsum = listsum + lst_PaymentsList.Column(8, x)
x = x + 1
Loop Until x = listrs
lbl_GrandTotal.Caption = listsum
listrs2 = lst_PaymentsList.ListCount
Do
listsum2 = listsum2 + lst_PaymentsList.Column(7, y)
y = y + 1
Loop Until y = listrs2
lbl_TotalSessions.Caption = listsum2
lst_PaymentsList.SetFocus
End Sub
Private Sub lst_PaymentsList_Click()
lblClientNo.Caption = lst_PaymentsList.Column(1)
lbl_PaymentNo.Caption = lst_PaymentsList.Column(0)
cmd_exit.SetFocus
End Sub
Private Sub txt_EnterDate_AfterUpdate()
Dim criteriaList1 As String
criteriaList1 = "SELECT Payments.PaymentNo, Payments.ClientNo, Client.Title & ' ' & [Forename] & ' ' & [Surname] AS Name, Client.Address1, Client.Address2, Payments.FromDate, Payments.ToDate, Payments.NoWeeks, Payments.Amount FROM Client INNER JOIN Payments ON Client.ClientNo=Payments.ClientNo WHERE (((Payments.FromDate)=[txt_EnterDate])) ORDER BY Payments.FromDate; "
lst_PaymentsList.RowSource = criteriaList1
lst_PaymentsList.ColumnCount = "9"
lst_PaymentsList.ColumnWidths = "0cm;0cm;5cm;4cm;2.508cm;3cm;3.501cm;2cm;1cm"
lst_PaymentsList.Width = 12315
lst_PaymentsList.Left = 2205
lst_PaymentsList.Requery
If lst_PaymentsList.ListCount > 0 Then
Label87.Visible = True
Label88.Visible = True
Label89.Visible = True
Label83.Visible = True
Label82.Visible = True
Label84.Visible = True
Label85.Visible = True
Detail_Payment.Visible = True
lst_PaymentsList = ""
'lst_PaymentsList.Requery
lblClientNo.Caption = ""
lbl_PaymentNo.Caption = ""
Dim listrs As Integer
Dim listsum As Integer
Dim x As Integer
Dim listrs2 As Integer
Dim listsum2 As Integer
Dim y As Integer
listrs = lst_PaymentsList.ListCount
Do
listsum = listsum + lst_PaymentsList.Column(8, x)
x = x + 1
Loop Until x = listrs
lbl_GrandTotal.Caption = listsum
listrs2 = lst_PaymentsList.ListCount
Do
listsum2 = listsum2 + lst_PaymentsList.Column(7, y)
y = y + 1
Loop Until y = listrs2
lbl_TotalSessions.Caption = listsum2
lst_PaymentsList.SetFocus
Else
MsgBox "No payments for that date !", vbInformation, "CleanAll Services"
btn_ChangeView.Visible = True
txt_EnterDate = ""
txt_EnterDate.SetFocus
End If
End Sub
Private Sub wh_letter_AfterUpdate()
Dim letter As String
Dim criteria As String
letter = Chr$(wh_letter + 64)
criteria = "SELECT Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo FROM Client LEFT JOIN Payments ON Client.ClientNo = Payments.ClientNo GROUP BY Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo HAVING (((Client.Surname) Like " & "'" & letter & "*')) AND (((Payments.ClientNo) Is Not Null)) ORDER BY Client.Surname;"
'criteria = "SELECT Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo FROM Client LEFT JOIN Payments ON Client.ClientNo = Payments.ClientNo GROUP BY Client.ClientNo, Client.Title, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Payments.ClientNo HAVING (((Client.Surname) Like " & "'" & letter & "*')) AND (((Payments.ClientNo) Is Not Null)) ORDER BY Client.Surname;"
lst_Client.RowSource = criteria
lst_Client.Requery
lst_Client.Visible = True
Detail_Payment.Visible = False
lst_Client.SetFocus
End Sub
