Combo Box Query Problem

[DKDiveDude]

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.

 

[RV]

Create this query:

SELECT tblPublicationTitles.*
FROM tblPublicationTitles INNER JOIN
tblPublicationTypes ON tblPublicationTitles.PublicationTypeFKID
=tblPublicationTitles.numPublicationFKID;

Next, base your combobox on this query.

RV

 

[DKDiveDude]

Solution

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

 

[DKDiveDude]

Thanks.

I guess I did something similar:

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

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

 

[DKDiveDude]

Ops, thanks.

Yep you're right I missed your point.

Thanks, I use your example instead...