Using Checkbox to update a field

Question

I am currently working on an ACCESS database where I have created a form that pulls records where the contact date is blank.  I want to use the form to help fill the contact date.  What I mean by that is I want to use a check box to select 5 out of 10 records (as an example) and update all of these records with the same date. I would like to enter the date in a text box then check the checkbox to fill the contact date field.  I need help tying the text box to the check box and the contact date field.  I've tried using an if statement, but either my formatting is wrong or this is the wrong approach.  Any help that you can provide would be greatly appreciated.

Linnet

Answer 1

It looks like the forum's software is dropping the space before the line continuation underscore. You example is missing the spaces, too.

So it does.  I've never known that happen before.  Let's try again and see what happens.  The spaces are definitely in the following as I'm seeing it in the reply window:

strSQL = "UPDATE NewHireTable " _

& "Set [NewHireTable].[MeetingDate] = #" & Format(Text65, "mm-dd-yy") & "# " _

& "Where [NewHireTable].[MeetingBool] = True"

and:

trSQL = "UPDATE NewHireTable " _

& "Set MeetingBool = False"

Answer 2

I have tried several variations of what you have asked me to do.  Each time I get syntax errors OR Expected : line number of label or statement or end of statement - & highlighted OR invalid character - highlighting the underscore.  Here is my modified code; the bold items are highlighted as if they are in error.  Any help is greatly appreciated.  Thanks,  Linnet

Private Sub Check75_Click()

Const MESSAGETEXT = "Date missing or invalid."

Me.Dirty = False

'ensure text box has a value and is a valid date

If IsDate(Me.Text65) Then

'build SQL statement to update table

strSQL = "UPDATE NewHireTable"_& "Set [NewHireTable].[MeetingDate] = #" & Format(Text65, "mm-dd-yy") & "#"_& "Where [NewHireTable].[MeetingBool] = True"'execute SQL statement

CurrentDb.Execute strSQL, dbFailOnError

'build SQL statement to set Check56 column back to False

'in all rows ready for next update

strSQL = "UPDATE NewHireTable"_& "Set MeetingBool = False"'execute SQL statement

CurrentDb.Execute strSQL, dbFailOnError

Else

MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"

End If

End Sub

Answer 3

You have to put the entire SQL statement in quotes and concatenate the value of the text box like this:

strSQL = "UPDATE NewHireTable " _

& "Set [NewHireTable].[MeetingDate] = #" & Format(Text65, "mm-dd-yy") & "# " _

& "Where [NewHireTable].[MeetingBool] = True"

Answer 4

Thank you for your response, I have found this information very helpful.  I am still having problems getting this to work.  When I try to test, by clicking on the checkbox in the form, I am getting a compile error - Sub or Function notdefined and the Where is highlighted.  Can you assist me further?  Here is my code and additional information.  Thank you for your assistance.  Linnet

I have my form linked directly to my table (previously it was linked  to a query).  The form is set up to filter on MeetingDate is null.  This is working.

Table Name = NewHireTable

Field Names = MeetingDate

                      MeetingBool - this is control source for my checkbox in the form - check75

Header,unbound textboc = text65

Private Sub Check75_Click()

Const MESSAGETEXT = "Date missing or invalid."

Me.Dirty = False

'ensure text box has a value and is a valid date

If IsDate(Me.Text65) Then

  'build SQL statement to update table

  strSQL = "UPDATE NewHireTable"

      Set [NewHireTable].[MeetingDate] = "#" & Format(Text65, "mm-dd-yy") & "#"

      Where [NewHireTable].[MeetingBool] = True

  'execute SQL statement

  CurrentDb.Execute strSQL, dbFailOnError

  'build SQL statement to set Check56 column back to False

  'in all rows ready for next update

  strSQL = "UPDATE NewHireTable"

    Set MeetingBool = False

  'execute SQL statement

  CurrentDb.Execute strSQL, dbFailOnError

Else

  MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"

End If

End Sub

Answer 5

Let's assume that the table is called MyTable, the date column is called MyDate and the Boolean (Yes/No) column to which the check box is bound is called MyBool.

In the form the text box to enter the date should be unbound and in the form's header of footer section.  Let's assume it's named txtMyDate.  The button to update the checked rows can also be in the header or footer.  In the button's Click event procedure the code would be:

Const MESSAGETEXT = "Date missing or invalid."

Dim strSQL AS String

' make sure current record in form is saved

Me.Dirty = False

' ensure text box has a value and is a valid date

If IsDate(Me.txtMyDate) Then

    ' build SQL statement to update table

    strSQL = "UPDATE MyTable " & _

        "SET MyDate = #" & Format(txtMydate,"yyyy-mm-dd") & "# " & _

        "WHERE MyBool = TRUE"

    'execute SQL statement

    CurrentDb.Execute strSQL, dbFailOnError

    ' build SQL statement to set MyBool column back to False

    ' in all rows ready for next update

    strSQL = "UPDATE MyTable " & _

        "SET  MyBool = FALSE"

    'execute SQL statement

    CurrentDb.Execute strSQL, dbFailOnError

Else

    MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"

End If

Note the use of the ISO Standard for date notation of YYYY-MM-DD to ensure international unambiguity.  You'd enter the date in the text box in your usual regional date format of course.  If you format the text box as 'Short Date' this will cause the date picker to pop up once the text box receives focus.

Remember that if any control, table or column name includes spaces or other special characters (not advised) it must be wrapped in square brackets in the code [like this].