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

 

[Pat Hartman]

RV left out a step. That being that you need to requery the combo whenever the value in the criteria field changes. So in the AfterUpdate event of numPublicationTypeFKID, put:

Me.YourCombo.Requery

Quick! what is the 5th item in the records menu of Access 95? Don't know the answer, dont use those outdated menuitem commands. Switch your code to

DoCmd.RunCommand acWhatever

 

[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

 

[Pat Hartman]

I saw what you did. I guess you didn't get the point of my post. I will be more clear. DO NOT use DoMenuIem commands that reference Access 95 menu options. Are you going to have any clue what those statements do when you look at them in six months? I don't think so. Neither will anyone else. And there is no easy way to look them up unless you keep Access 95 installed. Don't be misled by the fact that the Microsoft wizards still generate that old code, even they recommend that you don't use it.

 

[DKDiveDude]

Ops, thanks.

Yep you're right I missed your point.

Thanks, I use your example instead...