Forum Discussion

JayKeebs2423's avatar
JayKeebs2423
Copper Contributor
Jan 27, 2022

How to Update Table from a Form using cmd button

Okay i would like to be able to update part attributes table from a form. The Part_ID in table (primary key) is listed in combo box in form (prtnum_cbo) this carries a list of attributes that can be ...
access
George_Hepworth's avatar
George_Hepworth
Silver Contributor
Jan 27, 2022
Please explain what the problem is, exactly. Remember we can only see what you include in your post. The fewer the details, the harder it is to guess what is needed.

You want to update a table in a form. Bind the table you want to update to that form. Add the fields in the table to the form as bound controls. Then you can update those fields using those controls.

What, precisely, is missing in your attempt to create this form?
  • JayKeebs2423's avatar
    JayKeebs2423
    Copper Contributor
    Jan 27, 2022

    George_Hepworth 

    I am using Code to on a Cmd button(upprt_cmd) to update or edit 19 feilds in a table (SawPartNumber) that is being used in a combo box (prtnum_cbo) in the Form. Below is code I am using to "Add New" record to the table and code i attempted to use to "Edit". The "Edit" option is not linked to specific record i want to edit or update therefore it is updating the 1st record in the table. how can i fix this ?

     

    ********Add New***********

     

    Private Sub svprt_cmd_Click()
    On Error GoTo Error_Handler

    Dim db As Database
    Dim rec As Recordset

    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from SawPartNumber")

    rec.AddNew
    rec("Part_ID") = Me.prtnum_cbo
    rec("Rev") = Me.rev_txt
    rec("Tool Type") = Me.tool_cbo
    rec("Tool Diameter") = Me.TDia_txt
    rec("Wing count") = Me.tip_cnt_txt
    rec("Saw Style") = Me.styl_cbo
    rec("Kerf") = Me.kerf_txt
    rec("Tip style") = Me.tips_cbo
    rec("Tip grade") = Me.tipg_txt
    rec("Hook") = Me.hook_txt
    rec("OD cl") = Me.odcl_txt
    rec("Radial") = Me.radin_txt
    rec("Back") = Me.backin_txt
    rec("Drop") = Me.drop_txt
    rec("Top Bvl") = Me.tpbvl_txt
    rec("Cnr Brk") = Me.cnrbk_txt
    rec("K Lnd") = Me.klnd_txt
    rec("Tooth Style Count") = Me.tscnt_txt
    rec("Special Notes") = Me.prtnts_txt
    rec("Tooth style") = Me.toos_txt

    rec.Update

    Set rec = Nothing
    Set db = Nothing
    Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

    Error_Handler:
    MsgBox "Part already exists"

    Resume Error_Handler_Exit

    End Sub

     

    *******EDIT / UPDATE***********

     

    Private Sub upprt_cmd_Click()

    Dim db As Database
    Dim rec As Recordset

    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select *FROM SawPartNumber")

    rec.Edit

    rec("Rev") = Me.rev_txt
    rec("Tool Type") = Me.tool_cbo
    rec("Tool Diameter") = Me.TDia_txt
    rec("Wing count") = Me.tip_cnt_txt
    rec("Saw Style") = Me.styl_cbo
    rec("Kerf") = Me.kerf_txt
    rec("Tip style") = Me.tips_cbo
    rec("Tip grade") = Me.tipg_txt
    rec("Hook") = Me.hook_txt
    rec("OD cl") = Me.odcl_txt
    rec("Radial") = Me.radin_txt
    rec("Back") = Me.backin_txt
    rec("Drop") = Me.drop_txt
    rec("Top Bvl") = Me.tpbvl_txt
    rec("Cnr Brk") = Me.cnrbk_txt
    rec("K Lnd") = Me.klnd_txt
    rec("Tooth Style Count") = Me.tscnt_txt
    rec("Special Notes") = Me.prtnts_txt
    rec("Tooth style") = Me.toos_txt

    rec.Update

    Set rec = Nothing
    Set db = Nothing

    End Sub

    • George_Hepworth's avatar
      George_Hepworth
      Silver Contributor
      Jan 27, 2022

      JayKeebs2423 

       

      Okay, you actually have an unbound form, then. That's the hard way. Why not use a bound form?

      That said, your "edit" version omits the part where you'd have to select WHICH record to update. Therefore, it will always edit the first record in the table.

      You would need to use the value selected in the combo box to FIND the correct record in the recordset and then update it. What I'd do, if I was forced to go about this the hard way with an unbound form, would be to simply modify the way you open the recordset.

       

      Set rec = db.OpenRecordset("Select * FROM SawPartNumber WHERE Part_ID = " & Me.prtnum_cbo)

       

       

      • JayKeebs2423's avatar
        JayKeebs2423
        Copper Contributor
        Jan 27, 2022
        The form is bound to another table already.
        Okay I believe I am getting an error now due to Part_ID not = a number. Part_ID needs to be text.
        It says:
        "Syntax error (missing operator) in query expression 'Part_ID = Test blade'."

Resources