-
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.
-
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
