Display data in subForm help needed

[TeresePamela]

I'm using Access 2000.

I have 3 files, the strucures are:
FileA: Category = primary key
Mftr, Location, Address
FileB: Category, SubCat, SerNr, PartNr, Model.
(primary key is category & SubCat & SerNr)
FileC: Category, SubCat, PartNr, Trouble, DateIn, DateOut
Resolution.

FileA has one record per category. FileB has many records per Category, only one per primary Key, FileC has many records per Category + SubCat + SerNr

My first form has 4 textboxes for the user to enter one of: Category, SubCat, SerNr or Model. They then click a "FindRecord" Button which finds all records in FileB that match the input and displays the results n a list box. The user can then double click on the desired record and all records from FileC that match the category + SubCat + SerNr are displayed in a second form in a list box.

All working ok so far. However I want the data from FileA to be displayed in a subform on the first form, but don't know how to sync the records. I currently have the subform's visible property set to false until the user clicks the "findrecord" button, at this point I set the visible to True, but of course the first record in FileA is displayed which is not the desired result. I would like the subform to display the record for the category related to the data the user requested. How can I sync the records so that I can find the category from FileB based on the user supplied input, then retrieve the matching record from FileA and display it in the subform?

As usual, any and all input will be greatly appreciated.

Pam

 

[PHV]

What is the RecordSource of your subform ?
You may consider add a criteria on Category.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TeresePamela]

Hi PH,

The recordsource on the subform is FileA. What did you mean by "add a criteria on Category"?

Thanks

 

[PHV]

You may consider setting the subform's recordsource to something like this:
"SELECT * FROM FileA WHERE Category='" & the category value from the listbox displaying matching records from FileB & "'"
If Category is defined as numeric, get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244