Pass form parameters to query expression

Hi All -

Given:
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude & 
longitude).
3. btnOK on the form sets Me.Visible = False, but does not close the form.
4. A stored Select query based on an underlying table.
5. A controlling VBA procedure that opens the form, waits until btnOK is 
pressed, then continues executing.

Question: Can the form variables ‘latitude’ and ‘longitude’ be passed from 
the open form (or from the controlling VBA procedure) to an expression in a 
query field of the stored query without using an SQL statement?  The query 
field expression in the query design grid is:

Delta: Abs(Abs([StartLat])-Abs(latitude))+Abs(Abs([StartLon])-Abs(longitude))
(‘StartLat’ and ‘StartLon’ come from the query’s underlying table)

Avoiding the use of an SQL statement is desirable in this case because 
application evolution will require building numerous queries and query 
combinations; it would be easier to manage the evolution if variables could 
be fed into expressions in stored queries if possible.

TIA,
Jay
0
Utf
1/3/2008 8:03:02 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
1254 Views

Similar Articles

[PageSpeed] 19

What do you mean by form variables?
variables can be defined in the form's module, but can't be passed to stored 
queries.

If you mean controls on your form, then yes, they can be passed to a stored 
query.

To use a form control in a query, you have to fully qualify its reference as 
in this example:

WHERE [SomeField] = Forms!MyFormName!MyControlName
-- 
Dave Hargis, Microsoft Access MVP


"Jay" wrote:

> Hi All -
> 
> Given:
> 1. Stand-alone Access 2003 mdb database.
> 2. Custom dialog form with two textboxes for user input (latitude & 
> longitude).
> 3. btnOK on the form sets Me.Visible = False, but does not close the form.
> 4. A stored Select query based on an underlying table.
> 5. A controlling VBA procedure that opens the form, waits until btnOK is 
> pressed, then continues executing.
> 
> Question: Can the form variables ‘latitude’ and ‘longitude’ be passed from 
> the open form (or from the controlling VBA procedure) to an expression in a 
> query field of the stored query without using an SQL statement?  The query 
> field expression in the query design grid is:
> 
> Delta: Abs(Abs([StartLat])-Abs(latitude))+Abs(Abs([StartLon])-Abs(longitude))
> (‘StartLat’ and ‘StartLon’ come from the query’s underlying table)
> 
> Avoiding the use of an SQL statement is desirable in this case because 
> application evolution will require building numerous queries and query 
> combinations; it would be easier to manage the evolution if variables could 
> be fed into expressions in stored queries if possible.
> 
> TIA,
> Jay
0
Utf
1/3/2008 8:17:08 PM
Hi Dave -

You're correct, I meant controls.

Your suggestion to fully qualify the control's reference does make the query 
run properly when it is run manually.  Thanks for the solution.

I have a related question, but I'll repost.

Thanks again,
Jay


"Klatuu" wrote:

> What do you mean by form variables?
> variables can be defined in the form's module, but can't be passed to stored 
> queries.
> 
> If you mean controls on your form, then yes, they can be passed to a stored 
> query.
> 
> To use a form control in a query, you have to fully qualify its reference as 
> in this example:
> 
> WHERE [SomeField] = Forms!MyFormName!MyControlName
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Jay" wrote:
> 
> > Hi All -
> > 
> > Given:
> > 1. Stand-alone Access 2003 mdb database.
> > 2. Custom dialog form with two textboxes for user input (latitude & 
> > longitude).
> > 3. btnOK on the form sets Me.Visible = False, but does not close the form.
> > 4. A stored Select query based on an underlying table.
> > 5. A controlling VBA procedure that opens the form, waits until btnOK is 
> > pressed, then continues executing.
> > 
> > Question: Can the form variables ‘latitude’ and ‘longitude’ be passed from 
> > the open form (or from the controlling VBA procedure) to an expression in a 
> > query field of the stored query without using an SQL statement?  The query 
> > field expression in the query design grid is:
> > 
> > Delta: Abs(Abs([StartLat])-Abs(latitude))+Abs(Abs([StartLon])-Abs(longitude))
> > (‘StartLat’ and ‘StartLon’ come from the query’s underlying table)
> > 
> > Avoiding the use of an SQL statement is desirable in this case because 
> > application evolution will require building numerous queries and query 
> > combinations; it would be easier to manage the evolution if variables could 
> > be fed into expressions in stored queries if possible.
> > 
> > TIA,
> > Jay
0
Utf
1/3/2008 8:55:04 PM
Reply:

