where do i put a SQL Statement in my project?

[Spawny]

Hi. I have a delema. I am not sure where to to put the following SQL statement into my project so that it will do what i want it to. It is in the Access database under QryActive. Sql :

Mysql = "SELECT * FROM Clients WHERE Status = "Active"

Cleints is the table in the database, status is a field that is not the primary key, active is what the fieldname will equal. What i want to do is click on a command button where it will load data into a listview and it will show the "Active" people of the database. Now under the command button i have the following code. It will load the list view but not the data I want it to. :
Dim mRstrans As Recordset, qryDef As QueryDef, itmx As ListItem

InitialiseListView

Set qryDef = pDatabase.QueryDefs("QryActive")

Set mRstrans = qryDef.OpenRecordset(,dbOpenSnapshot)

Do While Not mRstrans.EOF

Set itmx = LsvDetails.ListItems.Add(, "A" & CStr(mRstrans!AccountID), mRstrans!Surname)

If mRstrans!GivenName > "" Then
itmx.SubItems(1) = mRstrans!GivenName
Else
itmx.SubItems(1) = ""
End If

If mRstrans!Street > "" Then
itmx.SubItems(2) = mRstrans!Street
Else
itmx.SubItems(2) = ""
End If

mRstrans.MoveNext

Loop
mRstrans.Close

Now where would i try to put the Sql statement in to query what i want it to show?
Any help would be great and thanx in advance for those who help.

[absolut]

I'm kinda new at this so there may be a better way. But, this should do what you need if I'm understanding your question correctly.

Try Changing: Mysql = "SELECT * FROM Clients WHERE Status = "Active"

To : Mysql = "SELECT * FROM Clients WHERE Status = 'Active'"

(This will only work if the field "Active" Stores text and is not a Yes/No Field)

Or Try The Code Like This:

Dim mRstrans As Recordset, qryDef As String, itmx As ListItem

InitialiseListView

qryDef = "SELECT * FROM Clients WHERE Status = 'Active'"

Set mRstrans = qryDef.OpenRecordset("qryDef")

Do While Not mRstrans.EOF

Set itmx = LsvDetails.ListItems.Add(, "A" & CStr(mRstrans!AccountID), mRstrans!Surname)

If mRstrans!GivenName > "" Then
itmx.SubItems(1) = mRstrans!GivenName
Else
itmx.SubItems(1) = ""
End If

If mRstrans!Street > "" Then
itmx.SubItems(2) = mRstrans!Street
Else
itmx.SubItems(2) = ""
End If

mRstrans.MoveNext

Loop
mRstrans.Close