Solved In form, move to another record with known ID

[twgonder]

This one's been bugging me for a few weeks now, and I haven't gotten an answer on two other forums.

I'm in a form on a record. I programmatically copy that record to a new record. After doing that I know the ID of the new record.
How can I "move" to that new record in the same open form with VBA ?

 

[theDBguy]

How about?

Me.Recordset.FindFirst "ID=" & varID

 

[The_Doc_Man]

If you have the ID, ...

Me.RecordsetClone.FindFirst "ID = " & varID
If Not Me.RecordsetClone.NotFound Then
    Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark
End if

 

[arnelgp]

if you are using SQL (which i think you are doing since it does not automatically put you on the New record).
you first Requery your form and execute Either of the 2 previous replies.

if you want to be on the New record created you can use:

with docmd
   .RunCommand acCmdSelectRecord
   .RunCommand acCmdCopy
   .RunCommand acCmdPasteAppend
end with

 

[twgonder]

How about?

Me.Recordset.FindFirst "ID=" & varID

I gave it a try, it goes to the wrong record. Should go to the record id in green, but went to the one in pink (hey, I didn't choose these colors that the snip tool uses).

 

[arnelgp]

what is AppId? a function?
how does the function work?

 

[twgonder]

If you have the ID, ...

Me.RecordsetClone.FindFirst "ID = " & varID
If Not Me.RecordsetClone.NotFound Then
    Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark
End if

Gets a runtime error:

 

[twgonder]

what is AppId? a function?
how does the function work?

AppId is a constant in the form, not important here.
The actual record copy code (I copied it from elsewhere, modified it some, but it seems to work) is a few sub layers deep, but the important section of code that copies looks like this:

Dim fimr As String
  Dim rs1 As Recordset, rs2 As Recordset, F As Field
  Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM " & aTblNm1 & " WHERE " _
    & aWhereCond)
  '  While Not rs1.EOF
  Set rs2 = CurrentDb.OpenRecordset(aTblNm2)
  rs2.AddNew
  For Each F In rs1.Fields
    temp1 = "]" & F.Name & "]"
Debug.Print temp1
    If InStr(1, gSp(6), temp1) Then
      'don 't include the skipped item
    Else
      fimr = ""
      If InStr(1, gSp(7), temp1) Then
        ' write extraction routines
        temp2 = fSarrayPosition(gSp(7), F.Name, "]")
        fimr = fSarrayExtract(gSp(8), temp2, "]")
        Select Case fimr
          Case "*User*"
            fimr = SysCtrl(2, 1)
          Case "*Now*"
            fimr = Now()
          Case "*Now.*"
            fimr = Format(Now(), "yyyy.mm.dd.hh.nn.ss")
          Case Else
            ' use data passed in
        End Select
      End If
      If fimr = "" Then
        rs2(F.Name) = rs1(F.Name).Value
      Else
        rs2(F.Name) = fimr
      End If
    End If
  Next
  rs2.Update
  rs2.Bookmark = rs2.LastModified
  gSp(1) = rs2.Fields(0)
  rs1.MoveNext
  '  Wend
  rs2.Close
  rs1.Close
  Set rs2 = Nothing
  Set rs1 = Nothing
ExitCd:
  Exit Sub

 

[moke123]

.NotFound?

Shouldn't that be .NoMatch?

 

[arnelgp]

yes use .NoMatch not .NotFound

also i noticed from the code you post that gsp(1) is already being set to .Field(0) (is field(0) same as "EntityID"?)

sorry but you need to Requery the Form's Recordset first because you are using a Different Recordset on
Adding new record.

Requery, then .FindFirst.

if you do not want to requery use the Form's Recordset to add the record.

 

[moke123]

This one's been bugging me for a few weeks now, and I haven't gotten an answer on two other forums.

You really should post links to your cross-posts.
here's one:
https://www.accessforums.net/showthread.php?t=86653

 

[The_Doc_Man]

Whoops, my bad. I always confuse .NoMatch and .NotFound - that is probably the source of the error.

Apologies, @twgonder, for shooting from the hip - and missing. The snippet I showed you using .RecordsetClone is the code that the command button wizard will build for you. I added the test for failure to find it because if you DO fail to find it, the bookmark copy won't work right, so would have traded one error for another.

 

[twgonder]

You really should post links to your cross-posts.
here's one:
https://www.accessforums.net/showthread.php?t=86653

Yep, that post was about the copy, which I've solved. Even though moving to a new record became a later post in the thread.
I tried to keep it simple this time, with just the basic question.

 

[twgonder]

also i noticed from the code you post that gsp(1) is already being set to .Field(0) (is field(0) same as "EntityID"?)

sorry but you need to Requery the Form's Recordset first because you are using a Different Recordset on
Adding new record.

Requery, then .FindFirst.

if you do not want to requery use the Form's Recordset to add the record.

I ran across some confusion on .Field(0), with some claiming it's always the ID and others saying no. So if it fails I'll go back to the field name proper. If I can remember how.

 

[Minty]

I ran across some confusion on .Field(0), with some claiming it's always the ID and others saying no. So if it fails I'll go back to the field name proper. If I can remember how.

