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

[Deltavictor]

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!

 

[CT Witter]

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

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

 

[Deltavictor]

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?

 

[CT Witter]

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

 

[zababo]

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

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

Thank you for above. How would I use this code to read all of the Email addresses from a field in a Access database table into the .To line of code to generate an outlook email. Example below. I have searched around and am unable to find clear explanations of how this works. Help would be appreciated!

Note: the emails in the fields already have semi colons after them "Email;" So it wont need to be added.

Set Mail = MyOutlook.CreateItem(olMailItem)

Mail.To = "Email Addresses"
Mail.Subject = "Test"
Mail.Body = "Test"