How to Update Table from a Form using cmd button

Question

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.

george_hepworth · Answer

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 · Answer

George_Hepworth&nbsp;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 ?&nbsp;********Add New***********&nbsp;Private Sub svprt_cmd_Click()On Error GoTo Error_HandlerDim db As DatabaseDim rec As RecordsetSet db = CurrentDbSet rec = db.OpenRecordset("Select * from SawPartNumber")rec.AddNewrec("Part_ID") = Me.prtnum_cborec("Rev") = Me.rev_txtrec("Tool Type") = Me.tool_cborec("Tool Diameter") = Me.TDia_txtrec("Wing count") = Me.tip_cnt_txtrec("Saw Style") = Me.styl_cborec("Kerf") = Me.kerf_txtrec("Tip style") = Me.tips_cborec("Tip grade") = Me.tipg_txtrec("Hook") = Me.hook_txtrec("OD cl") = Me.odcl_txtrec("Radial") = Me.radin_txtrec("Back") = Me.backin_txtrec("Drop") = Me.drop_txtrec("Top Bvl") = Me.tpbvl_txtrec("Cnr Brk") = Me.cnrbk_txtrec("K Lnd") = Me.klnd_txtrec("Tooth Style Count") = Me.tscnt_txtrec("Special Notes") = Me.prtnts_txtrec("Tooth style") = Me.toos_txtrec.UpdateSet rec = NothingSet db = NothingError_Handler_Exit:On Error Resume NextExit SubError_Handler:MsgBox "Part already exists"Resume Error_Handler_ExitEnd Sub&nbsp;*******EDIT / UPDATE***********&nbsp;Private Sub upprt_cmd_Click()Dim db As DatabaseDim rec As RecordsetSet db = CurrentDbSet rec = db.OpenRecordset("Select *FROM SawPartNumber")rec.Editrec("Rev") = Me.rev_txtrec("Tool Type") = Me.tool_cborec("Tool Diameter") = Me.TDia_txtrec("Wing count") = Me.tip_cnt_txtrec("Saw Style") = Me.styl_cborec("Kerf") = Me.kerf_txtrec("Tip style") = Me.tips_cborec("Tip grade") = Me.tipg_txtrec("Hook") = Me.hook_txtrec("OD cl") = Me.odcl_txtrec("Radial") = Me.radin_txtrec("Back") = Me.backin_txtrec("Drop") = Me.drop_txtrec("Top Bvl") = Me.tpbvl_txtrec("Cnr Brk") = Me.cnrbk_txtrec("K Lnd") = Me.klnd_txtrec("Tooth Style Count") = Me.tscnt_txtrec("Special Notes") = Me.prtnts_txtrec("Tooth style") = Me.toos_txtrec.UpdateSet rec = NothingSet db = NothingEnd Sub

george_hepworth · Answer

JayKeebs2423&nbsp;&nbsp;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.&nbsp;Set rec = db.OpenRecordset("Select * FROM SawPartNumber WHERE Part_ID = " &amp; Me.prtnum_cbo)&nbsp;&nbsp;

jaykeebs2423 · Answer

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

george_hepworth · Answer

JayKeebs2423&nbsp;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&nbsp;tables raises some concerns.&nbsp;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.&nbsp;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.&nbsp;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&nbsp; left, is identified with an Index of 0, and the second column is Index 1, and so on.&nbsp;So, in your VBA, to refer to the bound column, which again I assume is actually the AutoNumber PK, you'd use&nbsp;Me.prtnum_cbo.Column(0) or just plain&nbsp;Me.prtnum_cbo&nbsp;&nbsp;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.&nbsp;Sorting that out should correct the problem.&nbsp;

Forum Discussion

How to Update Table from a Form using cmd button

Share

Resources