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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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'.

kcmooreAuthor Commented:
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.
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?)

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kcmooreAuthor Commented:
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...)
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:

regards, Franz
kcmooreAuthor Commented:

I can not view this db sample for some reason. I am using Access 97 so that may be the issue.
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: -  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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You will needs some VBA, and this may be useful to you:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.