ADO refresh problem

I was looking through my book to see how to use SQL query statements to change the recordsource.

My first question is... what would be the syntax to make the search criteria be whatever text was entered from a textbox?

ie. Let form1.ado1.recordsource = "SELECT * FROM data WHERE month = 'txtMonth.text'"

That's what I am using, but after entering txtMonth and the period it doesn't pop up a list of options so I am assuming it's not recognizing it as and object...

And i've tried something along the lines of "SELECT * FROM data WHERE month = 'January'"

And when I run it I get an error message saying

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Then another box pops up stating that form1.ado1 was unable to be refreshed...

What am I doing wrong?

Thanks for the help

Herb Moyer

When you pass the value of a textbox or variable or whatever into string, you gotta concatinate it, ie. do this:

Code:

---

ado1.RecordSource = "SELECT * FROM Data WHERE Month = '" & txtMonth & "'"
ado1.Refresh

---

Also, it may be coming up with an error message, cos you've used the name 'month' as a field name, which is also a SQL function, try this:

Code:

---

ado1.RecordSource = "SELECT * FROM Data WHERE [Month] = '" & txtMonth & "'"
ado1.Refresh

---

Do it like this:

Code:

---

form1.ado1.recordsource = "SELECT * FROM data WHERE month = '" & txtMonth.text & "'"

---

I took the advice as far as the syntax of the text box data entry... that works fine... now when i type txtMonth and the period, the options box pops up...

But I am still getting that error message...

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

I have no idea what is causing that...

just to make sure it is this...

SELECT * FROM <name of table in database> WHERE ...

correct?

I only have 1 table in my database called Data so I dont see why it'd be confused :)

Try putting the brackets around both names, ie.

Code:

---

SELECT * FROM [Data] WHERE [Month] = '" & txtMonth & "'"

---

Quote:

---

Originally posted by nullus
Try putting the brackets around both names, ie.

Code:

---

SELECT * FROM [Data] WHERE [Month] = '" & txtMonth & "'"

---

---

Ahhhhhh I figured out what it was! I feel stupid now...

In the ADO control there is a property called CommandType...

I had it set to adCmdTable and the RecordSource was data
I changed the CommandType to adCmdText and set the RecordSource to SELECT * FROM data and now my queries work fine!

Thank you guys for all the help, I really appreciate it!

Herb Moyer