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

 

[Micron]

"can't seem to get this work" doesn't help too much. Getting an error? Form opens but not to the desired record? It does but you can't edit?

I will guess that you need to open this form as you currently are in order to create the record but have a way for the user to elect to open to edit existing.
Forms have properties that either do or don't allow edits, deletions or adding as well as the data entry property. You need to have these set correctly in order to switch between allowing/disallowing certain form capabilities.

 

[MHamid]

Hello,

The error I am getting with the code is 'Compile error: With object must be user-defined type, Object, or Variant".

I don't want the user to open the table file to manually edit the record.
I want a code that will do the work for the user. The code should search for the case number and update any fields necessary from the user form. The code will be embedded in a button that the user will click from the Data Entry Form.

Is this even possible?

Thank you,
Miriam

 

[Micron]

I don't want the user to open the table file to manually edit the record.

Not sure if you're implying I said otherwise or are just letting us know you know that is the proper way to do things. Before going any further with how the form opens (edit mode or whatever) it seems the error has nothing to do with that. It means you are trying to use a With Block on something that doesn't allow it - like a user defined type of variable. Post the section of code where the error occurs (the With block) and identify what the offending variable is; e.g.

With myVariable
  .visible = false
  .whatever =...
End With 
'**myVariable is a (describe the variable a bit).

 

[MHamid]

Hello,

I was just informing.

It doesn't specify exactly what the offending variable is.

It just highlights in yellow:

Private Sub EditData_Click()

It highlights in blue the word "With"

    With "MasterThroughput"
    Me.EndDate = .Fields("End Date")
    Me.AddRecord.Caption = "Update"
    Me.EditData.Enabled = False
    End With

 

[Micron]

and identify what the offending variable is; e.g.

After going back to your original post, I'm deducing that MasterThroughput is the name of your recordset. Sorry I didn't catch that right away - so it is an object after all.

The syntax isn't right for a with block. A with block is meant to replace something like this

Forms!frmDataEntry.txtFname = "sam"
Forms!frmDataEntry.txtLname = "smith"
Forms!frmDataEntry.txtGender = "male"
Forms!frmDataEntry.txtAge = 25

with something like this

With Forms!frmDataEntry
 .txtFname = "sam"
 .txtLname = "smith"
 .txtGender = "male"
 .txtAge = 25
End With

I have moved the dot (.) and control name so each is on their own line, but the sequence is interpreted exactly as before. Then you assign the property or value to the child or the parent as the case warrants. txtFname is a "child" of the form, and both of these are objects, not variables. What you have is sort of insinuating that Me. is a child of Masterthroughput, or you are mixing the form and recordset in the same block, which you cannot do. What you need is

With "MasterThroughput"
  .Fields("End Date") = Me.EndDate
  .Fields(StartDate") = Me.StartDate (I made this up as an example but would not use a block for just one line)
End with

With Me (I have not always had luck using Me in a with block. In those cases, I used the full Forms! reference.
  .AddRecord.Caption = "Update"
  .EditData.Enabled = False
End With

 

[MHamid]

Hello,

I changed the code and I am still getting the same error code.

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

Is there an easier way or an easier code to accomplish this?
I've looked online and I found another code that I can possibly use, but I can't make much sense of it.

Private Sub cmdMovePosition_Click()
    Dim dbVideoCollection As Object
    Dim rstVideos As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set dbVideoCollection = CurrentDb
    Set rstVideos = dbVideoCollection.OpenRecordset("Videos")
    Set fldColumns = rstVideos.Fields

    ' Scan the records from beginning to each
    While Not rstVideos.EOF
        ' Check the current column
        For Each fldEnumerator In rstVideos.Fields
            ' If the column is named Title
            If fldEnumerator.Name = "Title" Then
                ' If the title of the current record is "Congo"
                If fldEnumerator.Value = "Congo" Then
                    ' then change its value
                    rstVideos.Edit
                    rstVideos("Director").Value = "Frank Marshall"
                    rstVideos.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstVideos.MoveNext
    Wend
End Sub

I have attempted to tweak this code to no avail:

Private Sub EditData_Click()
    Dim dbReferencingThroughputTracker As Object
    Dim rstMasterThroughput As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set dbReferencingThroughputTracker = CurrentDb
    Set rstMasterThroughput = dbReferencingThroughputTracker.OpenRecordset("MasterThroughput")
    Set fldColumns = rstEnumerator.Fields

      While Not rstEnumerator.EOF
        For Each fldColumns In rstEnumerator.Fields
            If fldEnumerator.Name = "CaseNo" Then
                If fldEnumerator.Value = Me.Case Then
                    rstMasterThroughput.Edit
                    rstMasterThroughput("End Date").Value = "'" & Me.EndDate & "'"
                    rstMasterThroughput.Update
                End If
            End If
        Next
        rstMasterThroughput.MoveNext
    Wend
    
End Sub

I get an error message in the following line of the code (run-time error '424': Object required).

Set fldColumns = rstEnumerator.Fields

I'm sure I'm missing something or misunderstanding something here.

The table I need updated is called MasterThroughput
The database is named ReferencingThroughputTracker
The column I need updated is called End Date
However, I don't want to limit it to just editing this column, the user can edit any other column as needed but it is not necessary. So if the other field are left blank, then nothing just be updated, just the field that has data.
I hope that makes sense.

Thank you,
Miriam

 

[MHamid]

I just updated the tweaked code and I tested it and it did nothing at all. It did not edit the record and it did not give me an error message.

Private Sub EditData_Click()
    Dim dbReferencingThroughputTracker As Object
    Dim rstMasterThroughput As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set dbReferencingThroughputTracker = CurrentDb
    Set rstMasterThroughput = dbReferencingThroughputTracker.OpenRecordset("MasterThroughput")
    Set fldColumns = rstMasterThroughput.Fields

      While Not rstMasterThroughput.EOF
        For Each fldEnumerator In rstMasterThroughput.Fields
            If fldEnumerator.Name = "CaseNo" Then
                If fldEnumerator.Value = Me.Case Then
                    rstMasterThroughput.Edit
                    rstMasterThroughput("End Date").Value = "'" & Me.EndDate & "'"
                    rstMasterThroughput.Update
                End If
            End If
        Next
        rstMasterThroughput.MoveNext
    Wend
    
End Sub

 

[Micron]

So sorry, I make boo-boo - I did what I said you can't do. Change With "MasterThroughput" to With rs (no quotes).
rs is the object, not MasterThroughput!!

As for difficulty, it's not so hard, really. The difficulty lies in us being in two different places with one knowing (or should know) how to do something but without the benefit of the program and the other having the program but not the experience. Your error message should go away now.

 

[MHamid]

Hello,

I have made the update to the original code as you mentioned and now I am getting the error code "Run-time error '3020': Update or CancelUpdate without AddNew or Edit.

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