Results 1 to 4 of 4

Using VBA to get a field value from a table into variable

This is a discussion on Using VBA to get a field value from a table into variable within the Microsoft Access forums, part of the Question Forums category; Hi. I am working on an Access database. One of the functions provides an inventory of 24 connections, include if ...

  1. #1
    New Member
    Join Date
    Aug 2005
    Posts
    2

    Default Using VBA to get a field value from a table into variable

    Hi.

    I am working on an Access database. One of the functions provides an inventory of 24 connections, include if they are active or not based on a status field.

    One particular table has 24 entries (always will, no more, no less) This is the table with the definition of the 24 connections.

    One of the fields in this table is a yes/no field which basically is a status field for whether that record is currently 'on' or 'off'

    On the main form that I'm using I would like to display the status of the 24 connections, based only on the 'active' field.

    It would help me if I could find a way to retrieve the 24 values via VBA.

    Is there, for an example a way to take.

    testValue1 = table.Connections(record(1), field(active)).value

    or better yet

    for counter = 1 to 24

    testValue.counter = table.Connections(record(counter), field(active).value

    {code that does other stuff}

    next counter


    I know that having a database with 24 records isn't exactly harnessing the full power of Access, but it gives me a project to play around with.

    Thanks for any help!

  2. #2
    MrExcel MVP CT Witter's Avatar
    Join Date
    Jul 2002
    Location
    Columbus, OH
    Posts
    1,208

    Default

    Welcome to Access!!

    Below is a quick example of how to read a table as a recordset and carryout some action.

    Make sure to set a reference to Microsoft DAO

    Code:
    Sub Faster()
       Dim d As Database
       Dim r As Recordset
       Dim Price As Field, Qty As Field, UnitCost As Field
       Set d = CurrentDB()		
       Set r = d.OpenRecordset("TableName")
       Set Price = r.Fields("Price")
       Set Qty = r.Fields("Qty")
       Set UnitCost = r.Fields("UnitCost")
       While Not r.EOF
          r.Edit
          Price = Qty * UnitCost
          r.Update
          r.MoveNext
       Wend
       r.Close
    End Sub
    HTH,
    CT

  3. #3
    New Member
    Join Date
    Aug 2005
    Posts
    2

    Default

    Thanks for the information and help.

    It's the r.MoveNext that takes me to the next record in the set right?

    While fooling around after posting originally I found I could also do what I wanted with a dlookup statement

    testingValue = DLookup("[In Use]", "[Main_Hub]", "Port=1")


    However, looking at your codes, I think that although it will take me a little bit more time to implement will be a better structure. I like the idea of being able to edit and update my tables from VBA... I can see all sorts of ideas for that.


    Could you please explain what I need to do to set a reference to MS DAO? Is that like a declaration or something more complex?

  4. #4
    MrExcel MVP CT Witter's Avatar
    Join Date
    Jul 2002
    Location
    Columbus, OH
    Posts
    1,208

    Default

    You are correct that the movenext will take you to the next record.


    To get to the reference....

    In the database window type "ALT+F11", this will open up the vbe window.
    Go to Tools->References

    Scroll down the list, you should find something like "Microsoft DAO 3.6 Object Library"

    HTH,
    CT

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com