Can I Use One Form For Different Queries? And More (97)

Topic

New Reply

Is it 350k zipped?

You talked about queries but not about tables. Are you using a single table, separate tables for each kind of thing, or what? I would have suggested a field in your table(s) that referenced a chapter, as well as a field that said what kind of thing it was. That way, you wouldn’t need 20 databases, you would merely need to look at chapter 1 (or whatever) in a single database.

If the forms have the same structure and are bound to the same field names and all the queries have the same structure and the same field names or aliases, then you should be able to use a single form. I can’t say much more than that without more information.

Reply | Quote

Replies

I’m a college professor. I am writing a college textbook and I’m trying to use Access to manage the end-of-section, end-of-chapter, and testbank problems. My original design worked well when there were only a few chapters. Now that the project has expanded, I’m beginning to see that my design is causing me a lot of extra work.

I have a main database that stores the data for the problems. This part works fine and I am happy with it.

I then have a number of queries that pull out certain parts of the data. The main ones are:

1. All the problems
2. End-of-section problems
3. End-of-chapter problems
4. Testbank problems

My first mistake was to then to link each query to their own form. The forms look the same but I did not know enough to be able to use the same form for different queries. Now that I have seven sets of databases (more in a moment) and each database has about eight different queries and matching forms, making even a minor change to a form is a major undertaking.

Just today, I needed to add a field to keep problems that use the same figure together when sorting the database. That change itself was not too difficult but updating all those queries and forms took all afternoon.

So, my first question is if it is possible to easily have a form be used for multiple queries? For example, if I have queries A, B, C, and D, all with the same structure, how can I just create one form for them all?

The book will have chapters 1-20. Originally, I could not see an easy way to use one database and still be able to work with just one chapter at-a-time. So, I made the mistake of planning on having one database per chapter so I’ll end up with 20 databases. Fortunately, they will all have the same structure.

I am beginning to see just how complex this approach makes things. However, I still need to be able to work with one chapter at-a-time. Is there a way to have a query ask which chapter to use? The answer would always either be 1-20 OR, on occasion, ALL.

I know that last ALL is likely to make this more complex. To make it more complex still, the form needs to ask which query to use and then the query needs to ask which chapter. This is well beyond my skills. I don’t even know if its possible.

I’ve attached a zipped version of the database (blank) if anyone is interested.

Ronny

Reply | Quote

I’ve deleted your forms and replaced them with a single form called generic. I’ve added a new table called menu options. In the table add the form description, ( i’ve added you existing forms) and the query you wish the form to be based on.

There is a new form called menu, ope it and see the options available to you. Select the form from the list box. Optionally select the Chapter from existing entries in the table, type a new chapter number if it doesn’t exist, or leave the chapter blank to view all.

Click the open form button.

Hope this helps

Reply | Quote

I just downloaded this and took a quick look at it. From what I saw in a quick look, this is very slick and is a much better approach than what I was doing. I want to take an more detailed look at it tomorrow and learn how you are making some of those things work, both for my own understanding and in case I need to make modifications later on. (I keep figuring out little things I need to add, a process that first pointed out how troublesome my original approach was.)

One of the things I did was append a couple hundred records from two chapters to see how it handled a lot (at least to me) of data. The only quirk I found was that the drop down chapter list has one “1” for each record from that chapter and one “3” for each record from that chapter. (I just happened to pick those for pasting.) As a result, I ended up with 200 items in the chapter list with only two chapters. I can, of course, just type in the chapter to view rather than picking from from the list.

Thanks for all your work.

Ronny Richardson

Reply | Quote

on the main menu change the rowsource for the combo box from

SELECT DISTINCTROW [Problems].[Chapter] FROM [Problems];

to

SELECT DISTINCT [Problems].[Chapter] FROM [Problems];

This will give you 1 instance of 1 and 1 instance of 3 etc.

Glad to help.

Reply | Quote

I spent a lot of time banging away on this database yesterday and I think it solves every one of the problems I was having with my approach–especially after applying that patch you posted.

I still want to spend time learning how you did some of the things. I thought the way you put the query descriptions and names to select from into a table was slick.

I’ve already put this version into production. I copied all my problems from all the chapters and pasted them into this version and it’s happily churning along with well over 500 records.

Thanks a bunch for your help.

Ronny Richardson

Reply | Quote

Stewart,

I’ve been using the database with the changes you made and it’s working out well. One issue and one question has come up that I hope you can help me with.

I use a specialized testbank program to create and manage exams. Once each chapter is finished, I export those questions to four different testbanks (end-of-section, end-of-chapter, testbank, and all) for use in tests for field testings.

When a problem needs modification, I make those changes in Access. It’s a big deal to export to the testbank so I may wait until I have several changes and then I’ll go in and export that chapter again.

You may have noticed that several of the queries generate complex code that tells the testbank program exactly how to import each question. There are actually eight of these queries–all the ones with the names beginning with Export.

The problem that has cropped up is with all the chapters combined into one table, the query now (naturally) grabs all the chapters. I can manually strip out the extra chapters but I would prefer for the query to ask for the chapter to use. Is this possible?

Also, the only difference between the end-of-section, end-of-chapter, testbank, and all queries is the Usage field. A value of “1” is end-of-section, “2” is end-of-chapter, and “3” is testbank. (The All queries just don’t filter on this field.) Is it also possible to have the query to ask about filtering on this field?

If the query can do both, then I could combine all these eight queries into just two.

I tried looking at the form you created that asks which query to feed into the data entry form. I figured that a similar approach might work here. However, I guess I don’t know enough about Access because I just could not figure out how it worked.

If the changes are simple, and you have the time, you could just tell me how to make them and I’ll do it myself. I really would like to understand what is going on here and that might help me to do so.

Thanks
Ronny Richardson

Reply | Quote

Never mind the last post, I figured it out. I added a

Like [Enter Type] & “*”

and I can now use one query to handle all four conditions. I even added a similar statement so I can also just export one chapter with the query.

Thanks for all your help.

Ronny Richardson

Reply | Quote

I’m glad you worked it out. I’ve been off work so hadn’t seen your posts. Sorry for the delay in responding.

Use of the parameters in a query is exactly what I did but the paramater was pointing to the field on the menu, rather than asking for the value to be entered.

Reply | Quote

I should not post so late. I did not think about zipping the file. I’ve gone back and modified the post and included a zipped version of the file.

I use one main table for everything. There are supporting tables for difficulty level, type of problem and the like, but the mainly allow me to entier a difficult level as a 1, 2, 3, 4 for sorting and the print that out as Easy, Moderate, Hard, Very Hard on the reports. So really, there is one table.

There actually is a field in the main table for chapter. My problem is that I need to extract data on a chapter-by-chapter basis. Since I don’t know how to have a query ask what chapter to use, all I could see to do is have 20 end-of-section queries (chapters 1-20), 20 end-of-section queries, and so on.

Ronny Richardson

Reply | Quote

I haven’t looked at your database yet and probably won’t get a chance to for a day or so, but the basic syntax for parameter queries is something like this:

Select * from tblMain
Where tblMain.Chapter=[Enter chapter number];

Of course, if you have a query with joins to other tables, they have to be included in the main Select clause, but the Where clause stays essentially the same.

In the query grid, the [Enter chapter number] (you have to include the square brackets) would go in the criteria line below the field. Because Access recognizes that you don’t have a field in any of your tables named “Enter chapter number”, is will pop up a dialog prompting you to enter the value, and then it will return the records with a matching chapter number.

Reply | Quote