If statement

[losthome]

Hi There,

I having been trying to have an if statement work, but have no success. I have attach a pic of what I am trying to do.

I have a form that has data in it and a yes/no check box at the end. The form is looking at data from a query. If there is a check mark in the box, append those records to another data table. But then I don't want the people with the check mark showing in the query anymore.

If I put a cretiera NO, then the append query doens't work.

If this isn't clear, please say so and I will try a different way of explaining it.

This is the only code I have so far, it appends to another table, but then I don't know how to filter out the people who have check marks.

DoCmd.SetWarnings False
If Me.Select = True Then
DoCmd.OpenQuery "qryappend90dayreview"
End If
DoCmd.SetWarnings True

Any suggustions?

Jennifer

 

[charityg]

Depending on what you want to do, you either need to run an update query to uncheck all of the check boxes in the table after you append the records, or run a delete query to delete the checked records you just appended.

 

[losthome]

If I use a delete query, it deletes the person from the employee main list, which is not good.

Any other suggestions?

Jennifer

 

[glynch]

You can run a delete query to delete records from any specific table you want. Maybe you explanation isn't clear enough (at least not to me), I'm not sure what problem you are now having and why you can't do what you want.

 

[losthome]

Ok, I will try and explain it better.

I have many tables that hold different information.

EG.

EmployeeInfo - holds all the employees for the company

TrainingCompleted - Keeps track of the training the employees have taken

90dayReivew - Keeps track of the employee's 90 review time

I have a query that takes the employee's and their start date's and once their 90 day review time comes up, I want to select the employee's and check mark off the ones that the reviews have been done. Then I don't want to see those employee's on that list anymore, but they must stay in the employee table. That is why a delete query won't work

I have a Select field in the employee info table that is a yes/no field. This is the field I am using to select the employees.

I hope this is clearer and I hope someone can help me out.

Thanks

Jennifer

 

[Rich]

Your checkbox and criteria needs to be set against training completed table and not the employees table.
Training should be a One to Many with Employees

 

[RV]

>Then I don't want to see those employee's on that list anymore<

Add this line to your code:

Me.Refresh

So your Event code becomes:

DoCmd.SetWarnings False
If Me.Select = True Then
DoCmd.OpenQuery "qryappend90dayreview"
End If
DoCmd.SetWarnings True
Me.Refresh

As for Rich's remark:

>Your checkbox and criteria needs to be set against training completed table and not the employees table<

I guess Rich means the checkbox and criteria needs to be set against the 90DayReview table....

RV

 

[Pat Hartman]

I think that you are making this harder than it needs to be. The 90dayreview table is completely unnecessary. If you have a date field that stores the date of the last review in your EmployeeInfo table, you can always use a query to select those employees who have not yet had a review and are currently due.

 

[losthome]

Thank you Pat,

I think your idea is going to work and I will give it a try.

Thanks,
Jennifer

****
Pat it worked Thanks again, I was going about it the very hard way.

Jennifer