Search form to display results in results form: can't invoke sql

[Jules]

Hi:

I have an Access 97 Search form set up - a couple of combo boxes, a
couple of text fields and a command button. I want the command button
to run an SQL script and then open the results form. I can get the
button to open the other form but I can't get it to run the module
with the subroutine in it. This is my first sorti into programming
Access so be kind - I have some SQL and ASP experience but these
modules are a problem for me.

Thanks,

Jules

[Bas Cost Budde]

Jules wrote:

Hi:

I have an Access 97 Search form set up - a couple of combo boxes, a
couple of text fields and a command button. I want the command button
to run an SQL script and then open the results form. I can get the
button to open the other form but I can't get it to run the module
with the subroutine in it. This is my first sorti into programming
Access so be kind - I have some SQL and ASP experience but these
modules are a problem for me.

Modules are just documents for your functions and procedures. You don't
run them. Just call the function/sub by its name.

--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

[Larry Linson]

Seems unlikely that you'd need to "run an SQL script" and _then_ open
another Form -- the much more common structure would be to construct the SQL
query for the search then replace the RecordSource of the results form with
that SQL.

You don't "run modules" -- you call Procedures, either Sub (which do not
return a value) or Function (which do return a value) ... but, in the
situation you describe, it would be much more likely that you'd want to put
your code in the Click event of the Command Button. Procedures exist in
modules, but are executed directly, with no reference to the module

For a Sub named YourSub with arguments A1, A2, and A3, the statement would
be:

YourSub A1,A2,A3

For a Function named MyFunc with the same arguments,

MyVariable = MyFunc(A1,A2,A3 )

Investing a little time with a self-study about Access might be a good idea.
For the raw novice, _Microsoft Access <versionnumbe r> Step by Step_
published by Microsoft Press. Some other books that begin at the beginning
and go deeper are _Special Edition - Using Microsoft Access_ by Roger
Jennings, published by Que and _Microsoft Access Inside Out" by John L.
Viescas, published by Microsoft Press.

For the power user moving to developer level, _Programming Microsoft Access
2000_ or _Programming Microsoft Access 2002_, by Rick Dobson, published by
Microsoft Press. There is a 2003 edition that I haven't seen, but I would
guess that it would be good, too.

The consensus choice for a detailed, in-depth reference for developers is
_Access <versionnumbe r> Developer's Handbook_ by Litwin, Getz, et al,
published by Sybex.

For information on other resources available to help, visit the Resources
section of http://ntaccess.tripod .com.You'll find other's views and
recommendations in the FAQ at http://www.mvps.org/access and in the archives
of this newsgroup at http://groups.google.com.

Larry Linson
Microsoft Access MVP

"Jules" <ju************ *@ndm.gov.on.ca > wrote in message
news:f5******** *************** ***@posting.goo gle.com...

Hi:

I have an Access 97 Search form set up - a couple of combo boxes, a
couple of text fields and a command button. I want the command button
to run an SQL script and then open the results form. I can get the
button to open the other form but I can't get it to run the module
with the subroutine in it. This is my first sorti into programming
Access so be kind - I have some SQL and ASP experience but these
modules are a problem for me.

Thanks,

Jules

[Jules]

Bas Cost Budde wrote:

Modules are just documents for your functions and procedures. You don't
run them. Just call the function/sub by its name.

Hmmm, thought I tried that - probably wrongly then. OK, this is what I
did. In Properties for the command button, I selected OnClick and went
into the Code builder and it popped up with a default code. I then
tried to modify the DoCmd to something that will invoke the subroutine
but I find nothing related to functions or subroutine, I tried to
OpenModule but it opened it in editing format so I could see the code.

I can't just set RowSource to the SQL code because I have to do some
manipulations first. The RowSource is actually a temporary table so I
have to perform the search (using SQL) then dump the recordset to the
temporary table.

Any other ideas?

[Jules]

Got it now.

Call subroutine
DoCmd.OpenForm "formname"

Works perfectly,

Jules

[Bas Cost Budde]

Jules wrote:

Got it now.

Call subroutine
DoCmd.OpenForm "formname"

Works perfectly,

Jules

Great.

Call is optional; but if you omit it, you must also get rid of the
parentheses around your parameters. Like

Call yourSub(par1)

becomes

yourSub par1

--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

[Jules]

ju************* @ndm.gov.on.ca (Jules) wrote in message news:<f5******* *************** ****@posting.go ogle.com>...

Got it now.

Call subroutine
DoCmd.OpenForm "formname"

Works perfectly,

Jules

Ok, I have decided to modify the form and have the results display in
a listbox below the criteria fields. Before I do this, I just want you
to know that when I select criteria then click on the "Search
Publications" command button, the query is run, the recordset is
dumped into a temporary table and then the DoCmd.OpenForm opens the
Found_Publicati ons form with the results properly displayed in the
list control. Now, I want to combine the two forms into a top area
where the criteria are set and the bottom area where the results are
displayed. I have used:

Call PerformSearch
DoCmd.GoToContr ol "listSearchResu lts"

and then the event property for the OnFocus event for
listSearchResul ts is (currently)

DoCmd.Beep

and it beeps which means that the focus has been transferred but I
don't know how to refresh the display of the new results in the lower
list box. The results table is filled with the correct results but the
list box, which is properly tied to the results table, is not
refreshing the display of the table when the criteria change.

Help please,

Jules