Editing Record from Form to Table

[MHamid]

Hello,

I created a User Form in Access 2010 that will add, delete, and clear records without any issues.
Now I am trying to figure out how to edit a record that is on the table.

Using the Data Entry Form, the user is able to add the following record criteria to a table:
SID
Start Date
End Date
CaseNo
Entity Count
Referenced Entity #
Regional Assist
Vendor Assist
Fully Referenced
LOB
Comments

However, the End Date, Regional Assist, Vendor Assist, and Comments can be left blank if no data is needed for the case.
I am trying to allow an added record to be edited using the SID and CaseNo to find the record, but I can't seem to get this work. What am I missing or doing wrong?

Private Sub EditData_Click()
    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("MasterThroughput")
    
    With "MasterThroughput"
    Me.EndDate = .Fields("End Date")
    Me.AddRecord.Caption = "Update"
    Me.EditData.Enabled = False
    End With
    
End Sub

Thank you,
Miriam

 

[MHamid]

I did a really quick research on the error code and I found something that I was able to use to get the code to work.

Private Sub EditData_Click()
    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("MasterThroughput")
    
    With rs
        .Edit
        .Fields("End Date") = Me.EndDate
        .Update
        
        .Edit
        .Fields("Comments") = Me.Comments
        .Update
        
        MsgBox "Record Updated Successfully", vbInformation, "SUCCESS!"
    End With
    
End Sub

Now I just have to figure out how to tweak this code to provide a message if the case number does not exists and to skip the edit if the field is left blank.
Another question though, would I have to the below for each field that the user may need to update?

        .Edit
        .Fields("End Date") = Me.EndDate
        .Update

 

[Micron]

I'm beginning to wonder where my head is on this one. Of course, you have to put the recordset in "edit" mode before trying to edit it, and I totally blew that one.

would I have to the below for each field that the user may need to update?

No, what you're updating is the recordset (With rs.), not an individual field.

...how to tweak this code to provide a message if the case number does not exists

First you mentioned this, so I'm not sure how you want the process to go if it doesn't exist. If it's part of the record edit, then you have options: bypass it or substitute another value. I will take a stab at substitution in the suggested code below and assume it is text and not a number. If you want to bypass, use an If Then statement to direct execution around this field update. As usual, I'm at the disadvantage that I can't really test my code since I don't have your db. That's why it's called air code.

Private Sub EditData_Click()
Dim rs As Recordset
Dim varCaseNo as Variant

On Error GoTo errHandler 'should have error handling in this
If IsNull(Me.CaseNo) or Me.CaseNo = "" Then 
  varCaseNo = Null
Else
  varCaseNo = Me.CaseNo
End If

Set rs = CurrentDb.OpenRecordset("MasterThroughput")
With rs
   .Edit
   .Fields("End Date") = Me.EndDate
   .Fields("Comments") = Me.Comments
   .Fields("CaseNo") = NZ(varCaseNo,"Missing")
   .Update
End With
MsgBox "Record Updated Successfully", vbInformation, "SUCCESS!"

exitHere:
rs.Close 'should close recordset and destroy the object
Set rs = Nothing
Exit Sub

errHandler:
'present info on any error. Construct Select Case block to handle multiple possibilities if necessary
msgbox "Error " & Err.Number & ": " & Err.Description 
Resume exitHere
End Sub

After all this, I'm wondering why you just don't run an update query that gets the data from a form.

 

[MHamid]

Hello,

I tried out your code and it updates the current record even if the case number does not match.
What I need it to do is to bring up a message stating "Case does not exists". If the case does not exist, then the code will stop and do nothing else.
However, if the case does exist, then we can continue on with update/edit process.
If a comment exists and no new comment is needs to be added, then do nothing. However, if a comment already exists and additional comment is needed, can the code just add additional comments instead of deleting what was in the original comment?


I am new to access/coding and I wouldn't know how to begin running an update query that would get the data from a form with a click of a button.
I am not opposed to this method though.


Thank you,
Miriam

 

[xenou]

IF the form is bound to the data table, you edit it simply by opening the form, navigating to the record, and entering the new/updated data.

 

[MHamid]

Hello,

I don't want to give the user access to be able to manipulate the table itself.
I have trust issues and I don't trust that they will update the record they need to update only.

 

[Micron]

Read my post #12 and you will see that I made assumptions. You did not confirm or dispel those assumptions, so if you don't address what I don't know, you can't expect success unless I get real lucky with guessing. This part is supposed to do what you ask, but if it doesn't work, I have to think it's because CaseNo is not on the form (hence referring to it that way is useless), or your control is not called CaseNo or something like that.

If IsNull(Me.CaseNo) or Me.CaseNo = "" Then 
  varCaseNo = Null
Else
  varCaseNo = Me.CaseNo
End If

 

[MHamid]

Hello,

the he column on the table is labeled as CaseNo and on the form it is labeled as Case. Does that help?

 

[Micron]

Small point, but I think you are referring to the names of these things and not their captions or actual labels on a form, so you should refer to these "labels" as names.
If your form textbox is named Case, then change CaseNo in the code to whatever the name of the case number control is and see what happens. I'm surprised that what I wrote did not generate an error with respect to Me.CaseNo. You must not have Option Explicit turned on - that's not good.