[computer room] real time judgment of balance


The first time I went to check and accept the computer room, master asked me how to ensure that students would not have negative balance due to too long time on the computer, but I didn't think about it carefully, but later master told me that I could realize a real-time function to judge the balance, so I started to do this function idea: timer control

Train of thought:

  • Using timer control
  • The time interval is one minute (the maximum time interval allowed by Timer)
  • Tables required for connection
  • Join the OnLine table with the Student table
  • Calculate the on-board time (the same as under "off board")
  • Calculated balance
  • Circulate

Specific implementation:

Private Sub Timer2_Timer()
  Dim txtsql As String
  Dim msgtext As String
  Dim mrcOnL As ADODB.Recordset
  Dim mrcStu As ADODB.Recordset
  Dim mrcBas As ADODB.Recordset
  Dim Balance As Integer

 'take OnLine Table and student Table to connect
  txtsql = "select cash from student_Info,OnLine_Info where _ 
      student_Info.cardno=OnLine_Info.cardno "
  Set mrcStu = executeSQL(txtsql, msgtext)

  'Connect Online surface
  txtsql = "select * from OnLine_Info "
  Set mrcOnL = executeSQL(txtsql, msgtext)

  'Connect Basic surface
  txtsql = "select * from BasicData_Info "
  Set mrcBas = executeSQL(txtsql, msgtext)

'When OnLine Calculate and judge when the table is not empty
Do While Not mrcOnL2.EOF

 'Calculate the length of time on the computer
  intlinetime = (Date - DateValue(mrcOnL2!ondate)) * 1440 _
      + (Hour(Time) - Hour(TimeValue(mrcOnL2!OnTime))) * 60 _
      + (Minute(Time) - Minute(TimeValue(mrcOnL2!OnTime)))

 'Calculated balance
  If Trim(mrcOnL.Fields(1)) = "Temporary users" Then 'If it is a temporary user, calculate the balance
      Balance = Trim(mrcStu.Fields(0)) - (Int(intlinetime / 30) + 1) * _ 
       (Trim(mrcBas.Fields(1)) / 2)
  Else 'If it is a fixed user, calculation of consumption amount
      Balance = Trim(mrcStu.Fields(0)) - (Int(intlinetime / 30) + 1) * _ 
       (Trim(mrcBas.Fields(0)) / 2)
  End If

  If Balance < Trim(mrcBas.Fields(5)) Then
  txtCardNo.Text = mrcOnL.Fields(0)
  MDIFrom.cmdOffLine = True
  End If

end sub

Conclusion: I think it's very difficult to realize this function before I have any ideas, and it's quite difficult, but I found it's not very difficult to do it myself.

Special thanks:

Thanks for master's guidance and Comrade Wei Jie's help. In fact, Wei Jie came up with the idea of cycling and connecting the two tables.

Posted on Sat, 04 Apr 2020 14:04:08 -0700 by maryp