Combo Box Query Problem

[ReWeFe]

Brief abstract explanation of my problem:

In table #3 (field #3) I have a combo box that I ONLY want to show data from table #2 (field #2) that equals data entered/selected earlier in same table #3 (field #2), which contains data from table #1 (field #2). Confused? Me too!


Detailed explanation, my tables, fields, properties, sample data, and relationships:

tblPublicationTypes (#1)
numPublicationTypeID – AutoNumber
txtPublicationType – Text

Sample Data:
1, Book
2, Magazine

tblPublicationTitles (#2)
numPublicationTitleID – AutoNumber
numPublicationTypeFKID – Number
txtPublicationTitle – Text

Sample Data:
1, 1, Book Title 1
2, 1, Book Title 2
3, 1, Book Title 3
4, 2, Magazine Title 1
5, 2, Magazine Title 2
6, 2, Magazine Title 3

tblPublications (#3)
numPublicationID – AutoNumber
numPublicationTypeFKID – Number
Field Properties set so I can chose an existing publication type by name
Combo Box
Table/Query
SELECT DISTINCTROW tblPublicationTypes.* FROM tblPublicationTypes;
Bound Column = 1
Column Count = 2
Column Widths = 0”;2”
numPublicationTitleFKID – Number
Field Properties set so I can chose an existing publication title by name
Combo Box
Table/Query
SELECT DISTINCTROW tblPublicationTitles.* FROM tblPublicationTitles;
Bound Column = 1
Column Count = 2
Column Widths = 0”;2”

Relationships:
tblPublicationTypes!numPublicationTypeID - One-To-Many - tblPublicationTitles! numPublicationTypeFKID

All the above works fine. However as I would really love to limit the choices in combo box #2, field #3, table #3 to ONLY show titles that matches type selected in previous field same table.

I tried the following, which does not work, it just prompt me for a value when I open the table for data input:
SELECT DISTINCTROW tblPublicationTitles.* FROM tblPublicationTitles WHERE ((([tblPublicationTitles].[numPublicationTypeFKID])=[numPublicationFKID]));

I appreciate any solution to this problem, thanks.

 

[Trendsetter]

I was with you til we got to this:
"However as I would really love to limit the choices in combo box #2, field #3, table #3 to ONLY show titles that matches type selected in previous field same table."

Do you want to restrict the content of the combo to be the same as a previous selection.. say Book selected >>> combo only shows Books???
If so - in the query that feeds combo#2 the criteria against the TypeID column needs to be:
Forms!frmName!TypeID
frmName will be the name of the form holding the control that is the selector and TypeID is the name of the field itself. You must also requery the combo in the afterupdate of the TypeID field.


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[ReWeFe]

Ok, the following works on a form:

SELECT tblPublicationTitles.* FROM tblPublicationTitles WHERE [tblPublicationTitles].[numPublicationTypeFKID]=[Forms]![frmPublicationsTEST]![numPublicationTypeFKID];

But only if I refresh the form data like this:

Private Sub Form_Current()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

Private Sub numPublicationTitleFKID_Enter()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

Thanks for all the advice...