-
Hi, I have a problem with listviews. I have a form that has a listview on it and the problem isn't loading the data into the listview it, it is loading certain data into the listview. For the listview I have a query that brings 2 tables together. From the clinets table i have a primary key called AccountID and from the results table i have a foriegn key called RAccountID and a 1 is to many relationship goes as 1 Client ID(AccountID) as to many ResultIDs RaccountID). At present the listview will load all transactions into the listview irrelevant of the AccountIDs. What i need is some way of only loading the clientsID transactions only in the listview not every ones. The SQL statement i am using is:
SELECT * FROM Clients, Results WHERE Clients.AccountID = Results.RAccountID ORDER BY Surname;
ok and Function LoadTransactions is (Loads ListView) :
Dim mRstrans As Recordset, itmx As ListItem, qryDef As QueryDef, strParm As String
Dim Account1 As String, Account2 As String
InitialiseListView
Set qryDef = pDatabase.QueryDefs("QryResults")
Set mRstrans = qryDef.OpenRecordset(, dbOpenSnapshot)
Do While Not mRstrans.EOF
Account1 = mRstrans!AccountID
Account2 = mRstrans!RAccountID
Account1 = Account2
Set itmx = LsvWDetails.ListItems.Add(, "A" & CStr(mRstrans!TransID), Format$(mRstrans!RDate, "dd/mm/yyyy"))
If mRstrans!RComments > "" Then
itmx.SubItems(1) = mRstrans!RComments
Else
itmx.SubItems(1) = ""
End If
etc
mRstrans.MoveNext
Loop
mRstrans.Close
Now what or how can i only load certain records with the same fields into the listview without having every record being loaded into the listview. Any help would be much aprreciated. Thanx in Advance.
Mike
-
First of all you cant use Group By on a '*'.
If you want to represent each ID only ones you have modify your select statement:
SELECT DISTINCT Clients.AccountID, Clients.Surname, Results.RAccountID, Results.Surname FROM Clients INNER JOIN Results ON Clients.AccountID = Results.RAccountID ORDER BY Clients.Surname, Clients.Surname, Results.RAccountID, Results.Surname;
Regards,
------------------
Serge
Software Developer
[email protected]
[email protected]
-
Hi,
If I understand you correctly, you want to display only one ClientID records in the listview. For example, if you have a clientid of 20 then you want to display all the results of clientid 20.
If this is what you want, then SQL Statement is right, but you never tell which Clientid to select which is why you always get everyones results.
Change your SQL Statement to:
SELECT * FROM Clients, Results WHERE Clients.AccountID = Results.RAccountID AND Clients.AccountID=SelectedIDGoesHere
ORDER BY Surname;
You replace SelectedIDGoesHere with the ID that you want.
HTH,
Preeti
