Form Recordsource

[Metzoid]

Hi,

I have simple access database, I have two forms...first form creates a
criteria for data to be displayed on second form. i.e. based on selections
the recordsource for the second form is created and passed after opening the
form in the form of a sql statement select item1, item2, item3 from thetable
where item2=1 and item2>=3 etc. When I open this form using the first form
my access 2000 hangs and does nothing. As far as I can see I am creating the
sql statement correctly.

I have created a blank first form with a button on it which opens second
form and sets recordsource...this works perfectly.

But when I do it in my original form with list boxes and drop-downs to build
the sql statement and then click on the search button to produce the results
it hangs. I am mystified to why this is happening.

I have run debugging to find that the open form command line is passed but
when I reference the recordsource in the immediate window.. i get the
runtime error 2450 for not open message..its like it hasn't opened the form
for some reason....

Using dao 2.51/3.51 compatibility.

ANY IDEAS?

 

[David C. Holley]

Start small and confirm that your code is creating the SQL statement
correctly. Modify the code so that the instead of opening the form, the
code merely prints the SQl statement to the Immediate Window and then stops.

Debug.Print strRecordSource
Stop

This will allow you to inspect the SQL Statement. If it APPEARS correct,
then copy the output from the Immediate Window and paste it as the SQL
for a new query and try running the running on it. Another option would
be to try running the DoCmd.OpenQuery statement with the output as the
source, however I personally have never try using DoCmd.OO in debuggin -
it might work. At any rate, when you create a new query with the SQL
statement if there is a problem with the statement, Accesss should tell
you that there is a problem AND hopefully what the problem is.

Try that then post back if you're still having problems.

David H

 

[Metzoid]

Hi,

The SQL statement is perfectly correct, I have already done various tests to
verify the query is correct as it will always bring back the two records I
want...based on my selections.

Have tested with openquery and output as my sql statement...all works fine.

As I say am mystified as to what is going on....

 

[David C. Holley]

Have you pasted a generated SQL statement into the RecordSource of the
form? While I'm on it, what specific code are you using to set the
form's recordSource? If a form's recordSource returns 0 records, it can
appear as if things are hanging.

David H