How to start a specific Private Sub

[HandJT]

Hi

I have a form that when filled out needs to save in two seperate tables, by saving the info in the one table (we'll call it the info table) and calculating points into the other table (we'll call it the points table), which it is successfully doing. In this form, I have a field "Expired" that through code is calculated 6 months from the date in which I add into the date field, which it is successfully doing. What I would like the second part of my code to do is when the expired field from the "info" table = today's date to go back and subtract the points from the "points" table and then delete the record from the "info" table. Here is what I have:

Private Sub Expired_()

Dim ExpiredDate As Date

ExpiredDate = FormatDateTime(Now, vbShortDate)

Set rs2 = db.OpenRecordset("Select * " & _
"FROM Employee_Scrap_Log " & _
"WHERE Employee_Scrap_Log.[Expired] <= " & ExpiredDate)


If rs2!Expired <= "# ExpiredDate & #" Then

'For Plant Employees table
'For Repairs

If [Repair or Scrap].Value = 1 Then
If (OperationName <> "") Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Plant_Employees " & _
"WHERE Plant_Employees.[ClockNo] = " & Val(ClockNo.Value))
rs.Edit
rs![Repairs] = rs![Repairs] - 1
Select Case OperationName.Value
Case 8, 9, 10, 11, 12, 15
rs![R Points] = rs![R Points] - 0.5
Case 13, 14
rs![R Points] = rs![R Points] - 0.33
Case Else
rs![R Points] = rs![R Points] - 1
End Select
rs.Update
rs2.Delete
rs.Close
End If
Else '****************************************************

'For Scraps

If (OperationName <> "") Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Plant_Employees " & _
"WHERE Plant_Employees.[ClockNo] = " & Val(ClockNo.Value))
rs.Edit
rs![Scraps] = rs![Scraps] - 1
Select Case OperationName.Value
Case 8, 9, 10, 11, 12, 15
rs![S Points] = rs![S Points] - 0.5
Case 13, 14
rs![S Points] = rs![S Points] - 0.33
Case Else
rs![S Points] = rs![S Points] - 0.33
End Select
rs.Update
rs2.Delete
rs.Close
End If
End If

What I guess I am having the problem is starting out the private sub. I'm not sure what to have it do, (ie. before update, after update) Any suggestions anybody?

 

[hneal98]

What you can do is put the code in it's own sub in a module and then call that sub from the OnCurrent event of the form. You can have it check the value of the control you want it to check.

 

[HandJT]

Okay, let me see if I understand you correctly:

I would put this code(above) in a module, then in my form code I would call on that module through an OnCurrent event? In the OnCurrent event, I would have it check to see if the expired date = today's date?

What I guess I'm missing is that in the above code, which would be in a module, it already states the value I'm looking for. So, would I have to rewrite the above code for inputting it into a module or would I just have to delete that part and enter it in the OnCurrent event through the form code?

Sorry, I'm learning on the fly here.

 

[hneal98]

Yes. I am assuming you have the code in a text box or some control on your form? You can just give the sub a name like ChkExpireDate()... and then copy your code in the module and delete it from where ever you have it now.