Only if it's the first field in the record set being used.
Always specify the field name unless you are retrieving a single field record set with just the ID, it removes any ambiguity.

 

[moke123]

I ran across some confusion on .Field(0), with some claiming it's always the ID and others saying no. So if it fails I'll go back to the field name proper.

It shouldn't be too confusing. I almost exclusively have the primary key as the first field in a table. Therefore rs.Fields(0) always points to the PKey. It just depends where it is positioned in the fields collection. Using rs.fields("MyPKeyField") will work no matter where the field lies in the collection.

 

[moke123]

I cant really tell what exactly you're doing as all the if's and select cases are confusing and you don't show all your code.

From what I can tell you are trying to copy all the form fields to a new record and then move the forms bookmark to that record.

here's a simple example that adds all the forms fields and field values to a dictionary object. The arguments passed are the name of the primary key field name and the primary key value of the record being copied. The primary key field is then removed from the dictionary .
We then loop through the dictionary in the .addnew section of the code and return the new ID. Then the forms bookmark is set to the new PK.

 

[twgonder]

Thanks for all the help. I now have Burt Charles Smith copying like a mad man.
My next step will be to add sequential numbers instead of date to the Entity code. New question for that.

I cant really tell what exactly you're doing as all the if's and select cases are confusing and you don't show all your code.

From what I can tell you are trying to copy all the form fields to a new record and then move the forms bookmark to that record.

here's a simple example that adds all the forms fields and field values to a dictionary object. The arguments passed are the name of the primary key field name and the primary key value of the record being copied. The primary key field is then removed from the dictionary .
We then loop through the dictionary in the .addnew section of the code and return the new ID. Then the forms bookmark is set to the new PK.

I cant really tell what exactly you're doing as all the if's and select cases are confusing and you don't show all your code.

From what I can tell you are trying to copy all the form fields to a new record and then move the forms bookmark to that record.

here's a simple example that adds all the forms fields and field values to a dictionary object. The arguments passed are the name of the primary key field name and the primary key value of the record being copied. The primary key field is then removed from the dictionary .
We then loop through the dictionary in the .addnew section of the code and return the new ID. Then the forms bookmark is set to the new PK.

I cant really tell what exactly you're doing as all the if's and select cases are confusing and you don't show all your code.

From what I can tell you are trying to copy all the form fields to a new record and then move the forms bookmark to that record.

here's a simple example that adds all the forms fields and field values to a dictionary object. The arguments passed are the name of the primary key field name and the primary key value of the record being copied. The primary key field is then removed from the dictionary .
We then loop through the dictionary in the .addnew section of the code and return the new ID. Then the forms bookmark is set to the new PK.

I cant really tell what exactly you're doing as all the if's and select cases are confusing and you don't show all your code.

From what I can tell you are trying to copy all the form fields to a new record and then move the forms bookmark to that record.

here's a simple example that adds all the forms fields and field values to a dictionary object. The arguments passed are the name of the primary key field name and the primary key value of the record being copied. The primary key field is then removed from the dictionary .
We then loop through the dictionary in the .addnew section of the code and return the new ID. Then the forms bookmark is set to the new PK.

This page seems to have gone nuts on me wehen replying, and I can't see why.

@moke123 Thanks for your assist. What all the code is doing isn't all that important. It works. I copied it, modified it and think I understand it. I was looking for a way to get the newly created-copied record to appear in the form. It seems you are doing a read-write-read-write while my code just does a read-write. If I understand you correctly.

 

[moke123]

It seems you are doing a read-write-read-write while my code just does a read-write. If I understand you correctly.

No, my code only reads the recordset once into a dictionary and writes it to the table and it only uses one recordset as opposed to 2.

It's this part of the code which sets the forms bookmark

Private Sub Command4_Click()
    Dim rsID As Long
    Dim rs As DAO.Recordset

    Me.Dirty = False  'save record

    rsID = frmCopy("PersonID", Me.PersonID) 'copy the record and get the new PKey

    Me.Recordset.Requery

    Set rs = Me.Recordset

    rs.FindFirst "PersonID = " & rsID

    If Not rs.NoMatch Then
        Me.Bookmark = rs.Bookmark
    End If

End Sub

I hope you're aware that these table names need fixing. Get rid of the spaces and special characters.

 

[twgonder]

No, my code only reads the recordset once into a dictionary and writes it to the table and it only uses one recordset as opposed to 2.

I hope you're aware that these table names need fixing. Get rid of the spaces and special characters.

I'm not sure what you mean by "dictionary".
What spaces are you seeing in a table name? Underscore is not a "special character" it is a common delimiter used for coding.

Although I don't use it to separate Order_Details, I do use it in the multi-language and helps parts of my system. I want to process on tbl_ names, but not on tbld_ and tblc_ records which don't contain user data. It's easier to just pluck the _ field than try and build a bunch of logic for different size portions of a name. I do the same with names for text boxes and combo boxes in forms to process on those objects.

The Au in the name means "Application Universal" as Pr means "Payroll". This allows me to isolate tables based on their application modules. And to create a similar name for different sub modules without clashing.