Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Jun 2013
    Posts
    17

    Unanswered: Add & Update Records With Check box

    My database tracks machines and maintenance/repairs. Would like to be able to add new records to "ShopWork" table with current date and text in the "WorkDone" field based on check boxes on a form. This is currently entered via a text box.

    I add a new record for each task, keyed to the machine ID#. I use standard text for tasks" "Replace Fuel Filter", Replace Belt", etc.

    Hope this enough information. Your help is appreciated.

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    You can use a query and VBA to add your record:
    Code:
    Dim WorkText as String
    If Me.CheckBox1 = -1 Then WorkText = "Replace Fuel Filter"
    If Me.CheckBox2 = -1 Then 
         If Isnull(WorkText) Then
         WorkText = "Replace Belt"
         Else
         WorkText = WorkText & vbnewline & "Replace Belt"
         End If
    End If
    'etc. repeat for all your checkboxes
    DoCmd. runSQL "INSERT INTO ShopWork (CurDate,WorkDone ) VALUES (Date(),'" & WorkText & "');"

  3. #3
    Join Date
    Jun 2013
    Posts
    17
    The Do.Cmd line yields a syntax error in the INSERT statement

    The ShopWork table has these field names:
    JobID (autonumber, Pkey); Ynumber (machine ID#, selected from combobox); WorkDone (textbox); Date(formatted textbox, default to current date); Notes(textbox)

    I have a "save" button. On Click eventprocedure runs this line:
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    I placed the DoCmd****nSQL line above it.

    My limited VBA knowledge is self-taught. Thanks for your help.

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    If any other fields are required they would need to be added:

    Code:
    DoCmd****nSQL "INSERT INTO ShopWork (CurDate,WorkDone,Ynumber,Notes ) VALUES (Date(),'" & WorkText & "'," & Ynumber & ",'" & "');"
    If you still get errors could you attach your DB so I can take a look? Thanks.

  5. #5
    Join Date
    Jun 2013
    Posts
    17
    I changed the field name "Date" to "Workdate" and had to make corrections to forms & data access pages. Now everything is back together again.

    I'm making changes to the SAVE button EventProcedure OnClick statement.

    I entered these lines:
    DoCmd****nSQL "INSERT INTO ShopWork (WorkDate,WorkDone ) VALUES (Date(),'" & WorkText & "');"
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    The result was 1 new record with the machine id# "Y001" in the Ynumber field,
    nothing in the WorkDone field, and the date in the WorkDate field and another new record with nothing in the Ynumbefr field, "Replace Fuel Filter" in the WorkDone field, and the date in the WorkDate field
    ---------------------
    After reading your last reply, I deleted this line
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    And changed the other to:
    DoCmd****nSQL "INSERT INTO ShopWork (WorkDate,WorkDone,Ynumber,Notes) VALUES (Date(),'" & WorkText & "'," & Ynumber & ",'" & "');"

    and I get a syntax error.

    I'm making progress and learning along the way and appreciate the education.

  6. #6
    Join Date
    Jan 2005
    Posts
    146
    Initially you have extra junk in the statement since you are not having any value for Notes:
    DoCmd****nSQL "INSERT INTO ShopWork (WorkDate,WorkDone,Ynumber,Notes) VALUES (Date(),'" & WorkText & "'," & Ynumber & ",'" & "');"
    should be:
    Code:
    DoCmd****nSQL "INSERT INTO ShopWork (WorkDate,WorkDone,Ynumber) VALUES (Date(),'" & WorkText & "'," & Ynumber & ");"
    Also you need to be aware when it is a text field vs. a numberic or date field. Text fields need to be surrounded by single quotes.

  7. #7
    Join Date
    Jun 2013
    Posts
    17
    I added single quotes around " & Ynumber & " because the machine id# is Y001...Y175. This is picked from a combo box.
    And Success! sort of. The syntax error is corrected but I still get 2 records added as
    I described in my 15:55 post.

  8. #8
    Join Date
    Jan 2005
    Posts
    146
    Is the form also saving the same record to the ShopWork table?

  9. #9
    Join Date
    Jun 2013
    Posts
    17
    yes. It saves everything to the ShopWork table.

  10. #10
    Join Date
    Jan 2005
    Posts
    146
    So it looks like you are saving the record twice, once in the form and once in the VBA code. I guess at this point going back to original question

    My database tracks machines and maintenance/repairs. Would like to be able to add new records to "ShopWork" table with current date and text in the "WorkDone" field based on check boxes on a form. This is currently entered via a text box.

    I add a new record for each task, keyed to the machine ID#. I use standard text for tasks" "Replace Fuel Filter", Replace Belt", etc.

    Hope this enough information. Your help is appreciated.
    I was mistaken in thinking the form was unbound. So, what it sounds like is you only wanted a solution to how to create the WorkDone text. The first part of the code I gave with a slight modification should provide a solution.

    Code:
    If Me.CheckBox1 = -1 Then Me.WorkDone = "Replace Fuel Filter"
    If Me.CheckBox2 = -1 Then 
         If Isnull(Me.WorkDone) Then
         Me.WorkDone = "Replace Belt"
         Else
         Me.WorkDone = Me.WorkDone & vbnewline & "Replace Belt"
         End If
    End If
    'repeat for each checkbox to verify

  11. #11
    Join Date
    Jun 2013
    Posts
    17
    I think I see what you are doing. Me.WorkDone will be bound to & update the WorkDone field in the table. I'm not sure how this will work if I use the textbox not a checkbox. Currently I enter text in the textbox bound to WorkDone to update the field. Which happens when I click SAVE. Now I have to type & click many times to cover the different tasks. Each task becomes a seperate record.

    WorkDone is never null. So if no boxes are checked (there may be 5 or 6) WorkDone is the text entered.

  12. #12
    Join Date
    Jan 2005
    Posts
    146
    What I think you should do is in the After Update event for each check box add:

    Code:
    If Me.CheckBox = -1 Then 'box checked
         If IsNull(Me.WorkDone) Then 
         Me.WorkDone = "Whatever This CheckBox represents"
         Else
         Me.WorkDone = Me.WorkDone & ", Whatever This CheckBox represents"
         End If
    Else 'If it was orginally checked then remove this text
         If Instr(Me.WorkDone,", Whatever This CheckBox represents") Then
             Replace(Me.WorkDone,", Whatever This CheckBox represents","")
         ElseIf Instr(Me.WorkDone,"Whatever This CheckBox represents" Then
             Replace(Me.WorkDone,"Whatever This CheckBox represents","")
    End If
    This will automatically update WorkDone upon checking the box. See if that might help out.

  13. #13
    Join Date
    Jun 2013
    Posts
    17
    When I add these lines, I get a compile error: Expected: list separator

  14. #14
    Join Date
    Jan 2005
    Posts
    146
    Could you post your code? Thanks.

  15. #15
    Join Date
    Jun 2013
    Posts
    17
    I apologize for the delay. Besides writing and maintaining the database, I actually replace all the filters and belts on our equipment.

    This is the code from the SAVE button on my test database as it stands now:

    Private Sub Save_Click()
    On Error GoTo Err_Save_Click

    Dim WorkText As String
    If Me.CheckBox1 = -1 Then Me.WorkDone = "Replace Fuel Filter"
    ' If Me.CheckBox2 = -1 Then
    If IsNull(Me.WorkDone) Then
    WorkText = "Replace Belt"
    Else
    WorkText = WorkText & vbNewLine & "Replace Belt"
    End If
    If Me.CheckBox = -1 Then 'box checked
    If IsNull(Me.WorkDone) Then
    Me.WorkDone = "Whatever This CheckBox represents"
    Else
    Me.WorkDone = Me.WorkDone & ", Whatever This CheckBox represents"
    End If
    Else 'If it was orginally checked then remove this text
    If Instr(Me.WorkDone, "Whatever This CheckBox represents) Then
    Replace(Me.WorkDone, "Whatever This CheckBox represents","")
    ElseIf InStr(Me.WorkDone, "Whatever This CheckBox represents") Then
    Replace(Me.WorkDone,"Whatever This CheckBox represents","")
    End If
    ' End If
    'etc. repeat for all your checkboxes
    ' DoCmd****nSQL "INSERT INTO ShopWork (WorkDate,WorkDone,Ynumber) VALUES (Date(),'" & WorkText & "'," & Ynumber & ",'" & Notes & "');"
    DoCmd****nSQL "INSERT INTO ShopWork (WorkDate,WorkDone,Ynumber) VALUES (Date(),'" & WorkText & "','" & Ynumber & "');"

    ' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Exit_Save_Click:
    Exit Sub

    Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click

    End Sub

    ======================================
    This is the SAVE button Event priocedure from my production db: It was generated by the wizard:

    Private Sub Save_Click()
    On Error GoTo Err_Save_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Exit_Save_Click:
    Exit Sub

    Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •