?
Solved

Access 2000 query parameters (or variables?)

Posted on 2003-03-08
4
Medium Priority
?
213 Views
Last Modified: 2006-11-17
In Access 2000 I have a list box for which the data source is “select . . .  from Q”
Q is a query which looks like “select . . .  from (Q1 left join Q2 on . . . ) left join Q3 on . . .  “

Q2 has a where clause which includes a form variable comparison :
(Part.id)=[forms]![F0022]![flagGroup].[value]

Q3 has a where clause which includes a form variable comparison :
(Conn.Part1)=[Forms]![F0022]![currentID])

The list box in question is also on form F0022.

My list box works fine.

However, if I create another instance of the form with
“set frm2 as new Form_F0022”
my list box query in frm2 behaves according to the control values in the original F0022.

How can I code the query parameters to pick up the contextually appropriate values (i.e.: those in the form where the list box resides)?
0
Comment
Question by:starrynighter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 400 total points
ID: 8093743
I've not encountered this particular situation before, but does changing:
Forms!F0022!currentID

to:
Form!currentID

make a difference ?
0
 

Author Comment

by:starrynighter
ID: 8095118
Yeah, that works!
Please point me to on-line doc or web pages that describe "Form!control" - I haven't seen it before.

Actually I would prefer to pass parameters to the queries rather than referencing form variables, but Access SQL does not seem to permit this in the way that other SQLs allow eg:
"select * from viewname("string", 2)"
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8095156
Form, like Me is a property that returns a reference to the current form(or active form in this case), but unlike Me, can be used in controls.

You should be able to find the reference in the Access VBA Help file (type Form Property into the Answer Wizard).
0
 

Author Comment

by:starrynighter
ID: 8099000
Good: that's pretty much what I need in designing forms which will run as multiple-instance.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question