472,344 Members | 1,244 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,344 software developers and data experts.

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

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
Nov 12 '05 #1
6 2204
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

Nov 12 '05 #2
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 <versionnumber> 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 <versionnumber> 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.google.c om...
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

Nov 12 '05 #3
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?
Nov 12 '05 #4
Got it now.

Call subroutine
DoCmd.OpenForm "formname"

Works perfectly,

Jules
Nov 12 '05 #5
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

Nov 12 '05 #6
ju*************@ndm.gov.on.ca (Jules) wrote in message news:<f5**************************@posting.google. 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_Publications 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.GoToControl "listSearchResults"

and then the event property for the OnFocus event for
listSearchResults 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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: CharitiesOnline | last post by:
Hello, I have set this script up to add paging to a search results page. Which on the first page works fine. I calculates how many pages there...
1
by: N. Graves | last post by:
Hi, I want to have a Search Dialog box that has several text box and fields to build a search and display the results in a form. I can do...
4
by: Dica | last post by:
i apologize for what is no doubt a very rudimentary question, but i'm still trying to wrap my brain around .net coding habits. in classic asp, if i...
1
by: Chris Newman | last post by:
i am developing a web based database using PHP and mySQL. Want the search interface to display a message "No results found" if search...
4
by: zakhirn | last post by:
Hello, I have an extremely new user to XML and XSL, and I would like to know how to search XML data via a input form in HTML, and have the...
1
by: grabit | last post by:
Hi Peoples i have a search page with a form field "subject" on my results page i have a paging routine . the first page lists its 10 records no...
22
by: Zytan | last post by:
I have public methods in a form. The main form calls them, to update that form's display. This form is like a real-time view of data that is...
1
Merlin1857
by: Merlin1857 | last post by:
How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.