473,838 Members | 1,637 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to implement a form to retrieve variables to customize a query

12 New Member
Hello everybody,

I would like my Database to be able to perform a Poblational Design. That is, I have a population stored in a table, which is characterized by a number of variables (Age, Gender, Height, Heart Rate...) (at all it makes more than 30 variables).

So I would like to allow the user to select a subpopulation according to the desired criteria.

Let's say, the user want to select a subpopulation with:

Gender = Female AND Heart Rate > 80

My idea, at least to beggin with, was to let the user select a maximal of 2 variables. So, I have implemented a form with 7 Drop-Down List Boxes (LB) as it follows:

LB1: A list with the 30 variable names [corresponding to the files in the Table MedicalData of the population] for choosing Variable1

LB2: [=, >, <]

LB3: Text Box Allowing the user to select a quantity/string

LB4: [AND, OR]

LB5-6: Same as LB1-3 but corresponding to Variable2.
So my question is, is there a way of passing the information retrieved by the form, to a customized query?

PD. My first idea was to program an individual query for each of the variables and then combine them in a form, but I would need to make a new query each time a new medical variable is added, and I was wondering if there is a more direct and pretty way if doing that.

Thank you very much in advance for your comments and suggestions
Nov 29 '11 #1
7 1523
3,080 Recognized Expert Specialist
I would start with proposing to teach the users the use of the right-click pop-up menu.
This will allow in a table (or form) to filter directly on the data and by adding filters Access will use an AND.

There's also a filter by form allowing an OR relation, but I think that's complexer as teaching a user to write a SELECT query in the Graphical query editor.

Nov 29 '11 #2
32,584 Recognized Expert Moderator MVP
Ambitious, but possible.

If you consider that queries are, fundamentally, just SQL commands passed as a string, then you can use VBA in your form to respond to the selections in the ComboBoxes (for such is how the drop-downs controls are named) on your form and create the relevant string which corresponds to all the selections.

You could, alternatively, create a form with such controls in the header section which simply applies a filter to itself assuming that is adequate to your requirements.
Nov 29 '11 #3
759 Contributor
Hello !
Just for subscription to this thread and some comments:

From your example: Gender = Female
What means if you replace the "=" sign with the other one from your list (< , >) ?! . Think about using a combo box for that type of data (in this case to make a choice between ALL, MALE,FEMALE)

What about if you wish to know who is in trouble with heart ?
Something like: Heart_Rate < 60 OR Heart_Rate > 80
You need to use the same variable two times.

And so on, a lot of things to think about.

More than, the boolean logic is not for any one as clear as it is for a coder (even experimented coders make mistakes when us it). And I think that you design this database for a medical team (their math is almost null).
Nov 30 '11 #4
32,584 Recognized Expert Moderator MVP
Some good points there in Mihail's post, but if you're considering presenting such a page to users I would assume you already know the users are capable of dealing with it intelligently and logically. Unfortunately, there's no facility within Access to check SQL as valid before executing it as there is in many other flavours of SQL. That would be an appropriate check if it were available. I do suggest you handle the SQL failing with a friendly error sequence though, as it's clearly possible for the SQL created to be invalid.

Whether you extend the comparison options to include the non-arithmetic operators such as Like; In(); Between ... And; etc is up to you, but doing so will add power to the page as well as complicating your design a fair bit.

NB. Remember to parenthesise each individual comparison on creation. Even if problems with such situations is rare, it doesn't hurt and also makes debugging later much easier. On that point, it may be worth building in a display of your SQL on the form so that you can check the results are as expected.
Nov 30 '11 #5
12 New Member
First of all, thanks to the 3 of you for your quick and useful answers, and sorry for the lack of feedback for a time, but I had to stop the project for several days. However I'm back.

Well, nico, the users are precisely the ones demanding a 'graphic' interface in order to facilitate the Population Design. So I don't think that solution will fullfill their expectations. Usually they do it manually, but they're handling hundreds and sometimes thousands of people!

On the other hand using Boolean Logic is OK for them, since they're not exactly Medical Doctors (MD) but Biomedical Engineers, so most of them have programmed at least once in their lives! However it is true that the final user will probably be a MD so a used-friendly interface and working-logic will be needed.

