ADO Help - Easy but not working

[Harrild]

i just got a different error msg. it said Syntax error in FROM clause and then i got the runtime error as before.

my code this time is

Dim search As String
Dim band As String

band = Trim(txtband.Text)

search = "select cd_serial_numbers.band_name, cd_serial_numbers.album_name, cd_serial_numbers.genre, cd_serial_numbers.single/album, cd_serial_numbers.released, cd_serial_numbers.company, cd_serial_numbers.serial from cd_serial_numbers"

If Len(band) Then
If blnFirstWhere Then
blnFirstWhere = False
search = search & " WHERE "
Else
search = search & " OR "
End If
End If

search = search & " (band_name = '" & band & "')"

Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh

When the runtime error comes up and i go debug it highlights the last line of code
adodc1.refresh i thinking i might have to use a set or let command before this line but i'm not sure.

[Stevie]

I can't see in your sql where there is a FROM.

Thats most likely your problem.

[Harrild]

its there but this time in capitals
search = "SELECT cd_serial_numbers.band_name, cd_serial_numbers.album_name, cd_serial_numbers.genre, cd_serial_numbers.single/album, cd_serial_numbers.released, cd_serial_numbers.company, cd_serial_numbers.serial FROM cd_serial_numbers"

Now can you see the Select and the FROM?

[Stevie]

Doh!

Sorry about that, I scanned through it quickly and missed the from.

[Stevie]

You can have your SQL like this, it won't help you, but will give your fingers a rest.

search = "SELECT band_name, album_name, genre, single/album, released, company, serial FROM cd_serial_numbers"

A syntax error in FROM clause is usually just a spelling mistake in your SQL.

What does the Debug.Print Search display when run?

[Harrild]

thats alright its a bit hard to see.

anyway, the sql works it just not refreshing it properly.