Update Record in one Form and Open in another form?

**FishVal** · Jul 1 '07, 08:57 AM

Originally posted by 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

Code:

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** · Jul 2 '07, 11:27 AM

Originally posted by FishVal

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** · Jul 2 '07, 04:29 PM

Originally posted by thisisntwally

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

[CODE=vb]
Me.Recordset![keyID]
[/CODE]

**thisisntwally** · Jul 3 '07, 06:14 PM

Originally posted by FishVal

Sure.

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

[CODE=vb]
Me.Recordset![keyID]
[/CODE]

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

Code:

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** · Jul 3 '07, 08:38 PM

Originally posted by thisisntwally

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

Code:

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

Code:

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

or

Code:

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

if anyway it is bound to Form_Input field named Field.

**thisisntwally** · Jul 5 '07, 11:27 AM

Originally posted by FishVal

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

Code:

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

or

Code:

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** · Jul 5 '07, 05:40 PM

Originally posted by thisisntwally

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

Code:

DoCmd.RunCommand acCmdSaveRecord

anywhere before DoCmd.OpenForm command.

**thisisntwally** · Jul 5 '07, 07:01 PM

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.

Code:

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** · Jul 6 '07, 09:03 AM

Originally posted by 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.

Code:

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

**thisisntwally** · Jul 6 '07, 11:09 AM

Originally posted by FishVal

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** · Jul 6 '07, 12:19 PM

Originally posted by 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** · Jul 6 '07, 12:47 PM

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:

Code:

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:

Code:

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** · Jul 6 '07, 02:16 PM

Originally posted by 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.

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** · Jul 6 '07, 02:48 PM

*very embaressed

Originally posted by FishVal

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)

Code:

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

Code:

'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:

Code:

 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.