Access 2000 with VB6

[Ranjan Mitra via AccessMonster.com]

Hi!
I have a problem. I am developing a library software of CDs in VB6 with
Access 2000. The search command for title, artist and composer are simple
involving single fields, but I simply do not know how to search for tracks
for which I have 18 field as Track 1 Track 2 .....Track 18 all in a single
table. I tried this:
Private Sub cmdTrack_Click()
prompt$ = "Enter the full Track Name."
SearchStr$ = InputBox(prompt$, "Track Search")
For i = 1 To datLib.Recordset.Fields.Count - 1
datLib.Recordset.Index = datLib.Recordset.Fields(i).Name
datLib.Recordset.Seek "=", SearchStr$ 'and search
Next i
If datLib.Recordset.NoMatch Then 'if no match
datLib.Recordset.MoveFirst
End If
End Sub
But this dosen't work! Please help.

 

[Douglas J. Steele]

Sounds like your database could use a redesign. You shouldn't have 18 fields
in a single record: how are you going to handle those CDs with 19, 20 or
more tracks?

You need a second table, where you can store each track as a separate row.

 

[Tim Ferguson]

The search command for title, artist and composer are simple
involving single fields

Not sure that I agree with this. Titles pretty much always single
attributes, but it is common to have more than one artist and more than one
composer for a CD. You might want to think about putting these in a more
normalised structure too.

All the best


Tim F