Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-29
8
Medium Priority
?
2,114 Views
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.
0
Comment
Question by:kcmoore
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Expert Comment

by:peter57r
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'.

Pete
0
 

Author Comment

by:kcmoore
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 13652880
kcmoore,
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?)

Pete
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:kcmoore
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...)
0
 
LVL 18

Expert Comment

by:bonjour-aut
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
0
 

Author Comment

by:kcmoore
ID: 13654342
Franz,

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

Accepted Solution

by:
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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13657798
You will needs some VBA, and this may be useful to you:
http://www.experts-exchange.com/Databases/MS_Access/Q_20525110.html
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 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