SQL works in Query window but not in VB

[lmawler]

Hi,

I am executing sql from VB to populate a form... (I'm building a search, so my code takes what the user inputs, queries the database, and then populates a table, to which the fields on my form are tied.)

The query runs fine (compiles and runs without throwing any errors). I can see in my "watch" in the debugger that the variable has the correct query text. But, when I open a new recordset with the query, I get no records.

To be sure it wasn't the sql, I copied my query into a query (sql view) and it executes just fine - 35 records.

the command is what you would expect:

 rst2.Open stSql 

where rst2 is the recordset (properly initialized, and showing up with everything correct in the watch), and stsql is the variable containing my sql query as a string. (showing up in source for rst2 in my watch, and running fine as a separate query). But no records in rst2.

I am concerned that it's something particular to my SQL syntax that won't work when passed/executed from VB:

"SELECT * FROM FullTable WHERE CodeA LIKE '*' & 'ABC' & '*'"
 

Any thoughts??

Thanks for your help!

[PEB]

Hi,
U've mentioned a form isn't it?

So rst.open YourSQL it's interesting like a construction.... I've seen it for the first time for this pupose and with recordset object in fact for the first time...

It's strange how it works.. but if you don't have a compile error so there is a functionnality like this!

I suggest you to try

Forms![YourForm].RecordSource = YourSQLString

But put this on Load form event procedure

:)

[lmawler]

Thanks for the idea!

The way I'm currently doing this is to have a search window that takes what information I want to find and then builds a query that writes the searched for records to a table that is permanently set up, but that I write and delete records in for each search.

So, I create a recordset to store the data from the query and then write it to the table that the form is tied to. This way, I don't have to target the individual fields.

Your command you provide seems like it will tie the whole form to the recordset. Is that true? What does that command do exactly?


>>Forms![YourForm].RecordSource = YourSQLString

I have actually tested putting in a simpler SQL string into the stSql, and it works just fine (inside rst.Open stSql). So, I think it's the syntax of the SQL string... which works in the query window, but not when passed this way.

Is it a function of using rst.Open? Would it work if I put the same SQL statement into Forms![yourForm].RecordSource = YourSQLString? Or is this an issue with my syntax conflicting with VB?

Thanks!
Lea Ann

[lmawler]

I see. Since my query has the same fields as the table my fields are tied to, the command just overrides the original table I was tied to?

However it works, it does!

Thanks!!
Lea Ann

[lmawler]

Hi again,

If I need to put data from the form recordsource into a field, how would i do that?

I had my fields on the form tied to the search table I referred to, but if I typed in the field, it was modifying my original table. So I unbound them, but I don't know how to refer to the recordsource data to target the unbound fields.

Thoughts?

thanks,
Lea Ann

[PEB]

It seems strange...

In the same time U want that the field is bound and Unbound???

When a field is bound - the data appears in the respective field and when it's modified so it's also modified in the source object!

Obviously you need only to visualize the information without modifications...

So in your form you can activate the property Allow Edits As False!

:)