Solved Same trick of vba code to Update data except primary Key field (1 Viewer)

[Ashfaque]

My Below code is working fine which is behind SAVE button. For Next loop reduced the efforts to save data field individually.

Set rst = CurrentDb.OpenRecordset("Select * from T_ProjectBaseContracts where ProjNum='" & ProjNum & "'")
rst.AddNew
For Each fld In rst.Fields
rst(fld.name) = Me(fld.name)
Next fld
rst.Update

I like to use same code with slight changes to UPDATE the record. This time I dont want to same one field i.e. ProjNum (it is primary key and already existing in record) so that after clicking Update btn I dont need to write such a lengthy code like Rst!field=Me!Field for each of my fields on form.

Is this possible?

I want my code to check if ProjNum is same which I have on my current form then other than that rest of the field data should be updated

If rst!ProjNum = Forms!F_Project!ProjNum Then update next field only...something like...

 

[Ranman256]

why are you using code? use a query.

 

[Ashfaque]

Thanks Ranman256

I need to update all the fields except ProjNum which is primary key so thought that I should use the shortest code. I think the Update Query Also need to select all the fields on the forms except primary key field so it is also some lenthy sql work.

 

[arnelgp]

sowhat same as your New record:

Set rst = CurrentDb.OpenRecordset("Select * from T_ProjectBaseContracts where ProjNum='" & ProjNum & "'")
If Not (rst.BOF And rst.EOF) Then
rst.Edit
For Each fld In rst.Fields
If fld.Name <> "ProjNum" Then _
rst(fld.name) = Me(fld.name)
Next fld
rst.Update
End If

 

[The_Doc_Man]

I wonder why you don't simply bind the fields to your form. Then saving and updating become automatic. That loop you showed us APPEARS to be updating a recordset with fields named the same as the fields on the form. At least, that is what it looks like. And Access does that with NO code if you just bind the recordset.

If you don't want to update the PK, just don't do it. There are all sorts of ways to make that work but I don't see why you need any code at all for the "other fields." At MOST, you could (in form_current routine) lock the PK if it isn't blank. In which case there is otherwise no difference between a new record to be saved and an extant record to be updated.

I guess what is confusting to me and to Ranman256 is that you are going out of your way to do something that should be darned near automatic. So we are perhaps missing something.

 

[Ashfaque]

You are correct The_Doc_Man
The bound forms might makes it very easy.

But my all forms are unbound and since beginning I connect them to tbls thru code and we dont know which field has been changed by the user as Project Form has more than 80 fields. And I dont want to write them fieldname=withnewUpdatedata 10s of times.

Thanks for your precious advice.

And thanks Arnel, it helped me a lot. I was doing IF Rst!projNum= Forms!_F_Project!ProjNum Then.... which did not work. But your code line worked perfectly. Thanks,

Regards,

 

[The_Doc_Man]

But my all forms are unbound and since beginning I connect them to tbls thru code

In so doing, you essentially disable 80-90% of the power of Access to do things automatically. You could build rudimentary forms via Form Wizards and tailor them more easily. The forms built that way would be more reliable since Access would get the recordset/control linkage correct every time (unless you used a reserved word for a field or control name). And, if I misunderstood what I read, I'm sorry, but it looks like you already have names for form controls to match the recordset fields anyway based on the line:

rst(fld.name) = Me(fld.name)

If the form matches the recordset that closely, field names matching control names, where is there an advantage to NOT binding the form to the table? Because for that little line of code to work on more than one form, it would mean that EVERY FORM already is individually customized with field names to match the corresponding recordset names.

Your comment about "we don't know which field has been changed by the user" is again clouding the issue. The code you showed us was not trying to do an audit-log type of operation, so in that sense, you don't show us that you CARE about which field was updated. That code just does a recordset update from the matching form control. But ACCESS instantly knows which field has been changed by the user and always manages that correctly. I am still in the dark about why you are making A LOT of work for yourself. And trust me, you are.

I don't understand the down-side of letting Access do this for you in a more traditional sense. If I want to help you do your job better, I have to at least give you a LITTLE shove towards using more of Access's internal automation. Just a LITTLE nudge, anyway. Not trying to be oppressive, but I see something awry in a post where someone has asked for help. So I'm trying to help.