Similar Artilces:

Is It Possible To Pass Parameters to A Pass Through Query
My assumption is that's it's not. At work I use ODBC to connect to our oracle database with Access 2003. There are cases where using a pass through query runs much much faster and I then use it in a make table query to make a local table. In access you can use brackets [] to have it ask for input. May I assume there is no way to do anything like that in a pass through query? Create a PassThrough query and use code to assign the SQL to this query filtering it with a parameter, and then run it e.g Dim MyVariable As Integer MyVariable = InputBox("Please select a Number&quo...

Subdatasheet in Forms
Hi: I have a query which has a subdatasheet defined. When I run the query I see the "plus" (+) sign and am able to drill down to see the child records. However, when I tie the same query to a form, and set Default View for the form to "Datasheet", the "plus" sign disappears, and I'm unable to drill down to the child records (only the Parent record displays). Can subdatasheets not be used with forms?? I thought they could. I'd appreciate any assistance you all can provide. Thanks, Manuel On Thu, 28 Feb 2008 14:20:02 -0800, Manuel <Manuel@dis...

Linked Reports Parameters
How I can pass data from CRM to a linked Report (such as parametrs to reporting services report: CRM_Url, CRM_FilterText etc). I need to pass entity data (for examle EntityId) on aspx page. This may be what you are looking for. I use it to pass Account ID, Opportunity ID's etc... to my RS reports from buttons in CRM created using the isv.config file. Running CRM - MS SQL Reporting Services (RS) from ISV button and passing in Account/Opportunity GUID parameters to report. Here is a method of running an RS report from CRM and passing in the GUID from the record you are on to the RS re...

Date query "Excel VBA"
Is there anyway i can use vba code to create a user input box on a sale worksheet that will filter the sales orders and only display the order that equal the date entered into the input box by the end user. I am already using the autofilter feature but would like to take it on step further and make it easier for the admin clerk. Thank-Yo -- Message posted from http://www.ExcelForum.com You didn't like yesterday's suggestion???? http://groups.google.com/groups?threadm=4110273A.B3C20CDD%40msn.com "avilla <" wrote: > > Is there anyway i can use vba code to cr...

Pass multiple selected values from list box to table
How do I pass multiple selected values from a list box to a table? I've found code to use the values as criteria in a query but would now like to store the values in a table (at least temporarily) thanks in advance. jack Pass selected values from a list box to a table? I don't really think you want to do this. Can you describe more about what you are trying to accomplish? You just store raw data in a table. You don't store manipulated data in a table. Maybe you need a query to do what you want to do... -- Ryan--- If this information was helpful, please indi...

Forms in Outlook 2003
We are upgrading from Exchange 5.5 to Exchange 2003. I am wanting to build some forms but I need to store the information that is collected in the form into an access database. Then my managers need to be able to run reports from this DB. I don't have any idea how to accomplish this task. Can anyone guide me where to get help or can someone here help? You'll have to write code to push the data into the database. See http://www.outlookcode.com/d/database.htm for samples and other resources. FYI, there is a newsgroup specifically for Outlook forms issues "down the hall&quo...

Calander in Forms Mode
Hi everyone! I'm hoping someone can help me...I am trying to put together a spreadsheet someone will be entering data into. I would like this to be as easy as possible. Does any one know if in forms mode (from the Data menu) I can have a calender pop up for easy date entry? Any help will do. Thanks so much. -- Jules Jules Not from the Data>Form. See Ron de Bruin's site for adding a pop-up calendar. http://www.rondebruin.nl/calendar.htm Gord Dibben MS Excel MVP On Tue, 15 May 2007 11:06:03 -0700, Jules <Jules@discussions.microsoft.com> wrote: >Hi everyone! ...

Web Query Help...
If I try and use a web query with the following site http://www.sportsline.com/mlb/stats/playersort/regularseason/yearly/MLB/P I get a message that says the web query returned no data. I click on the arrow right by player and it highlights all the players names and their stats. Anyone know why it doesn't import the data into excel? Is there too much data? Thanks Works fine for me, although one have to do some editing since it imports some trash as well (I would import it, then select the table itself from the import and copy and paste into a new sheet, then import the next 50 and...

Query #2
Is there any query for I can check on item options ACCEPT FOOD STAMP FOR THIS ITEM I need to ad this option on all the SODA & GROCERY department. -- Thank You Harjit Singh SherGill /DBA Altaville Market 324 South Main st po box 370 Altaville CA 95221 Tel : 209-736-1677 Fax; 209-498-3660 Altavillemarket1@att.net Hi Gill Backup the database first and if you can, run these after hours. First jot down the department ID values by connecting to your db and running SELECT * FROM DEPARTMENT -- jot down the ID values for soda and groceries Then check the items you are about to upda...

How to pass an object from C++
Hello! We have a C# asp.net web application that is using a COM dll We use the tlbimp to be able to use the COM dll from C# asp.net web application The method InitRules shown below is located in the COM dll. It can be seen below the text Original. Method InitRules below is called from C# but we want to pass an object of type Handle_DS in addition to all the other parameters. You can see the modified InitRules below marked Modified Note the object of type Handle_DS is a C++ object that is created from C# asp.net application and pass into the InitRules I have also copied the whole idl fil...

Setting a value on a main form from a subform
All, Am I correct in assuming the way to set the value of a control on the main form from a subform is: Me.Parent.ControlName.Value = "XXX" Obviously it isn't because I continue to receive errors when I try to access the control. Thanks in advance. - CES CES wrote: > All, > Am I correct in assuming the way to set the value of a control on the > main form from a subform is: > > Me.Parent.ControlName.Value = "XXX" > > Obviously it isn't because I continue to receive errors when I try to > access the control. > > Thanks in advance...

Outlook Express #268
I use Outlook Express. I will be out of my office for 2 weeks and would like to leave a message as such on my email. Is there a way to do that in Outlook Express? Robin <anonymous@discussions.microsoft.com> wrote: > I use Outlook Express. I will be out of my office for 2 > weeks and would like to leave a message as such on my > email. Is there a way to do that in Outlook Express? Ask in an Outlook Express newsgroup. -- Brian Tillman ...

How do I set the print range on a form. Regards
I've created a Timesheet on a form in Excel, but when I print, it only prints a third of the form Enter this into Excel help: Define or clear a print area on a worksheet This should give you a solution "Dave K" wrote: > I've created a Timesheet on a form in Excel, but when I print, it only prints > a third of the form In Excel 200, you would select the are to print, go to "File", "Print area", "Set print area". To make it easier and quicker to print in future, you can record a macro as you print the print area and att...

Form to query
Hello everybody, I have a question concerning the "Enter Parameter Value" window that opens when I run a query. I have asked this in the access.queries newsgroup but I couldn't get the answer. Is it possible to have that as a combobox so that a list of possibilities is shown? Another question has to do with the same query. This asks a date from and to (in the query criteria >[from] And [to]. I would like the selected period to be shown in the report. Is this possible (and if so... how can I achieve that)? Thanks for all the help you can give -- Lisa Save the Dogs Onlus...

Uneditable Form
I set up a recurring email form with some code from slipstick systems used in conjection with task scheduler. It used to work fine. I've done something that changed this. I've changed and deleted some personal folders and I also attempted to edit the form. Anywhow I find now that I can find the form, but, when I select tools>forms>design a form and select it the form is sent rather that being brought up in a edit window. Does anyone have any ideas? Thanks in advance ...

Making form using Excell Help ASAP!
I've made a form. This needs to be filled out online. I can freeze cells so the user cannot mess with them, but how do I form lines.? Say, a line above 'Customer Name' in order to answer that question. This is really getting to me b/c it seems like it should be so easy... Hi Amanda not really sure what you're after but if you click in a cell and choose format / cells and click on the border tab and then the top line icon does this give you what you want? Cheers JulieD "~Amanda~" <~Amanda~@discussions.microsoft.com> wrote in message news:26819D...

100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itreal
100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itrealexams.com/ and get real time Q&As, Brain Dumps, Real Exams, Study material, E-Books, Video Exams and Labs for your all type of I.T Certification Exams. ...

date diff expression in query
I am trying to calculate the difference between two dates 'End Date' and 'Date of Visit' using a query. This is the expression I used: TimeFrame:[Date of Visit] - [End Date]. However, 'End Date' is present in both tables I have used to create this query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from 'tbl_NewInfo'. How do i specify this in the expression and thereby prevent the error msg I am getting? Thankyou You must fully qualify the field name like: [Table Name].[Field Name] I use a naming convention that resu...

Checking a forms fields for null
Hi All, I can think of a few ways to accomplish this, but would like to know of a better way. just like when you fill out forms on the web if the field is mandatory it pops up a msg say somthing like "This field isn't optional" if it is left blank. What is the best way to do this in access on a form? I'm using now: if ISNull(myFeild1) then msgbox"Please fill in myFeild1" end if If ISNull(myFeild2) then msgbox"Please fill in myFeild2" end if if ISNull(myFeild3) then msgbox"Please fill in myFeild3" end if I use s...

Filter Report by Form
I have a report with both text, date and Yes/No fields and I am trying to filter the report data with a pop-up form. I have fields I want to filter the data by. I have code that works with 'Text' fields; it is For intCounter = 1 To 5 If Me("Filter" & intCounter) <> "" Then strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And " End If If i take out ...

Grouping Form Fields on Continuous Form
Hi, I have a table which looks somewhat like this: --------------------------- | Type | Name | Value | --------------------------- | A | Ball | 1.00 | | A | Cup | 2.00 | | B | Fly | 0.50 | (...and so on) I want these displayed on a form, however, grouped by Type (similar to a report), for example Type: A Ball [Textbox] Cup [Textbox] Type: B Fly [Textbox] Is such a thing possible? The closest I can get is with multiple labels and conditional formatting. Code seems to be of little us...

Report with buttons to open specific form for each record
Is it possible to have a report that is based on a query (which is based on numerous other queries) to have a button that opens a specific form for each record on the report? I would like for the user to be able to select an appropriate button which corresponds to a form based on the results showing on the report. Thanks. -- Donna N. ...

query problem 05-09-07
Hi, I have two tables. One for employees Info, the other one for employees completed tasks. In employee’s info table, employee ID is primary key while in employee completed tasks table the employee ID is foreign key. Moreover, in the employee completed tasks table there are more than one tasks for one employee. I made a query for showing all employee‘s info and task. The problem is when I run the query, only those who have a record in the employee tasks table just show, while who do not have record do not appear. I need to sum the completed tasks even if there is no data. Any help pleas...

IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!!
IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!! Hi guys, If you are serious about your professional career and pass IT Certification exam in first attempt then Visit http://www.FreeExamKing.com and get latest Questions And Answers Dumps for Microsoft, Oracle, Cisco, CompTIA and many more, i have used and found it very helpful. ...

Parameter based on Date Field
I have a customer table with a Date of Birth field. I want to be able to prompt a user to enter a begin and end date to print out birthday letters based solely on the user entering the begin birth month and day and the end birth month and day, and not the year. How can this be done? On May 8, 1:26 pm, dh1069 <dh1...@discussions.microsoft.com> wrote: > I have a customer table with a Date of Birth field. I want to be able to > prompt a user to enter a begin and end date to print out birthday letters > based solely on the user entering the begin birth month and day and the end ...