Retrieving Field Values with VBA

  • Thread starter Thread starter James S. Rice
  • Start date Start date
J

James S. Rice

How do I retrieve the value of a field in a record in an
MS Access 2002 table using Visual Basic?
 
Value of Field = DLookup("[Fieldname]","TableName","[SomeFieldInTable] =
SomeValue")

"[SomeFieldInTable] = SomeValue" looks something like this:

If [SomeFieldInTable] is a string:
"[SomeFieldInTable] = 'SomeStringValue'"
or
"[SomeFieldInTable] = '" & Forms!MyForm!NameOfControlOnForm & "'"

If [SomeFieldInTable] is numeric:
"[SomeFieldInTable] = SomeNumericValue"
or
"[SomeFieldInTable] = " & Forms!MyForm!NameOfControlOnForm
 
How do I retrieve the value of a field in a record in an
MS Access 2002 table using Visual Basic?
Click to expand...

A couple of different ways; the DLookUp function, or opening a
Recordset (in DAO or in ADO) come to mind. What's the context? How can
you identify the table, the record, the field?
 
John,

Thanks for the quick response.

I have a database, xyz.mdb. Within xyz.mdb is a table
called Individuals. Within Individuals is a field called
EmailAddress which is self explanatory. I wish to write
some code in a VBA module that will send an email (I
believe via the SendObject property) at the first of each
month to each individual in the Individuals table. I've
tried the ADO approach, but I lack the experience of
working with recordsets, etc.

Another responder mentioned DLookUp as well.

Which approach do you think would work best for what I am
trying to do?
 
Recordset since you want to send e-mails to many individuals in the list.
This is more efficient that using DLookUp and retrieve each individual
separately.

Most Access programming books havegood coverage of DAO (if you use JET
back-end) or ADO (if you use other database engines).
 
John,

Thanks for the quick response.

I have a database, xyz.mdb. Within xyz.mdb is a table
called Individuals. Within Individuals is a field called
EmailAddress which is self explanatory. I wish to write
some code in a VBA module that will send an email (I
believe via the SendObject property) at the first of each
month to each individual in the Individuals table. I've
tried the ADO approach, but I lack the experience of
working with recordsets, etc.

Another responder mentioned DLookUp as well.

Which approach do you think would work best for what I am
trying to do?
Click to expand...

Van's right - Recordset.

Here's some air code:

Dim strAddr As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Individuals", dbOpenSnapshot)
Do Until rs.EOF
strAddr = rs!EmailAddress
<do what you want with it>
rs.MoveNext
Loop
 
John,

Thanks again for the support.

I copied and pasted the code you supplied but it didn't
work outright. I'm not sure if it is an engine problem or
what. However, after a bit of trial and error, I was able
to finally get the following to work:

Set db = DBEngine.OpenDatabase("D:\...\xyz", False, True)
Set grp_rs = db.OpenRecordset("Groups")
Set ind_rs = db.OpenRecordset("Individuals")

Do Until ind_rs.EOF
myGroup = ind_rs!GroupName
Do Until grp_rs.EOF
If grp_rs!GroupID = myGroup Then
If grp_rs!Active = True Then
If ind_rs!Active = True Then
argTo = ind_rs!EmailAddress
DoCmd.SendObject ...
grp_rs.MoveFirst
Exit Do
End If
End If
grp_rs.MoveFirst
Exit Do
End If
grp_rs.MoveNext
Loop
ind_rs.MoveNext
Loop

db.Close

Thanks so much for the great head start.
 
John,

Thanks again for the support.

I copied and pasted the code you supplied but it didn't
work outright. I'm not sure if it is an engine problem or
what. However, after a bit of trial and error, I was able
to finally get the following to work:

Set db = DBEngine.OpenDatabase("D:\...\xyz", False, True)
Click to expand...

Is this the database in which you have the code, or a different one?
If it's the same, then you could just say

Set db = CurrentDb
Set grp_rs = db.OpenRecordset("Groups")
Set ind_rs = db.OpenRecordset("Individuals")
Do Until ind_rs.EOF
myGroup = ind_rs!GroupName
Do Until grp_rs.EOF
If grp_rs!GroupID = myGroup Then
If grp_rs!Active = True Then
If ind_rs!Active = True Then
argTo = ind_rs!EmailAddress
DoCmd.SendObject ...
grp_rs.MoveFirst
Exit Do
End If
End If
grp_rs.MoveFirst
Exit Do
End If
grp_rs.MoveNext
Loop
ind_rs.MoveNext
Loop
Click to expand...


And I'd STRONGLY suggest using a query with parameters, rather than
brute-force looping through both recordsets! Something like

Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT EMailAddress FROM Groups INNER JOIN Individuals" _
& " ON Groups.GroupID = Individuals.GroupName" _
& " WHERE Groups.Active = True AND Individuals.Active = True;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.Recordcount > 0 Then
Do Until rs.EOF
argTo = rs!EmailAddress
DoCmd.SentObject
Loop
End If
 
John,

I will give the query approach a whirl and follow up. It's
definitely cleaner than my approach.

As you can probably tell, I'm not a programmer by trade -
my training is in mechanical and aerospace engineering.
I've developed my coding skills by trial and error and it
probably shows in my approach. While I can almost always
get something to eventually work, by the time it is all
said and done it is usually quite ugly and inefficient.

This is indeed the same database in which I have the VB
code, but the "Set db = CurrentDb" approach failed
originally. I just tried it again and it worked. I don't
understand these things sometimes...

With the recordset problem now in hand, I'm getting an "E-
Mail Error 2046 - The command or action 'SendObject' isn't
available now." message when I try to execute
DoCmd.SendObject. Do you happen to know what could be
causing this error?

Thanks and talk to you soon.
 
With the recordset problem now in hand, I'm getting an "E-
Mail Error 2046 - The command or action 'SendObject' isn't
available now." message when I try to execute
DoCmd.SendObject. Do you happen to know what could be
causing this error?
Click to expand...

I haven't used SendObject much so I'm not certain! Could you post the
actual code that you're using? Maybe someone will spot the problem.
 
John,

The new approach is working well. I modified the SQL
to "SELECT * ..." as I needed more fields from the JOIN.

I only have one hang up: the GroupName field in the
Individuals table is a look-up to the GroupID field in the
Groups table. If I set fldGroupName = rs!GroupName, I get
Run-time error '3265': Item not found in this collection.
If I set fldGroupName = rs!GroupID, the value returned is
a number (Groups primary key?), not text. How do I get the
text from one of the GroupName fields? There is a
GroupName field in each table.
 
I only have one hang up: the GroupName field in the
Individuals table is a look-up to the GroupID field in the
Groups table. If I set fldGroupName = rs!GroupName, I get
Run-time error '3265': Item not found in this collection.
If I set fldGroupName = rs!GroupID, the value returned is
a number (Groups primary key?), not text. How do I get the
text from one of the GroupName fields? There is a
GroupName field in each table.
Click to expand...

Hrm? Why are you storing the groupname redundantly in two tables? It
should exist in the Groups table and ONLY in the Groups table. Or have
you been victimized by Microsoft's abominable Lookup field type?

Just add the Groups table to your query, joining it to Individuals by
GroupID. Select the GroupName field from the Groups table.
 
John,

I'm running a telephony application that pulls records
from the Indivudals table and the app needs to know in
real-time to which Group each Individual belongs, hence,
the Group reference in the Individual's record via LookUp.
I understand what you mean, though, about the data
existing in only one table. I know that's how it is
supposed to be, but I would have a difficult time working
around it at this point.

My current query looks like this:

strSQL = "SELECT * FROM Groups INNER JOIN Individuals" _
& " ON Groups.GroupID = Individuals.GroupName" _
& " WHERE Groups.Active = True" _
& " AND Individuals.Active = True;"

How do I add the Groups table to this query, joining it to
Individuals by GroupID? I'm not familiar enough with this
type of query yet.

Once the query is updated, does the assignment look like
fldGroupName = rs!Groups.GroupName, or would this be legal
syntax?

Thanks again.
 
John,

I'm running a telephony application that pulls records
from the Indivudals table and the app needs to know in
real-time to which Group each Individual belongs, hence,
the Group reference in the Individual's record via LookUp.
I understand what you mean, though, about the data
existing in only one table. I know that's how it is
supposed to be, but I would have a difficult time working
around it at this point.

My current query looks like this:

strSQL = "SELECT * FROM Groups INNER JOIN Individuals" _
& " ON Groups.GroupID = Individuals.GroupName" _
& " WHERE Groups.Active = True" _
& " AND Individuals.Active = True;"

How do I add the Groups table to this query, joining it to
Individuals by GroupID? I'm not familiar enough with this
type of query yet.

Once the query is updated, does the assignment look like
fldGroupName = rs!Groups.GroupName, or would this be legal
syntax?

Thanks again.
Click to expand...

Now *I'm* the one that's confused. You already HAVE the Groups table
in this query; the join looks very strange - are you storing a
(numeric) ID in the GroupName field in Individuals? Is this a <yuck,
PTOOIE!> Lookup field? If so, bear in mind that your Individuals table
DOES NOT CONTAIN A GROUP NAME - it *appears* to, but that's just the
stupid Lookup wizard concealing the actual contents of your table
behind a combo box. It is perfectly possible (and preferable in my
opinion) to use the Relationships Window to create a relationship
between the GroupID of Groups and the appropriate foreign key field
(which I'd also name GroupID) in the Individuals table.

What fields do you actually have in the Individuals table pertaining
to groups? Do you have a GroupID field, a GroupName field, or both? Is
either of these a Lookup field?

I would recommend that you should NOT use the SELECT * syntax. Select
the fields that you want to see, from the tables where you want to see
them. As it is, I can't tell which fields exist in either table.
 
Q: Are you storing a (numeric) ID in the GroupName field
in Individuals?
A: YES.

Q: Is this a <yuck, PTOOIE!> Lookup field?
A: YES. I'M STARTING TO GET THE IMPRESSION THAT LOOKUP
FIELDS ARE BAD?

I'm going to look into the relationship/foreign key
approach.

Q: What fields do you actually have in the Individuals
table pertaining to groups?
A: GROUPNAME

Q: Do you have a GroupID field, a GroupName field, or both?
A: GROUPID AND GROUPNAME FIELDS IN GROUPS TABLE AND A
GROUPNAME FIELD IN INDIVIDUALS TABLE.

Q: Is either of these a Lookup field?
A: YES. THE GROUPNAME FIELD IN INDIVIDUALS TABLE IS A
LOOKUP TO GROUPID FIELD IN GROUPS TABLE. SIMILARLY, THERE
IS A TITLE FIELD IN INDIVIDUALS TABLE THAT IS A LOOKUP TO
TITLEID FIELD IN TITLES TABLE.

I played around with it a bit and finally gained success
with the following SQL statement. Note the abscence of the
* parameter in the SELECT:

strSQL = "SELECT Titles.Title, Individuals.FirstName," _
& " Individuals.MiddleName," _
& " Individuals.LastName," _
& " Individuals.EmailAddress," _
& " Groups.GroupName," _
& " Individuals.PrimaryPhone," _
& " Individuals.SecondaryPhone," _
& " Individuals.TertiaryPhone," _
& " Individuals.VoicePager," _
& " Individuals.DigitalPager," _
& " Individuals.Designee," _
& " Individuals.Contact" _
& " FROM Titles" _
& " INNER JOIN (Groups INNER JOIN Individuals" _
& " ON Groups.GroupID=Individuals.GroupName)" _
& " ON Titles.TitleID=Individuals.Title" _
& " WHERE (((Groups.Active)=True) AND" _
& " ((Individuals.Active)=True));"

Thanks for all of your help. I would have never figured
this out with out it. I've learned quite a bit in the last
few days.
 
Q: Are you storing a (numeric) ID in the GroupName field
in Individuals?
A: YES.
Click to expand...

ok... the name was a bit confusing.
Q: Is this a <yuck, PTOOIE!> Lookup field?
A: YES. I'M STARTING TO GET THE IMPRESSION THAT LOOKUP
FIELDS ARE BAD?
Click to expand...

See http://www.mvps.org/access/lookupfields.htm for a critique. They
have a few minor advantages and a great many severe problems.
I'm going to look into the relationship/foreign key
approach.

Q: What fields do you actually have in the Individuals
table pertaining to groups?
A: GROUPNAME
Click to expand...

which is actually a GroupID number. Ok...
Q: Do you have a GroupID field, a GroupName field, or both?
A: GROUPID AND GROUPNAME FIELDS IN GROUPS TABLE AND A
GROUPNAME FIELD IN INDIVIDUALS TABLE.

Q: Is either of these a Lookup field?
A: YES. THE GROUPNAME FIELD IN INDIVIDUALS TABLE IS A
LOOKUP TO GROUPID FIELD IN GROUPS TABLE. SIMILARLY, THERE
IS A TITLE FIELD IN INDIVIDUALS TABLE THAT IS A LOOKUP TO
TITLEID FIELD IN TITLES TABLE.
ok.

I played around with it a bit and finally gained success
with the following SQL statement. Note the abscence of the
* parameter in the SELECT:

strSQL = "SELECT Titles.Title, Individuals.FirstName," _
& " Individuals.MiddleName," _
& " Individuals.LastName," _
& " Individuals.EmailAddress," _
& " Groups.GroupName," _
& " Individuals.PrimaryPhone," _
& " Individuals.SecondaryPhone," _
& " Individuals.TertiaryPhone," _
& " Individuals.VoicePager," _
& " Individuals.DigitalPager," _
& " Individuals.Designee," _
& " Individuals.Contact" _
& " FROM Titles" _
& " INNER JOIN (Groups INNER JOIN Individuals" _
& " ON Groups.GroupID=Individuals.GroupName)" _
& " ON Titles.TitleID=Individuals.Title" _
& " WHERE (((Groups.Active)=True) AND" _
& " ((Individuals.Active)=True));"
Click to expand...

Yep. There you go! And it should work with the VBA just as well as it
will using the query as the recordsource for a report. Glad to have
been of help!
 
Back
Top