Results 1 to 5 of 5
  1. #1
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67

    Use code not in a form's module to access the forms recordset

    I have a form F_Chosen which has as its record source the query Q_Chosen. One of the fields of the recordset is Q_Num.



    From code written inside the form's code module, I have no trouble accessing fields of the recordset with code such as

    x = Me!Q_Num.



    But clearly this doesn't work in code written in another Module (e. g. Module1) because Module 1 doesn't know what Me refers to.
    How might I set it up so that code written in Modlule1 could directly access the recordset of form F_Chosen?


    Thank You in Advance

  2. #2
    princess12 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    79
    is module 1 in a different form?

  3. #3
    ssanfu is online now Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,731
    It depends on what you are trying to accomplish.

    In code, you could use "Forms!Form_Name.Control_Name" (the form must be open)
    Example
    Code:
    x = Forms!F_Chosen.Q_Num

    Or you could write a UDF (user defined function) referencing the control on the form (the form must be open); the function is in a standard module (say, Module1).

    Example:
    Code:
    Public Function RandomNumber(Lowest As Long, Highest As Long)
    ' Generates a random whole number within a given range
       Randomize
       RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
    End Function
    How do you use it?
    In a control on a form
    =RandomNumber(750,1000) ... to give a random number between 750 and 1000
    =RandomNumber(0,10) ... to give a random number between 0 and 10

    If you have two controls on a form: MyLower and MyUpper, you would use
    =RandomNumber(Me.MyLower, Me!MyUpper)

    You could also use the UDF in a query.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Code:
    dim db as database
    dim rst as recordset
    dim iQNum as double 'or integer or whatever the datatype is
    
    set db = currentdb
    set rst = db.openrecordset("Q_Chosen")
    
    do while rst.eof <> true
        'do whatever you're going to do on a record by record basis
        iqnum = rst.fields("Q_num")
        'or iqunum = rst!qnum or any of a variety of other ways
        rst.movenext
    loop
    rst.close
    set rst = nothing
    set db = nothing

  5. #5
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67
    Steve,

    The first part of your answer was exactly what I needed. Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-02-2014, 09:38 AM
  2. Replies: 2
    Last Post: 10-31-2014, 07:42 AM
  3. Replies: 0
    Last Post: 07-11-2013, 02:21 PM
  4. Replies: 4
    Last Post: 07-13-2012, 06:30 AM
  5. Replies: 2
    Last Post: 03-08-2012, 12:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums