Trying to change code that open a form and filters out records that were not altered

[Redbeard]

Hi, I am newbie that is using Access 2003. I have a command button on a pop-up form that duplicates the current record displayed on my main form and all sub-forms attached to it. The last part of the code directs the form to reopen and filters the form for the duplicated record, only displaying it. Is there any way of having the form reopen and going to the duplicated record without filtering all the other records out?

Here is the last line of the code:

DoCmd.OpenForm "frmDescription", acNormal, , "[Accession Number]='" + MyValue + "'"

If all the code is need I will post it but it is rather long.

[nico5038]

For this you'll need to use a bookmark and (when not established before) a .find command.

Check it out and let me know or more assistance is needed.

Nic;o)

[Redbeard]

Sorry I don't know code that well what is a bookmark? and how do I code the find command?

[nico5038]

Checkout the code in post #8:
http://www.thescripts.com/forum/thre...-bookmark.html

Nic;o)

[Redbeard]

Thanks for the direction but I am still having problems. Ok here is the whole code... I am trying to get them last bit at the bottom to work with no luck. After the records are duplicated I am try it find the duplicated record in the rest of the records instead of filtering for it.

Private Sub Duplicate_Record_Click()
 
 
    '------------------------------------
    'Declaration of the variable
    '------------------------------------
 
    Dim rs As Recordset             'recordset that contain the original record
    Dim RSDuplicated As Recordset   'recordset use to duplicate the record
    Dim QuerySelect As String       'represents each SQL query
    Dim MyValue As String           'represents the new accession number
    Dim ObjectID As Integer         'represents the new ObjectID of the duplicated record
    Dim OldObjectID As Integer      'represents the old ObjectId of the original record
    Dim Counter As Integer          'represents the counter use for many loops
    Dim RecordCounter As Integer    'represents a counter for each record duplicated
    Dim ConditionID As String       'represent a conditionId number
    Dim CollConditionID As New Collection   'represents the list of each conditionId record to be duplicated
    Dim CollTreatmentID As New Collection   'represents the list of each treatmentId record to be duplicated
 
    '-------------------------------------
    'End of declaration
    '-------------------------------------
 
    'Close the RecordControlBox
    DoCmd.Close acForm, "frmRecordControlBox"
 
    'Retrieving data from the record to be duplicated
    QuerySelect = "Select * From Description where Description.[Accession Number] = '" & Forms!frmDescription!txtAccNum.Value & "'"
    Set rs = CurrentDb().OpenRecordset(QuerySelect)
    Set RSDuplicated = rs.Clone
 
    'Initialization of the counter
    Counter = 1
 
    '------------------------------------------------
    'Adding a new record to the duplicated recordset
    '------------------------------------------------
    RSDuplicated.AddNew
 
    'starting the loop.
    '***should not be a constant, in case the table change! ***
    Do While Counter < 47
        RSDuplicated.Fields(Counter) = rs.Fields(Counter)
        Counter = Counter + 1
    Loop
 
    ' Display message, title, and default value.
    MyValue = InputBox("Enter the Accession Number", "ATCDB - Duplicate Record", RSDuplicated![Accession Number])
 
    'Check if the Accession Number is correct, otherwise, show a new dialog box
    If Len(MyValue) = 0 Then
        'cancel everything, show cancel popup
        MsgBox "This action was canceled by the user. No data has been duplicated"
    Else
        While Len(MyValue) > 0 And AccessionNumberPresent(MyValue) = True
            'show a new popup until it works
            MsgBox "This Accession Number already exists, try another one."
            MyValue = InputBox("Enter the Accession Number", "ATCDB - Duplicate Record", RSDuplicated![Accession Number])
        Wend
 
        If Len(MyValue) = 0 Then
            'cancel everything, show cancel popup
            MsgBox "This action was canceled by the user. No data has been duplicated"
        Else
            'Update the data to the table
            RSDuplicated![Accession Number] = MyValue
 
            'update the objectid number, cuz the autonumber doesn't work properly (bug?)
            OldObjectID = rs![ObjectID]
            ObjectID = AutoNumberObjectID()
            RSDuplicated![ObjectID] = ObjectID
            RSDuplicated.Update
 
            '--------------------------------------------------------
            ' Adding the condition report
            '--------------------------------------------------------
 
            'i stock the condition Id on a collection
            QuerySelect = "Select * From [Condition Info] where [Condition Info].[ObjectID] = " + CStr(OldObjectID)
            Set rs = CurrentDb().OpenRecordset(QuerySelect)
 
            'i go through the recordset to get all the conditionId
            If Not rs.EOF Then
                rs.MoveLast
                rs.MoveFirst
            End If
 
            RecordCounter = 0
 
            'putting the condition id into the collection
            Do While RecordCounter < rs.RecordCount
                CollConditionID.Add (CStr(rs![ConditionID]))
                rs.MoveNext
                RecordCounter = RecordCounter + 1
            Loop
 
            'if there is at least 1 record
            If rs.RecordCount > 0 Then
 
                RecordCounter = 1
 
                Do While RecordCounter <= CollConditionID.Count
 
                    QuerySelect = "Select * From [Condition Info] where [Condition Info].[ConditionID] = " + CStr(CollConditionID.Item(RecordCounter))
                    Set rs = CurrentDb().OpenRecordset(QuerySelect)
                    Set RSDuplicated = rs.Clone
 
                    'Loop in case there's more than one condition
                    RSDuplicated.AddNew
                    Counter = 0
 
                    'starting the loop.
                    'should not be a constant, in case the table change!
                    Do While Counter < 6
                        RSDuplicated.Fields(Counter) = rs.Fields(Counter)
                        Counter = Counter + 1
                    Loop
 
                    RSDuplicated![ObjectID] = ObjectID
                    RSDuplicated![ConditionID] = AutoNumberConditionID()
                    RSDuplicated.Update
                    RecordCounter = RecordCounter + 1
 
                Loop
 
            End If
            '--------------------------------------------------------
            'End of condition ID
            '--------------------------------------------------------
 
            '--------------------------------------------------------
            ' Adding the treatment info
            '--------------------------------------------------------
 
            'i stock the treatment Id on a collection
            QuerySelect = "Select * From [Treatment Info] where [Treatment Info].[ObjectID] = " + CStr(OldObjectID)
            Set rs = CurrentDb().OpenRecordset(QuerySelect)
 
            'i go through the recordset to get all the treatmentId
            If Not rs.EOF Then
                rs.MoveLast
                rs.MoveFirst
            End If
 
            RecordCounter = 0
 
            'putting the treatment id into the collection
            Do While RecordCounter < rs.RecordCount
                CollTreatmentID.Add (CStr(rs![TreatmentID]))
                rs.MoveNext
                RecordCounter = RecordCounter + 1
            Loop
 
            'if there is at least 1 record
            If rs.RecordCount > 0 Then
 
                RecordCounter = 1
 
                Do While RecordCounter <= CollTreatmentID.Count
 
                    QuerySelect = "Select * From [Treatment Info] where [Treatment Info].[TreatmentID] = " + CStr(CollTreatmentID.Item(RecordCounter))
                    Set rs = CurrentDb().OpenRecordset(QuerySelect)
                    Set RSDuplicated = rs.Clone
 
                    'Loop in case there's more than one condition
                    RSDuplicated.AddNew
                    Counter = 1
 
                    'starting the loop.
                    'should not be a constant, in case the table change!
                    Do While Counter < 5
                        RSDuplicated.Fields(Counter) = rs.Fields(Counter)
                        Counter = Counter + 1
                    Loop
 
                    RSDuplicated![ObjectID] = ObjectID
                    RSDuplicated.Update
                    RecordCounter = RecordCounter + 1
                Loop
 
            End If
            '-------------------------------------------------------
            'End of treatment info
            '-------------------------------------------------------
 
 
            'DoCmd.OpenForm "frmDescription", acNormal, , "[Accession Number]='" + MyValue + "'"
 
            '[Accession Number] = Forms!frmDescription!txtAccNum
            'Me!frmDescription.Requery
            DoCmd.OpenForm "frmDescription", acNormal
 
            Dim strCriteria As String
 
            strCriteria = "[Accession Number]='" + MyValue + "'"
            Forms!frmDescription!txtAccNum.FindFirst (strCriteria)
            Forms!frmDescription.Bookmark = Forms!frmDescription!txtAccNum.Bookmark
 
 
        End If
    End If

Any help would be great!

[nico5038]

I guess that you want to open "frmDescription" with the bookmark positioning.
This will imply that you don't need the current "Filter passing", but add some comma's in front of it so it becomes the last parameter named "OpenArgs" of the OpenForm command.
In the "frmDescription" you'll have to use the OnOpen event to intercept the passed string from Me.OpenArgs
This will hold the string "[Accession Number]='" + MyValue + "'"
For the find needed for the bookmark this string can be used "straight away".

Nic;o)

[Redbeard]

I am sorry you are going right over my head. First of all what lines from 187 down do I need to keep and what ones are wrong? What is "OpenArgs"? and how do I use it here and how do I get it to intercept the code on the OnOpen event in frmDescription? Sorry I am a newbie.

[nico5038]

Somewhere you'll have to open a form and I thought the last commented out Docmd.Openform was that position as it's after the loop with the addition of records.

For the OpenArgs you can check the F1 (help) description of the OpenForm command.

Nic;o)