Updating an Existing Record Via an Unbound Form

[injanib]

Hi all,



I have a continuous form. I use an unbound form to enter record in the underlying table of this continuous form and then requery the form. Each row on the continuous form also has a "delete" button and "modify" button. The delete button simply deletes the record in that row. What I want to do is to use the modify button to populate the record in that row on the unbound form I use to write record in the underlying table. Basically, I click on the "modify" button on a row, and it opens up the unbound form with the record in its respective row populated. I make the changes and then hit save. It writes the data back in the underlying table and requery the form. That way, the primary key doesn't change, only the data is modified.

Is it possible?

Thanks in advance

[nicknameoscar]

If I am understanding the question correctly then, yes, it is possible. You would have to make sure the record you want to modify is selected (record selector has black triangle or you otherwise know which record is the selected record).

The below assumes the unbound form is already open:

Private Sub cmdModify_Click()

Dim frm as Form
Set frm = frmUnboundForm

frm.txtObject1 = Me.txtObject1
frm.txtObject2 = Me.txtObject2
etc.
.
.
.
End Sub

If the unbound form isn't open then perhaps something like this:

Private Sub cmdModify_Click()

DoCmd.OpenForm "frmUnboundForm"

(insert code from above)

DoCmd.Close acform, "frmBoundContiuousForm"

End Sub

[injanib]

That was too easy. I can now populate the information. Just need to run an update query to write back the changes.

Thanks allot.

[June7]

Glad you have solution but not understanding why using an unbound form. Why go through all this manipulation when a bound form could be opened to the existing record and edits can be accomplished then the calling form can be refreshed. How would this change the primary key?

[blueraincoat]

Private Sub Command8_Click()
Dim mySQL As String
    mySQL = "UPDATE tbl SET tbl.C='" & Me.C & "',tbl.D='" & Me.D & "'"
    mySQL = mySQL & " Where tbl.A='" & Me.A & "'"
        Debug.Print mySQL
        CurrentDb.Execute mySQL, dbFailOnError
End Sub