Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Goto record based on value in Global variable

    Hi there,

    My form has a field called Internal_ID which is a string. The default view is set to continuous forms so i can see all the records simular to a spreadsheet layout.
    When i open the form i want to go to the record which matches the value in a global variable.

    Form is displaying a number of records ie

    10001/a Record 1
    10002/a Record 2
    ......

    10002/a is held in a global. When i open the form i would like to select that particular record please?

    thanks for your help
    M

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim rst As DAO.Recordset
        
        If Len(GlobalVariable) > 0 Then
            Set rst = Me.RecordsetClone
            With rst
                .FindFirst "Internal_ID = '" & GlobalVariable & "'"
                If .NoMatch = False Then Me.Bookmark = .Bookmark
                .Close
            End With
            Set rst = Nothing
        End If
        
    End Sub
    Where GlobalVariable is the name of the global variable.
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks for that.

    I really need to understand recordsets and it seems a very useful way of doing things. Is there a website you could suggest or a book on the subject?

    thanks again for your help

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Access help remains one of the most important source of information. It has a whole section dedicated to DAO. Among many other sites, you can also find information, advices and programming examples at :
    Microsoft Access tips: DAO Programming Code Examples
    DAO Advanced Programming with Microsoft Access
    CurrentDb Method [Access 2003 VBA Language Reference]
    Microsoft Access Database Development
    Have a nice day!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Or, without using a Recordset, simply

    Code:
    Private Sub Form_Load()
     If Len(Nz(GlobalVariable, "")) > 0 Then
      Internal_ID.SetFocus
      DoCmd.FindRecord GlobalVariable
     End If
    End Sub

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thankyou both for your advice. I'm sure it was on this site where i heard the phrase "there is always more than one way to skin a cat"

    thanks again
    marcus

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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