i have the following fields ID, membership ID , name,video,video code and dateborrowed.
i am developing a system for a video lending club so i need to filter the table using th ID field to display only records borrowed by a specific member. Or instead of the datagrid i could use a code that counts the records with similar IDs.
this is an example of what i hav so far in the fieldsID: A100,A103,A102,A101,A104,A100 each code refers to specific person in the database. The repeated codes means that the person has more than one video or atleast it shows the number of videos one has but i still need it in actual numeric figures.
Ok, I'm gonna "push" you in the right direction which will help you tremendously in the future. We are going to drop data controls, they are to say the least stupid and useless for proper data manipulation.
First, in your app, click on Project -->> References in your menu bar. Select Microsoft Active x Data Objects .... whichever newest version you have. select the 6.0 backcompat library if you have it, will ensure your app runs in all windows versions.
At the top of your code page, add ...
In your Form Load event, add the following...
Dim sConn As String
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
On Error GoTo ErrFailed
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MyDatabaseNameHere.mdb"
oRs.Open "SELECT * FROM MyTableNameHere WHERE ID ='A100' ORDER BY [Membership ID] DESC", oConn, adOpenKeyset, adLockOptimistic
''Try to move away from split naming conventions - Membership ID to membershipid will save you tons of headaches...
If oRs.EOF = False Then
''There are records available to show...
''Add code here to manipulate your data.
datagrid.Datasource = oRs
MsgBox "Error " & Err.Description