Clearing a field with conditions

[Ruegen]

I have a form which displays areas along with a checkbox for each area





I have a dlookup that lists the number of schools that fall in that area that have the fields filled.

I would like to clear the date field in the table for the schools, for those within that area.

I don't have an idea on how to do this. Any suggestions and I don't want to clear date fields for all the schools, just the ones in the area (linked tables)

The concept is that you check an area, click the clear dates button and it clears the date field for each school within that area.

[June7]

Use WHERE clause in an UPDATE sql action.

[Ruegen]

so UPDATE tblAreas INNER JOIN tblSchools ON tblAreas.AreasID = tblSchools.AreaID SET tblSchools.CatalogueLabelPrinted = NullWHERE (((tblAreas.CheckToClear)=True));

but in vba?

[June7]

I don't see a date field in that image. Which table has the date field that you want to clear?

[Ruegen]

I don't see a date field in that image. Which table has the date field that you want to clear?

CatalogueLabelPrinted

That is the date field

[June7]

Does the UPDATE query work? What happens?

You probably also want to run another query to set the checkboxes all back to unchecked and refresh the form.

[Ruegen]

Does the UPDATE query work? What happens?

You probably also want to run another query to set the checkboxes all back to unchecked and refresh the form.

I'm fairly certain the update query will work - I've never attempted it through vba (partly because if I get it wrong). I will try it on an "offline" copy of the database of course before I attempt it just because I don't want to while any valuable data.

So on click of button, uncheck after the vba for the sql is done? I didn't think of that (if the user is lazy and doesn't uncheck the area on the form).

I'll post a result soon (it's 5 am here I just happened to be awake).

[Ruegen]

I used

Dim strSql As StringstrSql = "UPDATE tblAreas INNER JOIN tblSchools ON tblAreas.AreasID = tblSchools.AreaID SET tblSchools.CatalogueLabelPrinted = Null " & vbCrLf & _
"WHERE (((tblAreas.CheckToClear)=True));"


   Const cstrPrompt As String = _
        "Are you sure you want clear the records? Yes/No"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
        ElseIf vbYes Then
      DoCmd.RunSQL strSql
       Me.Refresh
    End If

I even put a refresh when you click the checkboxes.

It works for the first time however press it again and it asks for the wrong numbers to clear. Instead of say 20 it will clear 40.

I think it is doing all schools in area and not by whether catalogue date is clear (not that it matters as they all need to be cleared anyway but I like to have less confusion for the user.)

[Ruegen]

Turns out the boss wanted a different date field - no matter - works for that too

ended up with

Private Sub Command63_Click()Dim strSql As String
Dim strSqlCheck As String
strSql = "UPDATE tblAreas INNER JOIN tblSchools ON tblAreas.AreasID = tblSchools.AreaID SET tblSchools.CallBackDate = Null " & vbCrLf & _
"WHERE (((tblAreas.CheckToClear)=True));"


strSqlCheck = "UPDATE tblAreas SET tblAreas.CheckToClear = False " & vbCrLf & _
"WHERE (((tblAreas.CheckToClear)=True));"


   Const cstrPrompt As String = _
        "Are you sure you want clear the records? Yes/No"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
        ElseIf vbYes Then
      DoCmd.RunSQL strSql
      DoCmd.RunSQL strSqlCheck
       Me.Refresh
    End If
End Sub

thanks for the help!