Show drill down of data in a report or form in Access 97

Posted on 2005-03-29
Medium Priority
Last Modified: 2012-05-05
I need help with this urgently. And if at all possible I need to do this with out any VBA.

I have a query called AttachmentInv. It contains the fields IntCustNo, Model,Serial,  and Div.

I need a form/report or form I can mak look like a report that will allow the user to select a IntCustNo and for the drill down to diplay the Model(s) that are assigned to that IntCustNo. Then when you click on the Model, it would list the Serial, Div...etc.

I have tried doing this in a form with a subform, but it is not "refreshing the data" when I change the IntCustNo. So I am willing to start from scratch with any new ideas.

Thanks in advance for your help.
Question by:kcmoore
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
  • 3
  • 2
  • 2
  • +1
LVL 77

Expert Comment

ID: 13652620
Hi kcmoore,
If there is only one record per IntCustNo then just create an autoform form based on the query.
Then add a combo box using the wizard, using the option to 'find a record in my form'.


Author Comment

ID: 13652659
Oh, sorry to mention, There are multiple records, the only single instance will be with the serial number. There will be numerous IntCustNo, Model, etc...only the serial is distinct.
LVL 77

Expert Comment

ID: 13652880
So are you saying that you need to know IntCustNo and Model and Serial to identify a record uniquely?
(Serial No doesn't do it on its own?)

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

ID: 13652919
The purpose of this would be to show all of the IntCustNo first, then they can get further detail by clicking on that field, and "drilling down" through the data. So all you need to know upfront is the IntCustNo, which I was thinking would be in a combo box, just not sure if that is the correct way. Once they select the number from the combo box, then the model#s tied to that customer would come up, then if you click on a model# you would see the remaining fields (Serial, Div, etc...)
LVL 18

Expert Comment

ID: 13652926
without vba you can achieve this with nested subform:

IntCustNunber > Model > Modeldetails

put a Printbutton on the Modeldetailssubform for a report using the formfieldvalues as restrictions

look at this example for nested subforms: http://www.itsgemeiner.com/service/EE/subform_nested.mdb

regards, Franz

Author Comment

ID: 13654342

I can not view this db sample for some reason. I am using Access 97 so that may be the issue.
LVL 18

Accepted Solution

bonjour-aut earned 1500 total points
ID: 13654431
in A97 you cannot nest subforms, the example is A2000

in A97 you will need to do:

mainform - subform

mainform unbound, two dropdowns
first dropdown with rowsource IntCustNo
VBA for cbo1_Exit:  Me.cbo2.Rowsource="SELECT Model FROM modelTab WHERE ...."
on this matter see example: http://www.itsgemeiner.com/service/EE/cbotest_97.mdb -  form test1  

then you can restrict the recordsource of the subform to the content of the two formfields

so this will involve some VBAing

regards, Franz
LVL 41

Expert Comment

ID: 13657798
You will needs some VBA, and this may be useful to you:

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

764 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