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

[FishVal]

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

Hi!

First of all there is no any reason why DoCmd.OpenForm will not work here.
You can use any field of Form2 Recordsource in DoCmd.OpenForm WhereCondition argument no matter it is bound or not to any control (certainly PK is preferrable).
If you have any special reason not to show the corresponding field on Form1, you can either hide the control bound to the corresponding field or retrieve it's value from Form1 Recordset.

I don't see any problem at all.

[thisisntwally]

Hi!

First of all there is no any reason why DoCmd.OpenForm will not work here.
You can use any field of Form2 Recordsource in DoCmd.OpenForm WhereCondition argument no matter it is bound or not to any control (certainly PK is preferrable).
If you have any special reason not to show the corresponding field on Form1, you can either hide the control bound to the corresponding field or retrieve it's value from Form1 Recordset.

I don't see any problem at all.

When you say retrieve value from recordset, is that to say i can reference a value not explicitly included in form1, provided its a field on the table? And if so can I reference a key which is autofilled on completion of Form1?

[FishVal]

When you say retrieve value from recordset, is that to say i can reference a value not explicitly included in form1, provided its a field on the table? And if so can I reference a key which is autofilled on completion of Form1?

Sure.

Something like this (assumed this is in Form1 module), where keyID is the name of PK table field

Me.Recordset![keyID]
 

[thisisntwally]

Sure.

Something like this (assumed this is in Form1 module), where keyID is the name of PK table field

Me.Recordset![keyID]
 

Thats the most helpful thing i've gotten yet! You rock.

Now why does it insist on only applying changes to the First record?
 Dim stDocName As String
    Dim stLinkCriteria As String
 
    stLinkCriteria = "Me.LastModified!key = & Forms![Input Form].txtkey"
    stDocName = "Update"
       DoCmd.openForm stDocName, , , stLinkCriteria
 

[FishVal]

Thats the most helpful thing i've gotten yet! You rock.

Now why does it insist on only applying changes to the First record?
Dim stDocName As String
Dim stLinkCriteria As String
 
stLinkCriteria = "Me.LastModified!key = & Forms![Input Form].txtkey"
stDocName = "Update"
DoCmd.openForm stDocName, , , stLinkCriteria
 

???
What is this code supposed to do?
I mean stLinkCriteria which is completely senseless. It should be a WHERE clause for Form_Update Recordsource.

For example

assumed
- the code opening Form_Update is in Form_Input module
- the name of recordsource table/query field you want to filter by is [Field]
- the [Field] is not bound to any control in Form_Input

stLinkCriteria = "[Field] = " & Me.Recordset![Field]
 

or

stLinkCriteria = "[Field] = " & Me![Field]
 

if anyway it is bound to Form_Input field named Field.

[thisisntwally]

???
What is this code supposed to do?
I mean stLinkCriteria which is completely senseless. It should be a WHERE clause for Form_Update Recordsource.

For example

assumed
- the code opening Form_Update is in Form_Input module
- the name of recordsource table/query field you want to filter by is [Field]
- the [Field] is not bound to any control in Form_Input

stLinkCriteria = "[Field] = " & Me.Recordset![Field]
 

or

stLinkCriteria = "[Field] = " & Me![Field]
 

if anyway it is bound to Form_Input field named Field.

Well while i am quite sure its rather convoluted at this point, its an attempt at opening a newly added record in a new form. it started out as you have above - but checking that again im getting a .(dot) or !operator or invalid parenthesis error...

[FishVal]

Well while i am quite sure its rather convoluted at this point, its an attempt at opening a newly added record in a new form. it started out as you have above - but checking that again im getting a .(dot) or !operator or invalid parenthesis error...

Post the whole sub which opens form "Update", as it looks so far, just to make sure we are mentioning the same.
BTW be aware that while a record is not saved (pencil mark on the record selector) you can't open it in another form. To save the record use

DoCmd.RunCommand acCmdSaveRecord
 

anywhere before DoCmd.OpenForm command.

[thisisntwally]

heres the sub ATM. im still getting the same error. I added the save command, and have changed my PK from an Autonumber to number, and its control source to =DMAX("key",[tbl Modifications]) + 1.
not that it produced any results per se.

Private Sub Command85_Click()
On Error GoTo Err_Command85_Click
    With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
        .AddNew
          !Program = Me.txtProgram                         '\
          !Name = Me.cboName                                 '|
          !ObjectClass = Me.cboObjectClass
          !ParentProgram = Me.txtParentProgram
          !Fund = Me.txtfund
          !ReportingEntity = Me.txtReportingEntity
          !PurchaseOrder = Me.txtPurchaseOrder
          !Comments = Me.txtComments
          !AppropriationYear = Me.cboAppropriationYear
          !Site = Me.txtsite
          !DOR = Me.txtDOR
          !FiscalYear = Me.cboFiscalYear                            '|
          !Description = Me.txtdescription                           '|
          !Budget = Me.txtCharge                             '|
          !UpdateDate = Date
          !SSD = Me.txtSSD
          !CertifiedDocument = Me.objRequest
          !BCD = Me.txtBCD
          !FCD = Me.txtFCD
          !Vendor = Me.txtVendor
          !key = Me.txtkey          
        .Update
        .Bookmark = .LastModified        
     End With    
   Dim stDocName As String
    Dim stLinkCriteria As String
 
   stLinkCriteria = "[txtkey]= " & Me![txtkey]
    stDocName = "Update"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.openForm stDocName, , , stLinkCriteria    
 
 
Exit_Command85_Click:
    Exit Sub
 
Err_Command85_Click:
    MsgBox Err.Description
    Resume Exit_Command85_Click
 
End Sub
 

edit: I also tried Me.Recordset![txtkey] which had the same result
another edit: stepping through the code, it goes to error on !key = Me....

[FishVal]

heres the sub ATM. im still getting the same error. I added the save command, and have changed my PK from an Autonumber to number, and its control source to =DMAX("key",[tbl Modifications]) + 1.
not that it produced any results per se.

Private Sub Command85_Click()
On Error GoTo Err_Command85_Click
With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
.AddNew
!Program = Me.txtProgram '\
!Name = Me.cboName '|
!ObjectClass = Me.cboObjectClass
!ParentProgram = Me.txtParentProgram
!Fund = Me.txtfund
!ReportingEntity = Me.txtReportingEntity
!PurchaseOrder = Me.txtPurchaseOrder
!Comments = Me.txtComments
!AppropriationYear = Me.cboAppropriationYear
!Site = Me.txtsite
!DOR = Me.txtDOR
!FiscalYear = Me.cboFiscalYear '|
!Description = Me.txtdescription '|
!Budget = Me.txtCharge '|
!UpdateDate = Date
!SSD = Me.txtSSD
!CertifiedDocument = Me.objRequest
!BCD = Me.txtBCD
!FCD = Me.txtFCD
!Vendor = Me.txtVendor
!key = Me.txtkey 
.Update
.Bookmark = .LastModified 
End With 
Dim stDocName As String
Dim stLinkCriteria As String
 
stLinkCriteria = "[txtkey]= " & Me![txtkey]
stDocName = "Update"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.openForm stDocName, , , stLinkCriteria 
 
 
Exit_Command85_Click:
Exit Sub
 
Err_Command85_Click:
MsgBox Err.Description
Resume Exit_Command85_Click
 
End Sub
 

edit: I also tried Me.Recordset![txtkey] which had the same result
another edit: stepping through the code, it goes to error on !key = Me....

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]?