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]

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?

Change the first code to this. WhereCondition argument in DoCmd.OpenForm has to have name of table field to the left of '=' not name of form control.

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

[thisisntwally]

this:

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

gives me a 'syntax error'

this:

DoCmd.openForm "Update", acNormal, , "!keyID='" & Me![txtkey] & "'", acFormEdit, acWindowNormal

prompts me for a parameter value for keyID

this (stolen from another of your posts):

 Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Update"
    stLinkCriteria = "[keyID]=" & "'" & Me![txtkey] & "'"
 
    With DoCmd
        .RunCommand acCmdSaveRecord
        .openForm stDocName, , , stLinkCriteria
    End With
 
    Forms![Update]![txtkey].DefaultValue = Me![txtkey]

says command or action saverecord is unavailable.

when put in this:

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
          !Amendment = Me.txtAmendment
 
        .Update
        .Bookmark = .LastModified
 
   End With  
 
Exit_Command85_Click:
    Exit Sub
 
Err_Command85_Click:
    MsgBox Err.Description
    Resume Exit_Command85_Click
 
End Sub

[FishVal]

this:

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

gives me a 'syntax error'

this:

DoCmd.openForm "Update", acNormal, , "!keyID='" & Me![txtkey] & "'", acFormEdit, acWindowNormal

prompts me for a parameter value for keyID

this (stolen from another of your posts):

 Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "Update"
stLinkCriteria = "[keyID]=" & "'" & Me![txtkey] & "'"
 
With DoCmd
.RunCommand acCmdSaveRecord
.openForm stDocName, , , stLinkCriteria
End With
 
Forms![Update]![txtkey].DefaultValue = Me![txtkey]

says command or action saverecord is unavailable.

when put in this:

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
!Amendment = Me.txtAmendment
 
.Update
.Bookmark = .LastModified
 
End With 
 
Exit_Command85_Click:
Exit Sub
 
Err_Command85_Click:
MsgBox Err.Description
Resume Exit_Command85_Click
 
End Sub

Post [tbl Modifications] metadata. Example given below.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[thisisntwally]

I figured much of my problems out, partially a bound form thing, partially through a different approach.

Form1 opens an existing blank record, which it edits, "saves", and opens in a new form. It then closes and generates a new blank for when it opens next.

heres the problem: Values inputed on Form1 fail to save, automatically or even when instructed. my code follows...



from the pulldown menu:

Sub openForm()
  DoCmd.openForm "Input Form", acNormal, , "[EntryStatus] = 0", acFormEdit, acWindowNormal
End Subon 

click event:

  With DoCmd
 
        .RunCommand acCmdSaveRecord
 
        .RunSQL "UPDATE [tbl Modifications] SET [EntryStatus]=(1) WHERE [keyID]= [txtkey]"
 
        .openForm "Update", acNormal, , "[keyID]=" & Me!txtkey, acFormEdit, acWindowNormal
 
        .Close acForm, Me.Name
 
        End With

onclose:

Private Sub Form_Close()
 
 
With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
        .AddNew
          !EntryStatus = 0
          .Update
End With
 
End Sub

[thisisntwally]

got this one. my controls were still unbound. oops.