Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open the same record in another form? (Office 2000)

    Hi!

    Is there any way to produce a command button in one form to open the same record in another form? I can set up a command button to open the form, but it would be wonderful if it would open the other form on the same record.

    Any ideas?

    Thank you
    Louise

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open the same record in another form? (Office 2000)

    Let's say the record identifier is just a field named ID.

    On your second form's Open event, use the following code:

    Private Sub Form_Open()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = '" & Forms![form1]![ID] & "'"
    Me.Bookmark = rs.Bookmark

    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open the same record in another form? (Office 2000)

    If you have a value identifying the current record, it is easy. Say that the field RecID is the primary key of the underlying table, and that it is a number.
    <code>
    Private Sub cmdOpenOtherForm_Click()
    DoCmd.OpenForm FormName:="frmOtherForm", WhereCondition:="[RecID] = " & Me.[RecID]
    End Sub
    </code>
    The square brackets [ ] are not strictly necessary here, but you do need them if the name contains spaces or other non-standard characters. If RecID is a text field, the instruction becomes
    <code>
    ..., WhereCondition:="[RecID] = " & Chr(34) & Me.[RecID] & Chr(34)
    </code>
    Chr(34) is the equivalent of the double quote ".

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open the same record in another form? (Office 2000)

    Thanks for the replies. Will have to try them to see if they work for me. What I normally do now is a search on the last name "LNAME" and when the record is brought up, I can work on it.

    Will let you know how I make out.

    Thank you!
    Louise

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open the same record in another form? (Office 2000)

    Hi,
    I did a command button on the original form to open the second form and I noticed that under the form operations, the wizard gave me a choice of opening all records or a specific one. I chose the specific one and then I chose LNAME as the field to look at and it worked! Didn't know I could do that.

    Thank you both!
    Louise

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Open the same record in another form? (Office 2000)

    Lname is not a particularly good field to use as there is no guarantee that all LastNames are unique. (unless you have set it up so they are all different which would be unusual.)

    So if you click the button while the name Brown is displayed, it will go a record with the name Brown, but it may no the same one.

    It is better to use the Primary Key field as the field, because it is always unique.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •