Update Record in one Form and Open in another form?

[thisisntwally]

I've created a button in formA which updates the table in question, but i'd like to have an option to update and open the same record in form2

(different datafields are being entered so i don't think the

DoCmd.openForm "form2", , , "[field]=" & Me.[field]

approach will work).

Is there anyway i can use the .bookmark property to open the new form?

The primary key is autogenerated on update, but as they're not a part of the input(1) or update(2) forms I'd like to avoid using it if possible

[thisisntwally]

Ok. Its much more cleare now where the legs are growing from.
The problem is so far in record update. The syntax seems to be Ok. Just a silly question concerning this: is the form bound to [tbl Modifications]?
The next one: is form "Update" bound to [tbl Modifications]?

Both tables are indeed bound to [tbl Modifications]. There has been some who question my logic (i don't believe its all that ab-normal hehheh) but for user simplicity's sake im rather stubborn about having two forms. I do have a functioning tabbed form which combines the two, so I could get to work on some other functions in the database, but I hate the d@mn thing.

[thisisntwally]

Both tables are indeed bound to [tbl Modifications]. There has been some who question my logic (i don't believe its all that ab-normal hehheh) but for user simplicity's sake im rather stubborn about having two forms. I do have a functioning tabbed form which combines the two, so I could get to work on some other functions in the database, but I hate the d@mn thing.

speaking of, i went ahead and changed the control source for "txtkey" on the all-inclusive tabbed form to =DMAX("key",[tbl Modifications]) + 1, and now have the same problem. (as it used to be autonumber and there was no need to reference the key on the combined form, this was never an issue).

that being said, it seems to me as though there is a problem with my Dmax syntax...

[thisisntwally]

hoookay, Got some of this worked out, i just changed the update spec to Dmax and voila! (well at first i forgot to change the control source...and maybe there was something else...)

that being said, the second form still insists on updating the first record. It is of course friendly enough to change the key value to corrispond with the first form - thus creating duplicates and leading me to believe the problem is either still here:

Dim stDocName As String
    Dim stLinkCriteria As String
 
   stLinkCriteria = "[txtkey]= me.txtkey"
    stDocName = "Update"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.openForm stDocName, , , stLinkCriteria
 

or perhaps on the second form here:

With CurrentDb.OpenRecordset("tbl modifications") '===>i deleted dbopentable - did nothing
 
        .Edit
            !key = DMax("[key]", "[tbl Modifications]")
          !UpdateDate = Date
          !Mod = Me.txtMOD
          !MRD = Me.txtMRD
          !SLD = Me.txtSLD
          !SCD = Me.txtSCD
          !OSD = Me.txtOSD
          !ObligationDocument = Me.objObligationDocument
 
        .Update
        .Bookmark = .LastModified
      End With

edit:i changed !key to = Me.txtkey, it then proceeded to update the same record, which oddly was no longer the first record but the second to last(the first of two 201's), gave it a null value, and moved it back to the top. The Implications of this are beyond my current comprehension (though they perhaps support hypothesis that the problem lies in the second form?)

[FishVal]

Both tables are indeed bound to [tbl Modifications]. There has been some who question my logic (i don't believe its all that ab-normal hehheh) but for user simplicity's sake im rather stubborn about having two forms. I do have a functioning tabbed form which combines the two, so I could get to work on some other functions in the database, but I hate the d@mn thing.

Really!?

Form RecordSource property is set to [tbl Modifications] and form fields ControlSource properties are set to [tbl Modifications] corresponding fields??!! Let me know whether is it really so.

If so why you update [tbl Modifications] programmaticall y. It will do nothing in "Update" form and cause record duplication in "Input" form, which in its turn will cause PK violation error on [tbl Modifications].key writing.

[thisisntwally]

*very embaressed

Really!?

Form RecordSource property is set to [tbl Modifications] and form fields ControlSource properties are set to [tbl Modifications] corresponding fields??!! Let me know whether is it really so.

If so why you update [tbl Modifications] programmaticall y. It will do nothing in "Update" form and cause record duplication in "Input" form, which in its turn will cause PK violation error on [tbl Modifications].key writing.

I don't(edit:do) think that the control source properties are set to fields in [tbl Modifications] (actually "txtkey" might be if what im doing in VB is setting control sources which as i type this begins to seem more likely)

 'in form1
!key = DMax("[key]", "[tbl Modifications]") + 1

'in form2
!key = DMax("[key]", "[tbl Modifications]")

So in response to your 1st question: maybe?(edit:in response to your only question: yes)

Form1(imput) successfully adds a new record but as you said (well the inverse), Form2(update) duplicates the PK value. How can I get form2 to edit the record from Form1 without referencing the PK? Barring the sloppy thinking you've just pointed out I had been thinking my problem was in Form2 here:

 With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)

edit:one last bit, in access form1(+2)>txtke y>Properties>co ntrol source = unbound, in case thats what you're asking about.

[FishVal]

*very embaressed


I don't(edit:do) think that the control source properties are set to fields in [tbl Modifications] (actually "txtkey" might be if what im doing in VB is setting control sources which as i type this begins to seem more likely)

 'in form1
!key = DMax("[key]", "[tbl Modifications]") + 1

'in form2
!key = DMax("[key]", "[tbl Modifications]")

So in response to your 1st question: maybe?(edit:in response to your only question: yes)

Form1(imput) successfully adds a new record but as you said (well the inverse), Form2(update) duplicates the PK value. How can I get form2 to edit the record from Form1 without referencing the PK? Barring the sloppy thinking you've just pointed out I had been thinking my problem was in Form2 here:

 With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)

I've meant form and form fields properties set when form was created or modified in Design view.
Open form (each form) in Design view and check whether form RecordSource property is set to [tbl Modifications] and form fields ControlSource properties are set to corresponding [tbl Modifications] fields.
If so record duplication actually occurs in "Input" form. When you start editing new record form automation creates new record, then your code do the same

With CurrentDB.OpenRecordset.....
     .AddNew
     .........
     .Update
End With
 

P.S. I have to go now, will be back in 4-5 hours.

[thisisntwally]

I've meant form and form fields properties set when form was created or modified in Design view.
Open form (each form) in Design view and check whether form RecordSource property is set to [tbl Modifications] and form fields ControlSource properties are set to corresponding [tbl Modifications] fields.

ok those are definately unbound. im pretty sure that the problem is that my editrecordButto n on Form2 is copied pretty directly from the original addrecordButton on Form1 - i just changed .addnew to .edit

Private Private Sub comRecord_Click()
Dim temp       As Variant
On Error GoTo Err_comRecord_Click
 
    temp = SysCmd(acSysCmdSetStatus, "Filling table...")
 
 With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
        .Edit
          !key = DMax("[key]", "[tbl Modifications]")
          !UpdateDate = Date
          !Mod = Me.txtMOD
          !MRD = Me.txtMRD
          !SLD = Me.txtSLD
          !SCD = Me.txtSCD
          !OSD = Me.txtOSD
          !ObligationDocument = Me.objObligationDocument
 
        .Update
        .Bookmark = .LastModified
      End With
 
temp = SysCmd(acSysCmdClearStatus)
 
Exit_comRecord_Click:
    Exit Sub
 
Err_comRecord_Click:
    MsgBox Err.Description
    Resume Exit_comRecord_Click
 
End Sub

Im going to look into this recordset business...

[FishVal]

ok those are definately unbound. im pretty sure that the problem is that my editrecordButto n on Form2 is copied pretty directly from the original addrecordButton on Form1 - i just changed .addnew to .edit

Private Private Sub comRecord_Click()
Dim temp As Variant
On Error GoTo Err_comRecord_Click
 
temp = SysCmd(acSysCmdSetStatus, "Filling table...")
 
 With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
.Edit
!key = DMax("[key]", "[tbl Modifications]")
!UpdateDate = Date
!Mod = Me.txtMOD
!MRD = Me.txtMRD
!SLD = Me.txtSLD
!SCD = Me.txtSCD
!OSD = Me.txtOSD
!ObligationDocument = Me.objObligationDocument
 
.Update
.Bookmark = .LastModified
End With
 
temp = SysCmd(acSysCmdClearStatus)
 
Exit_comRecord_Click:
Exit Sub
 
Err_comRecord_Click:
MsgBox Err.Description
Resume Exit_comRecord_Click
 
End Sub

Im going to look into this recordset business...

Code for record updating and, I'm pretty sure, there is also code for fields populating. Usually its better to let Access do it via bound form automation.
Why not to bind Form2 (maybe Form1 too, but Form2 definitely should be bound) to [tbl Modifications] and let Access do this behind the scenes?

BTW DoCmd.OpenForm with WhereCondition argument is indeed useless on unbound form.

[thisisntwally]

Code for record updating and, I'm pretty sure, there is also code for fields populating. Usually its better to let Access do it via bound form automation.
Why not to bind Form2 (maybe Form1 too, but Form2 definitely should be bound) to [tbl Modifications] and let Access do this behind the scenes?

BTW DoCmd.OpenForm with WhereCondition argument is indeed useless on unbound form.

I just found a seldom used 'ammendment number' that will soon become manditory now that i can identify the records without the autonumber/dmax (procurement request + amendment number => PR + Mod # =yay!). I'm hoping with that it will be easier to reopen a record in a new form now that i wont be referencing calculated fields.... now if i could only find those postings on linking with multiple criteria....I'l l be back just as soon as im in deep water again.

ps: SQL czars, gurus, etc beware - if things go well you're next

[thisisntwally]

Ok so form two was indeed unbound, a problem which has been addressed.

this didnt work:

Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Update"
 
    stLinkCriteria = "[txtkey]=" & Me!txtkey
    DoCmd.openForm stDocName, , , stLinkCriteria

which leads to "syntax error (missing operator) in query expression '[txtkey]='

so i tried this approach:

DoCmd.openForm "Update"
 
Forms!Update!txtkey.SetFocus
 
DoCmd.FindRecord Forms![Input Form]!txtkey

which gives me "An expression you entered is the wrong data type for one of the arguements" (goes to error on last line)

any ideas?