Results 1 to 4 of 4

Thread: MS Access - how to store a query value in a variable

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2010
    Posts
    2

    MS Access - how to store a query value in a variable

    I need to store a query value ( this is a max(fieldname) from a table) into a global variable. the field name is an autonumber in a table. On a form i have a textbox and I want the value of that textbox to be my global variable. then i want to create a query with a new field, and the field to be the valule of my textbox. How can I do this in VBA? I appreciate all of your assistance.

    I'm using MS Access 2007 on a Windows XP machine.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: MS Access - how to store a query value in a variable

    Welcome to VBForums

    Thread moved from 'Other Programming Languages' forum to the 'Office Development/VBA' forum


    First of all create the variable at the top of a module using the keyword Public, eg:
    Code:
    Public pMyVal as Long
    Then add code somewhere to use a recordset to set the value based on a query, eg:
    Code:
    Dim objRS as ADODB.Recordset
      Set objRS = New ADODB.Recordset
      objRS.Open "SELECT Max(field) FROM table", CurrentProject.Connection
      If Not objRS.EOF Then
        pMyVal = objRS.Fields(0).Value
      End If
      objRS.Close
      Set objRS = Nothing

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2010
    Posts
    2

    Re: MS Access - how to store a query value in a variable

    Thank you for taking the time to post this code. Unfortunatelly, I'm getting an error: User-defined type not defined. I pasted your code in the Private Sub Form_Load() event. The debbuger highlights the form and objRS As ADODB.Recordset.

    Also, can you tell me how in VB on Form_Load you can assign this global value to the textbox.value on the form?

    I much appreciate it.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: MS Access - how to store a query value in a variable

    The error is a bit strange, you should have that reference enabled by default... however, the "old style" DAO should work:
    Code:
    Dim objRS as DAO.Recordset
      Set objRS = New DAO.Recordset
      CurrentDb.OpenRecordset("SELECT Max(field) FROM table")
      If Not objRS.EOF Then
        pMyVal = objRS.Fields(0).Value
      End If
      objRS.Close
      Set objRS = Nothing
    Also, can you tell me how in VB on Form_Load you can assign this global value to the textbox.value on the form?
    Like this:
    Code:
    textbox.value = pMyVal

Posting Permissions

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



Click Here to Expand Forum to Full Width