Form Recordsource

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Metzoid - 11 Mar 2005 11:00 GMT

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....

ANY IDEAS?

Reply to this Message

Arvin Meyer - 11 Mar 2005 12:34 GMT

Try to duplicate your sql with an Access query. Use the build expression
wizard (right-click in the query criteria box) to add your form references.
Add them one at a time and run the query to see where the problem occurs.
Then build your form's sql based on that.

Also, I'd use the second form's Open event to pass the sql statement,
reading the criteria from the first form. I'd do that because it is easier
to see what's happening.

Signature

Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

> Hi,
>
[quoted text clipped - 19 lines]
>
> ANY IDEAS?

Reply to this Message

Metzoid - 11 Mar 2005 15:02 GMT

Hi,

SQL_Select = "SELECT tblProperty.PropID, tblProperty.Address,
tblProperty.Asking, "
SQL_Select = SQL_Select & "tblProperty.beds, tblProperty.baths, "
SQL_Select = SQL_Select & "tblProperty.recp "
SQL_Select = SQL_Select & "FROM tblProperty "
SQL_Select = SQL_Select & "WHERE ((tblProperty.requiredtype=1) "
SQL_Select = SQL_Select & "AND (tblProperty.beds>=" & Me![minbed] &
") "
SQL_Select = SQL_Select & "AND (tblProperty.Asking>=" &
Me![minprice] & ") "
SQL_Select = SQL_Select & "AND (tblProperty.Asking<=" &
Me![maxprice] & ") "
SQL_Select = SQL_Select & "AND (tblProperty.CountyID=" &
Me![Combo62] & ") "
SQL_Select = SQL_Select & "AND (tblProperty.LocalID=" & Me![localid]
& ") "
SQL_Select = SQL_Select & "AND (tblProperty.AreaID=" & Me![areaid] &
") "
SQL_Select = SQL_Select & "AND (tblProperty.pstatus=1 OR
tblProperty.pstatus=2))"
SQL_Select = SQL_Select & " ORDER BY [tblProperty].Asking;"
thesource = SQL_Select
DoCmd.Hourglass True
DoCmd.Echo False, "Please wait ... retrieving Records"
'Enumerate number of records and determine whether to show a
'summary sheet
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyRec = MyDB.OpenRecordset(SQL_Select, dbOpenDynaset,
dbSeeChanges)
MsgDialog = MB_INFO + MB_YESNO
MsgTitle = "Search Tool"
If MyRec.EOF = False Then
MyRec.MoveLast
totrecs& = MyRec.RecordCount
ServerAdd = False
If totrecs& > 1 Then
MsgResponse = MsgBox(CStr(totrecs&) & " records match your
criteria. Would you like to view a summary?", MsgDialog, MsgTitle)
If MsgResponse = IDYES Then
DoCmd.OpenForm "frm_AppRecordsSummary"
Forms![frm_AppRecordsSummary].RecordSource = SQL_Select

Now...I have checked Access queries using the SQL statement and the sql
statement itself is perfect. Openquery is also fine. But, it just will not
open the form...

I have debugged it to the point where 'DoCmd.OpenForm
"frm_AppRecordsSummary" is executed then it jumps to the Form_Activate()
under the frm_AppRecordsSummary VBA code where it does a Docmd.Maximise it
executes this and comes back to "Forms![frm_AppRecordsSummary].RecordSource
= SQL_Select" which it also completes. Then the open form is not displayed
and all I get is the sand clock icon on the screen doing nothing else.

tried putting the recordsource to the form-activate on the opening form as
well...still no success.

Regards

> Try to duplicate your sql with an Access query. Use the build expression
> wizard (right-click in the query criteria box) to add your form references.
[quoted text clipped - 40 lines]
> >
> > ANY IDEAS?

Reply to this Message