Dynamic Queries - building from forms? (1 Viewer)

pat_nospam · Jan 8, 2004

Okay - I didn't know whether to post this in queries or forms, so here goes.

My users need to be able to have more control over extracting data from the tables in a dynamic fashion. I envision them being able to go to a form, click on a pull down box which is populated with the table names - which, when clicked populates another pull down box with field names, which they can then select multiple field names and populate a report with them.

Is such a venture possible? Is there a straight forward method to doing this?

Currently, I just generate commonly used reports for them, but they want to have more control with fewer phone calls to me for development of queries.

Thanks in advance for all your help!

Pat

pat_nospam · Jan 9, 2004

So let's say I have ListBox1, ListBox2 and ListBox3.

I want ListBox1 to have the list of Tables of the database. ListBox2 would then list the fields of the Table selected, and ListBox3 would show the data for the field selected.

I've tried implementing an After_Update( Me.Listbox#.Requery ) and then having a query like:

SELECT [Listbox2] from [ListBox1]

But it doesn't seem to take.

Any ideas?

dcx693 · Jan 9, 2004

First, it's a good idea to get some background info. Try this article from Microsoft: Query by Form (QBF) Using Dynamic QueryDef.

Another tip: if you're going to use a listbox to list out fields that the user can choose, it's best to make it a multiselect box. That, however, makes it just a bit tougher to get all the selections out of it. Here's a good article on doing that: Use Multi-Select List boxes as query parameters.

DJN · Jan 9, 2004

Have a look at this site. I think there is a db that may help. It is under ChooseReportFields.

http://www.rogersaccesslibrary.com/TableOfContents.asp

David

pat_nospam · Jan 9, 2004

DJN - That is PERFECT.

It is exactly what I was looking for. However, I am having difficulty with two items.

One, it's not marrying to my system very well and Two, there is a limit of 6 fields to select. Is there a way to increase that?

The marriage to my system is having problems with the Table List under "Example Reading a Table" which is mainly what I am interested in. In this area, we have the following query in the properties of the table pull down:

Code:

SELECT DISTINCT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Like "tbl*") AND ((MSysObjects.Type)=1));

My tables are not named tblTABLENAME, and don't have a consistent prefix to them, they are named (bad naming convention) after titles because they were imported from Paradox (a conversion of a system created in 1994 that had to be upconverted). How can I tell the query that I'm looking for tables in MSysObjects without prefacing the table names and redoing all my table references in my forms and queries.

Thank you SO MUCH for your help!

Pat

Rich · Jan 9, 2004

Remove the Like clause

pat_nospam · Jan 9, 2004

I did that and it works so long as I'm in the example I downloaded.

Once I import it into my current database, I'm having problems with the program, specifically I'm having problems with the After_Update event in the form.

I'm trying to sort through these - thanks again guys for all the support.

Here is what the debugger is highlighting after it halts. It is working perfectly in the original database, but after straight (Get External Data) importation, it halts on this.

Code:

Dim dbs As Database,

pat_nospam · Jan 9, 2004

Hmmm - I went and matched code references in the sample to my database, but now I am getting a "Type Mismatched" error, without any debugging assistance.

Any ideas?

Always frustrating to find the perfect code and then not be able to get it to import

pat_nospam · Jan 9, 2004

Okay - I've attached the file post importation where the error is appearing. If you open the Table Query form and click to select tables, you immediately get a runtime error.

Help?

WayneRyan · Jan 9, 2004

pat,

You have a reference problem. Get your code in Design
View, choose: Tools --> References

Then make sure that Microsoft DAO 3.6 is selected and elevated
to maximum priority.

You can search here for References for more documentation.

Wayne

pat_nospam · Jan 9, 2004

I could have sworn I tried that, but upon trying it again it worked! Thanks Wayne

That extra set of "eyes" sure helped!

Dynamic Queries - building from forms? (1 Viewer)

pat_nospam

Registered User.

pat_nospam

Registered User.

dcx693

Registered User.

DJN

Guest

pat_nospam

Registered User.

Rich

Guest

pat_nospam

Registered User.

pat_nospam

Registered User.

pat_nospam

Registered User.

WayneRyan

AWF VIP

pat_nospam

Registered User.

Similar threads

Users who are viewing this thread