Combo Box - Pulling static information from field of repetitive data

[mpkramer]

Please Help!!

I have confused myself to the end of frustration and know this is easier than I am making it.

Here is the scenario:

TBL1
This is main table, sample fields..
Custody, SubCustody, Manu, Model, Serial
BLR ADM BLR HP
BLR ADM BLR HP
C7F CMC C7F IBM
BLR SUP BLR Astra
C7F HLO C7F Berry
BLR ADM BLR HP
BLR SUP BLR VIEW
BLR SUP BLR HP
BLR PER BLR Astra

TBL2
SubCustody
ADM BLR
SUP BLR
PER BLR
CMC BLR
CMC C7F
SUP C7F
ADM C7F

Trying to produce a combo box which will list only once the values in TBL1 "SubCustody". I tried using TBL2 to generate the Combo Box record source as there are actually 24 names of 5,000 repetitive entries in the SubCustody field.
The goal is to produce reports which will group the subcustody without the user having to memorize all the names (minimized list in TBL2 = 24 names). The drop down box will allow the user to pick a particular department, the relationship will allow the query to pull the records matching the choice from the combo box (TBL2) then output to a report showing all fields from TBL1 (relationship built on the field subcustody).
So I open my query or form called SubCustody, a combo box shows up, I drop the box down and choose one of the 24 names, this can cause one of two things
1. an automatic query dumping to a report showing the 400 plus records in report form.
-or-
2. same as above but includes a button on the form upon pressing brings up the query/report. (safer and preferred)

I can re-engineer if needed, TBL1 is the only important table as TBL2 was only for the combo box list.

I have lost hair over this one, any help will be greatly appreciated and if it works, I will let you know, then send me your address and I will send you something from over here in Japan.
If you need a live database let me know, I can send it too as none of this data is classified or secret, etc...

Thanks,
-mark

 

[THWatson]

Mark
Assuming that the 24 names have a distinct way to identify them (RecordID or something like that), you could do one of two things...and each works from a command button that calls the report.

1. In the query that drives the report, put criteria in the names column, such as Forms!YourFormName!YourComboBoxName

2. In the command button code, your DoCmd line would show something like...
DoCmd.Open Report "YourReportName",,"[RecordID] = Forms!YourFormName!YourComboBoxName"

If you don't have a record identifier such as RecordID, you could try using the Name in the Where clause in the DoCmd line.

Hope that helps.
Tom