Moving records that aren't current?

[UniqueTII]

I need to keep my job listings current by deleting any jobs that have been posted over 30 days, and I don't really know where to start. The code I use for removing them (moving them to the table of dead jobs) is:

Private Sub cmdRemoveJob_Click()
On Error GoTo Err_cmdRemoveJob_Click
Dim db As Database
Dim rs As Recordset
    
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblDeadJobs")
    rbLiveJob = 0
'the ( Me.Field1 ) is a field on my form that I want to
'include in the new record in table 2
'the ( !Field1 ) is the field in table 2 that
'is getting the data.

    With rs
        .AddNew

     !PostDate = Me.txtPostDate
     !JobNumber = Me.txtJobNumber
    !LiveJob = False
    !WSCodesID = Me.txtWSCodes
    !LocationID = Me.txtJobLocation
    !JobType = Me.txtJobType
    !JobDuties = Me.txtJobDuties
    !JobTitle = Me.txtJobTitle
    !PayRate1 = Me.txtPayRate1
    !PayRate2 = Me.txtPayRate2
    !Requirements = Me.txtRequirements
    !ContactTypeID = Me.txtContacttype
    !EmployerID = Me.txtEmployerID
    !TelNum = Me.txtTelNum
    '!ContinuousPost = Me.txtContinuousPost
    !Schedtype = Me.txtSchedule
    !WorkSchedule = Me.txtWorkSchedule
    !NumHoursAvailable = Me.txtNumHoursAvailable
        .Update
    End With
    
    rs.close
    Set db = Nothing
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    'DoCmd.close

Exit_cmdRemoveJob_Click:
    Exit Sub

Err_cmdRemoveJob_Click:
    MsgBox Err.Description
    Resume Exit_cmdRemoveJob_Click
    
End Sub

Now, what I need to do is: when the live job form is opened, I want it to check if any of the jobs have been posted for more than 30 days, for which I use this code:

Int(DateDiff("d",[PostDate],Now()))>30

but I don't know how to make it check each job and then remove the ones that have been on for over 30 days. I am so confused, I'm not even sure my question makes sense.

 

[Travis]

Why not do this with Two Queries?


1. Append Query that uses the Criteria "Int(DateDiff("d",[PostDate],Now()))>30". This will copy the records from your "Live" table to the "Dead" table

2. A Delete Query that uses the Criteria "Int(DateDiff("d",[PostDate],Now()))>30". This will delete the items that were just removed by the Append Query.


You run the Append Query First, followed by the Delete Query.

You can run these in code by using the Docmd.OpenQuery or CurrentDB.Execute.

Example:

CurrentDB.Execute "MyAppendQuery"
CurrentDB.Execute "MyDeleteQuery"

 

[Rich]

Use a query with the criteria you already have

 

[UniqueTII]

Thanks guys...I am working on that right now. I am so new at this that the simple stuff never even crosses my mind.

 

[UniqueTII]

Okay, one more thing...

I got it working just like I asked for with your help...thanks again. Now all I need it to do is change the value of [LiveJob] from -1 to 0 when the record gets moved. Any suggestions?

EDIT: This may be a round about way, but I made an update query to update [LiveJob] to 0 in the whole tblDeadJobInformation table.