form open event not working (1 Viewer)

[sahh1419]

Respected,I have a table name (fee) which have the following fields:it will only send one message and not send other messges which dues is unpaid...please advised. -----------------------------------------------------------------------GR No: 1, 2,3,4,5,6,7Name: Aslam,Naveed,Wasif,Altaf etcDate, Month, Year, TutFee, Exam, Comp, Other, Payable, Paid, Balance-----------------------------------------------------------------------i want to send the unpaid dues messges to parents whose dues is not paid afterdate 10-01-2013.i have put the following code in form open event:----------------------------------------------------------------------Private Sub Form_Open(Cancel As Integer)On Error GoTo ErrorHandlerDoCmd.SetWarnings FalseDim StrSQl As StringDim a As Datea = DateDim D As IntegerD = Day(a)If D > 10 And Me.Paid = 0 Or Me.Paid = "" ThenDim dbs As Database, rst As RecordsetSet dbs = CurrentDbSet rst = dbs.OpenRecordset("SELECT Fees.* " _& "FROM Fees WHERE [GR No]=" & "" & Me.[GR No] & "" And Me.Paid = 0)If Not rst.EOF ThenMe.T = DLookup("Mobile", "Student", "[GR No]" = "[GR No]")StrSQl = "Insert into MsgOut (iid,msg,send,msgto) values('[GR No]','Respected Parents!Your Child Fees Has Been Due..Please Paid the dues as eraliest...','Yes',T);"DoCmd.RunSQL (StrSQl)End IfEnd IfExitHandleroCmd.SetWarnings TrueExit SubErrorHandler:MsgBox Err.Number & Chr(13) & Err.DescriptionResume ExitHandlerEnd Sub

 

[pbaldy]

As answered elsewhere

It is very difficult to read unformatted code like that, but you want a loop rather than an If/Then block. It would start with

Do While Not rst.EOF

 

[sahh1419]

i have made some changes in that code: please reviewfor example: 30 students fess is unpaid. so after date 10 the message send to all parents whose students fees is unpaidrivate Sub Form_Open(Cancel As Integer)Dim Ins As IntegerOn Error GoTo ErrorHandlerDoCmd.SetWarnings FalseDim StrSQl As StringDim a As Datea = DateDim D As IntegerD = Day(a)DoIf D > 10 And Me.Paid = 0 ThenStrSQl = "Insert into MsgOut (iid,msg,send,msgto) values('[GR No]','Respected Parents!Your Child Fees Has Been Due..Please Paid the dues as earliest...','Yes',Mobile);"DoCmd.RunSQL (StrSQl)End IfExit DoLoopExitHandleroCmd.SetWarnings TrueExit SubErrorHandler:MsgBox Err.Number & Chr(13) & Err.DescriptionResume ExitHandlerEnd Sub

 

[pbaldy]

Again, as answered elsewhere, can you make it readable?

 

[sahh1419]

Please see the attached file.

 

[RainLover]

Please see the attached file.

You need to make your code more readable by using blank lines indexing a better font etc.

It is your position to make things easy for us to solve your problems.

 

[sahh1419]

Respected,
I have a table name (fee) which have the following fields:
it will only send one message and not send other messges which dues is unpaid...please advised.

Fields Of table: GR No: 1, 2,3,4,5,6,7
Name: Aslam,Naveed,Wasif,Altaf etc
Date, Month, Year, TutFee, Exam, Comp, Other, Payable, Paid, Balance
i want to send the unpaid dues messges to parents whose dues is not paid afterdate 10-01-2013.i have put the following code in form open event:
Private Sub Form_Open(Cancel As Integer)
Dim Ins As Integer
On Error GoTo ErrorHandler
DoCmd.SetWarnings False
Dim StrSQl As String
Dim a As Date = Date
Dim D As Integer
D = Day(a)
Do
If D > 10 And Me.Paid = 0 Then
StrSQl = "Insert into MsgOut (iid,msg,send,msgto) values('[GR No]','Respected Parents!Your Child Fees Has Been Due..Please Paid the dues as earliest...','Yes',Mobile);"
DoCmd.RunSQL (StrSQl)
End If
Exit Do
Loop
ExitHandler
oCmd.SetWarnings True
Exit SubErrorHandler:MsgBox Err.Number & Chr(13) & Err.DescriptionResume
ExitHandler
End Sub

 

[pbaldy]

As mentioned elsewhere, you've taken out the recordset. Perhaps this will help with the loop:

http://www.granite.ab.ca/access/email/recordsetloop.htm