VBA Query quetion

[newboy18]

Hi, I am a beginner at VBA for Access and have only used code on my Tables, I have a database with a Table and a Query.
I want to write some VBA to use data in the Query, my questions are:
1/. Do I need to run the Query before I try to use the results, if so is it a DOCMD command?
2/. Do I use the Query like a Table and refer to it as a Recordset, and used commands like MoveFirst, MoveLast and MoveNext?

 

[jgoodman00]

1. It depends what the query does. If it is an Access Action query (i.e. INSERT, UPDATE, DELETE), then yes it should be called before you try to use the data. A normal SELECT query will behave in pretty much the same way as a table.


2. What are you trying to do? Why do you think you need to use .MoveNext etc?

James Goodman MCP

http://www.james-goodman.co.uk

 

[newboy18]

Thanks for the quick reply.
I have a table that contains Contact details, Name, Email address, Address, Telephone etc and a Query that just sorts the Email address and Name fields into ascending order, the idea is the VBA will go thorough the Query, if an Email address exists then a routine will send the contact an email and if it doesn’t then a document will be created and saved in a folder ready to be printed and posted.
The problem that I have is that I have not used VBA with Query’s yet.

 

[jgoodman00]

In that case:

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
Dim strName As String, strEmail As String

Set cnn = CurrentProject.Connection
rst.Open ("QueryName&quot

If rst.RecordCount > 0 Then
rst.Find ("Name = '" & strName & "'&quot
If Not rst.EOF Then
strEmail = rst.Fields("EmailAddress&quot
If strEmail <> &quot;&quot; Then
'Call email sub here
Else
'No email address found, do nothing
End If
Else
'User not found, do nothing
End If
Else
'No records in query, do nothing
End If

James Goodman MCP

http://www.james-goodman.co.uk

 

[newboy18]

Looks very good but the line:
rst.Open (&quot;QryEmailName&quot
Gives the error - THE CONNECTION CANNOT BE USED TO PERFORM THIS OPPERATION. IT IS EITHER CLOSED OR INVALID IN THIS CONTEXT.
I have checked the spelling of my query and it is OK.
What do you think the problem is?

 

[jgoodman00]

Whoops, I forgot to specify the connection.

Should be

rst.Open(&quot;QryEmailName&quot,cnn,adOpenStatic

James Goodman MCP

http://www.james-goodman.co.uk

 

[newboy18]

Thanks very much, it is starting to work now.
To make it run through all the names in the query I assume I will have to go to the first record and then do a DoUntil EOF loop

 

[jgoodman00]

Depends on what you need to do. Assuming you can uniquely identify a user, then using the rst.Find method will be sufficient, without a Do Until loop.

James Goodman MCP

http://www.james-goodman.co.uk

 

[newboy18]

The idea is that the job will be run once a year and will run thorough all the users in the database, if they have an email address an email will be sent but if the field is blank then a document will be saved in a folder so that it can be printed off and then posted.

 

[jgoodman00]

Ahh right. In that case, remove the rst.Find code, & create a Do until loop:

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
Dim strEmail As String

Set cnn = CurrentProject.Connection
rst.Open (&quot;QueryName&quot, cnn, adOpenStatic, adLockPessimistic

Do Until rst.EOF
If rst.Fields(&quot;EmailAddress&quot <> &quot;&quot; Then
'Send email from here
Else
'Save doc from here
End If
rst.MoveNext
Loop

James Goodman MCP

http://www.james-goodman.co.uk

 

[newboy18]

I am impressed, thankyou very much