Harrild

Ok i got a button to execute a search and display the results on a flexigrid but VB doesnt like it for some reason.........Please help cos i have no idea where i have gone wrong.

Private Sub cmdSearchBand_Click()

Dim bandsql As String

bandsql = "Select * from cd_serial_numbers where (band_name ='" & txtband.Text & "')"

msflexigrid1.DataSource = bandsql
msflexigrid1.Refresh

End Sub


Could someone please tell me where i have gone wrong......

__________________
-{Reality is an illusion caused by by lack of drugs}-


Psyrus

What is the exact error you are getting?




Harrild
The error that i get is
Run-Time Error '424'

Object Required

__________________
-{Reality is an illusion caused by by lack of drugs}-




Psyrus
Do you have an ADO Data Control on the form?


Harrild
No there isnt a data control

So i link the flexigrid to the data control and then run the SQL string, yeah?
__________________
-{Reality is an illusion caused by by lack of drugs}-



Psyrus
If you want the ADO data control you must add it from the Components. It's Microsoft ADO Data Control 6.0 and add the Reference to Microsoft ActiveX Data Objects 2.0 Library.

Set the DataSource of the Flexgrid to the Data Control and then set the RecordSource of the Data Control to your SQL statement and do your refreshes.

That should work, it's late...



Harrild
Ok now the flexigrid displays the whole table....but now the error comes up when i try to search for a record.

Same error as before
Run-Time Error '424'
Object Required

it must be the SQL string...
__________________
-{Reality is an illusion caused by by lack of drugs}-



Psyrus
If it was the SQL string VB would give a Syntax error in SELECT statement.

Did the ADO Data Control test properly when you set it's properties?

Did you include the refernce to the Microsoft ActiveX Data Objects 2.0 Library?


Harrild

Ok the ADO Data Control tested fine
and i did include the reference to the ActiveX Data Object Controls 2.0

but i still get the same error......


__________________
-{Reality is an illusion caused by by lack of drugs}-




PaulLewis
That error you get is I think because you are not using the DataSource property correctly.

The code you gave shouldn't work even after following the advice of Psyrus because DataSource is not a string. When I try your example I get a Type Mismatch error as I would expect.

If you plan to use the MSFlexGrid, then you must first put a "Data" control onto your form. Then set the Datasource property of the MSFlexgrid to the data object (it will appear in the dropdown for the DataSource property).

I think you have already done this. Next, you may change the RecordSource property of the Data object in code or at design time. You must then refresh the Data object to "run" the query.

that is,
Data1.RecordSource="Select * from Table"
Data1.refresh

You do not even have to touch the msf control. It will be updated automatically.

Note that you can also set the recordsource property to the name of a table or query as well.

that is,
Data1.RecordSource = "myTable"
Data1.refresh

This has the same effect as the first syntax.

If this still doesn't work or make sense, you might have to write more description of which line the error happens on and what the settings of the properties I have mentioned are.

Regards
Paul Lewis



Harrild
Ok i followed your instructions and got rid of the ADO stuff and just put the plain data control in...
I linked it all up and ran it. Tried to search for a band name...and up came an error.....

Run-Time error '3078'

Microsoft Jet Databases cannot find the table or query "". Please make sure it exists and that its name is spelt correctly....

now what?

__________________
-{Reality is an illusion caused by by lack of drugs}-


PaulLewis
I see
I didn't mean for you to have to replace the ADO control - It is my fault for not reading the thread properly.

If your DB is access 2000 you should use the ADO control and the DBGrid control.

If it is access97 or earlier, the Data control and MSFlexGrid are OK but you should still consider using the ADO because it's better by far.

In your original post you mentioned FlexGrid so I was rambling on about that.

It's really easy to do this and if you already had an ADO Data Control 6.0 object (default name is adodc1) working then you are half way there!

You need to get rid of any FlexGrid you have and use the Microsoft DataGrid Control 6.0. If you are already using that one then good so far.

Set the DataGrid's DataSource property to the ADO Data Control (select form the list)

Set the Data Control's CommandType to adCmdTable
Set the Data Control's RecordSource to "cd_serial_numbers" (i.e. the name of a TABLE in your DB)

IN code, you can change the table your dataGrid displays by using this syntax
adodc1.recordsource="anotherTable"
adodc1.commandtype=adCmdTable
adodc1.refresh

To set the recordsource to a query, use this syntax
adodc1.recordsource="select * from anotherTable"
adodc1.commandtype=adCmdText
adodc1.refresh


This should be a better set of instructions than my last lot.

Give it a go.

Regards
Paul Lewis



Aldea
Hi,
I've tried something like that:
-first you set a reference to a ADO or Data control
-write the bandsql query
-set recordsource for ADO to bandsql
-set datasource property of flexgrid to ADO(not to a string,must be a "An object reference that qualifies as a data source")
-then refresh
It should look like that:
Dim bandsql As String

bandsql = "Select * from cd_serial_numbers where (band_name ='" & txtband.Text & "')"

ADODC1.RecordSource= bandsql
Set msflexigrid1.DataSource=ADODC1
ADODC1.Refresh
You must access datasource property with "Set".You can do same thing with Data Control in the same way.
Hope it helps!




Harrild
Yeah thanks for the help but it didn't work....
it doesn't like the refresh for some reason

__________________
-{Reality is an illusion caused by by lack of drugs}-




Aldea
In last example I was working with MSHFlexgrid and ADO.
You can work also with MSFlexGrid and Data Control.



Harrild
i am sorry i only saw PaulLewis's reply i tried your's Aldea now i just a problem of a syntax error which is nice.....It says

Syntax error in FROM clause

i think it is the Command Tyep in the recordsource tab of the adodc's properties. What should it be??

__________________
-{Reality is an illusion caused by by lack of drugs}-




Harrild
None of these work for me.....

PLEASE HELP anyone else w/ a different idea
__________________
-{Reality is an illusion caused by by lack of drugs}-



CGTS
Check out my reply to 'FLL' on a similar problem to yours. It is related to the data environment, however it is just the same if you have used ADO code.

http://forums.vb-world.net/showthrea...threadid=22548



Harrild
i read FLL thread but i dropped the ADO cos i just couldn't get it to work. The problem lies in executing the SQL string onto the recordsource...

the exact problem is that the data control does not want to refresh after the sql has been executed on it.


C'mon there has got to be a simple answer for this.
___________
-{Reality is an illusion caused by by lack of drugs}-