Discussion Locked

Dynamic query,User interface in Access97

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
I am working on Access 97. I want to design a query which includes fields based on user input.My problem is that the User inputs not the field values so that I can set a criteria but he/she rather enters the "FIELD NAMES".

Lets say a table in Access 97 has 10 fields with names

Last Name,First Name,Father's Name,Mother's Name,Age,Sex,Height,Weight,Color,Degree,Date

My task is to create a user interface for a query which is as follows.

When the user clicks on a button on an Access Switchboard to run this query,he may want to see any number of fields in the resulting dynaset.Lets say he wants to see only details about Sex,Height,Weight and Color of a particular person then

he/she will input the Last Name and First Name of that person and then, "THE FIELD NAMES" 'Sex'(the starting field name) and 'Color'(the ending field name) in the 4 pop up boxes which we must provide.

To summarize, we must provide 4 pop up boxes for collecting user input of these 4 items.
1) Last Name of that person
2) First Name of that person
3) NAME OF THE FIELD FROM WHERE WE SHOULD START DISPLAYING THE CORRESPONDING FIELD ENTRIES FOR THAT PARTICULAR PERSON(Last Name and First Name already entered)
4) NAME OF THE FIELD WHERE WE SHOULD STOP DISPLAYING THE CORRESPONDING FIELD ENTRIES FOR THAT PARTICULAR PERSON.

I hope I am clear and you can understand the problem.

I would really appreciate if you can suggest me a solution for this.

Thanks in advance,

Aditya Porella.

7 replies

by fofa

You would have to use VBA code to do it. I think since free form input is not the way to go because mispelling etc., you should roll through the table and display the fields on a form and offer a check box for the fields they want. Then you can dynamically generate a query using the field names you pulled from the table.

by donq

If I were attempting to create such an interface I would:

1. Create a checkbox for each Field option available on a GUI users are forces to make their selections from.

2. Create a procedure based on values from the Form in step 1 concateningselected Field text into a SELECT statement for my reports recordset.

3. Format a series of reports based on the recordsets generated above - linking each Data Source to the generated SQL STATEMENT from step 3.

4. Place an "OK" button on the form that calls a Case Select for the appropriate report based on user selected options.

5. Test and debug until I was "ticked pink"!

Have fun and remember (if you cant create an SQL SELECT statement to deliver what you want your data isn't normalized or it can't be done without modifying your data model.)

by bob sellman

I assume from your question that the use must enter the person's last name and first name.

You could, as mentioned above, set up a checkbox on your form for each field and build a query. However, since (according to your question) the user will only be displaying data about one person, why not do something like this:

Build a form where the body of the form displays the matching data (name, etc.).

Set the form up so initially the detail area (where this matching data is located) is set for visible = false.

On the form's header or footer set up the places for the user to select (from drop down boxes) or just enter the last name and first name. If you use combo boxes, remember to requery the first name after the last name combo box has been updated so only the first names with the selected last name are displayed. You could even automatically fill in the first name box using VBA code, so that if there is only one matching last name, the user doesn't have to select a first name.

Have a check box for each of the other columns. The user should check (or uncheck, depending upon how you set it up) any column that he doesn't want displayed.

Put a button on the header or footer labeled "Display data". When the userclicks this do the following in VBA code:

Set the form's detail's visible property to true.

Set each control that the user doesn't want to see so that visible = false for that control. (Or set all controls except name initially to visible = false and resent any NOT selected by the user to visible = true.)

Set the forms properties:
filteron = true
filter = "[LastName] = '" & me.cboSelectLN & "' And [FirstName] = '" & me.cboSelectFN & "'"

(The asterisks are necessary because these fields are text values.)

And finally, requery the form to diplay the matching record:
me.requery

This results in a cleaner and nicer looking interface that's all on one form.

This discussion is closed to new replies.