In reference to what you are mentioning:
  1. Nominal Variables: I was thinking, when a nominal variable was selected from the CombBox in the Form, to switch the second and third Input-boxes corresponding to that variable, into another Combobox with the allowed options for it (e.i. [=] and [Female/Male])
  2. Boolean Items and 'Between': For a first approach I was just thinking on allowing only the boolean operators mentioned in my first post. However, I have to admit it's a very good idea and I will consider it after getting something work in a simplified protoype of the initial idea
  3. SQL Sequences: so the key-question seems to be to pass the answers retrieved by the form as a SQL command string to the Query. Could somebody give me a piece of code with just 2 variables selected (let's say Blood Pressure = 80 AND Diagnose = 1) so it can let me figure out how to extrapolate it to the use of multiple variables and multiple booleans?to make me
  4. SQL Failing Checking: thanks for the suggestion. In effect, I was thinking in doing something like that, but first I need to make work cooperatively both the Form and the Query.

Thanks again for all your help!
Dec 13 '11 #6
32,584 Recognized Expert Moderator MVP
We don't just provide boilerplate answers for you, but we are happy to work along with you and prompt you in the right direction.

You need to start off with controls for the following items :
Expand|Select|Wrap|Line Numbers
  1. Existing filter (showing what has already been determined so far).  A label is fine for this.
  2. A list of the available/filterable fields in the record.  A ComboBox is fine for this.
  3. Comparison operator.  A ComboBox is fine for this.
  4. Main comparison value.  A TextBox is fine for this.
  5. Alternate comparison value which is only available for the 'Between' construct if supported.  A TextBox is fine for this.
You will also need to share the names of these items when you have them set up if we're to help further.

Now you have to determine which comparisons you wish to support. The code will vary greatly depending on this decision. If In() is to be supported then parsing the main comparison value will need to allow for a comma-separated list. In that case you may choose to dispense with the alternate comparison value control and allow the operator to specify two separate values (comma-separated again) in the main one.

There may well be fields which contain values which are limited to a specific list (Like an Enum). To support that you'd need an extra control that could be set up that way and you'd need to have the information as to which values are allowed somewhere too. Your code should ensure that only one of this or the main comparison value control is visible at any one time.
Dec 13 '11 #7
759 Contributor
Styles and styles.

My style is to start by designed a form, with all controls I think I need. Then, step by step, I turn, one by one, to life, each of them.

Good luck with your project !
Dec 13 '11 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Mike MacSween | last post by:
It's a form, with (so far) 4 tab pages on it, each of which holds 2/3/4 subforms. (I like tabbed forms by the way - do we all?) Basically the subforms are different ways of looking at the data. It's the orchestral management thing still. So tab 1 looks at each musician, the events they're booked onto, which jobs they're doing etc.
by: Mike | last post by:
I have to pass a date from a form to a report query. I have written a query that works fine when I execute from SQL view, But I dont know how to pass a value from the form to this query. SELECT Production.Production FROM Production WHERE (((Production.Date)=Forms!ReportForm!reportDate) And ((Production.ShiftName)="Shift1"));
by: TechBoy | last post by:
Re:Access 2002 If I base my form on an RWOP query, can I dynamically select from an RWOP query result set to display on the form? Does someone have an example? Thanks.
by: Stewart Saathoff | last post by:
Hello, I was wondering if anyone knows how to parse command line variables upon execution of an application. For example, I want to be able to type this at a command line: "myapp.exe /cid:12353" and have "myapp.exe" open and display a record for a client with the id of 12353. Does anyone know how to do that. Currently I have a text box on my form that if I enter 12353 and hit enter, the record will appear. I want this to be...
by: Paulo | last post by:
Hi everyone and thanks in advance for helping me. I´ve a form (continuous form) based on a query, wich several data fields are limited for severel fields (unbound text fields) build in footer form. everything works fine if i open directly the form. The problem is that i set up a startup form (another) wich has a command buton to open above form. Accessing the form, from the startup menu, data is empty.....opening the same form...
by: Deus402 | last post by:
Here is my table design: tblEmployers EmployerID autonum (primary key) EmployerName text tblLocations LocationID autonum (primary key) EmployerID longint (foreign key) LocationAdress text
by: Jarle Aase | last post by:
Hi, I wrote a multilanguage cms system a few years ago. Different languages are handled by loading php-files where each language-specific string is assigned to a normal php-variable ($LNG array). This loading takes place for each request. Now I'm developing a backend server in C++ that will allow some better security-checking, session management and caching. The backend server has a
by: PaulaCM | last post by:
Is there a way to create a Form based on a Query? Everyone in my database has a staff point person assigned to them. I've created a query for each person where they can find only their contacts. I'd like them to be able to use the forms to update the information, but not have to sift through the WHOLE databse to do it. Make sense? Thanks in advance for the help (and step by step instructions)! best,
by: jt196 | last post by:
I'm trying to create an editable form of fulfilled orders on my system and am running into problems with creating a form based on this query. The field that I need to update (invoice number) is read-only. I DO understand the reasons for this: The query takes information from an orders table and a transactions table which have a one to many relationship. I don't want to use any of the transaction fields, but I do need to use a query based on...
by: Tim Mullin | last post by:
Hello all, I have a problem that probably has a simple solution but I just can't seem to figure it out. I have a form (QueryScreen)that has a button that opens another form (StateSearch) in dialog mode. On the dialog form there is a list box with a list of states and a button that runs a query to return customers that are located in the selected state. After running the query, the query appears immediately in a new tab - but the dialog...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.