How to Update Table from a Form using cmd button

[JayKeebs2423]

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 loaded into form, from the table SawPartNumber.

[#2]

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?

[#3]

@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

[#4]

@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)

[#5]

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'."

[#6]

@JayKeebs2423 

The form is bound to another table already. I'll leave that for now, although any process that involves putting records from a form into two different tables raises some concerns.

There are a couple of ways this could be going wrong, but the most likely is that the combo box is bound to the wrong field in the rowsource table.

Most of the time, the Primary Key for Access tables is an AutoNumber, which is Long Integer datatype.

The table you are using probably has such a field, called "Part_ID". However, it may be the case that your table actually has used the text value, such as "Test blade" as the field designated as the Primary Key. I'm going to start by assuming that you have a typical table, i.e. one with an Autonumber Primary Key.

If that is the case, the combo box should have two columns (or possibly more). The first column on the left is by default the bound column. It is the column in which the Part_ID is used and, I assume, it is the AutoNumber PK. Your combo box possibly is bound to the second column, though, and that would be the one intended to DISPLAY the text value for that PK. If so, that would result in the error you see.

The tricky part is the way Access refers to columns in combo and list boxes. Column 1, the first column on the  left, is identified with an Index of 0, and the second column is Index 1, and so on.

So, in your VBA, to refer to the bound column, which again I assume is actually the AutoNumber PK, you'd use Me.prtnum_cbo.Column(0) or just plain Me.prtnum_cbo 

The fact that you are getting a datatype mismatch means either that you did not include the actual Primary Key (Part_ID) in the combo box, or that it is not the first column, or that the combo box is bound to a different column, i.e. the second or third column which DISPLAYS the text value.

Sorting that out should correct the problem.

[#7]

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