Combo Box in Table 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.

 

[scriverb]

Without bruising my mind here late in the evening and digging too deeply into your prediciment I have noticed the following.

SELECT DISTINCTROW tblPublicationTitles.* FROM tblPublicationTitles WHERE ((([tblPublicationTitles].[numPublicationTypeFKID])=[numPublicationFKID]));

The field [numPublicationFKID] has no table designation so ACCESS doesn't know where it is coming from. It is asking you to provide the data. The query has no other tables to join to so I don't know exactly what you are trying to compare to.

Now I could not find this name anywhere in your post so I am assuming that you are refering to [numPublicationID]. Maybe this will help you.


Bob Scriver

 

[ReWeFe]

numPublicationFKID is the second field in my third table (tblPublications)

Let me try and explain in a different way.

Let's say I have already populated tons of book and magazine titles in my second table (tblPublicationTitles)

Now the third table I mentioned (tblPublications)) is actually part of a bigger table.

In that table I first choose the publication type (book or magazine).

In the next field I then choose the title. It is here I want to limit my choices in the combo box. Instead of showing me ALL titles, books and magazines, I would like to limit to only show either book or magazine titles, DEPENDING on what I chose in the previous field.

Does this make any sense?

 

[scriverb]

What kind of format are you trying to make this happen in. Is this a form that you have designed. A Table datasheet view? Because, you see you can't just expect a table datasheet view to have access to another table. You only have access to the table you are viewing.

Let me know just what type of view you are in.

Bob Scriver

 

[scriverb]

From your original post your second field is called:
numPublicationTypeFKID not [numPublicationFKID] which is what you are using in your query as I stated previously. I think this is why you are being prompted here.

I understand now that you are using the query to populate the combobox in your table view but the query is erroring with this field name that it cannot find.



Bob Scriver

 

[GingerR]

i understand what you are trying to do. i replicated all of your tables, however i can't make it happen within a table.
i suggest making a form based on the table tblPublications. make combo boxes with the sql statements you listed above (except it would be i.e. '...where TypeID = Forms!FormName!TypeID...') and it should work.

what is in tblPublications? i mean, what kind of data? why do you have to select DISTINCT on the titles table? it seems there should already a table which has each title listed only once? how come you are doing this within a table? what are you trying to achieve?

sorry so many questions, just trying to help figure it out.

g

 

[scriverb]

In my first posting the field in red is misspelled according to the original post by ReWeFe. This is why he is getting prompted.

Bob Scriver

 

[ReWeFe]

Yeah, that the conclusion I came too.

Have to do it in a form.

Thanks...

 

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

 

[GingerR]

you can also do this:
in combo box GotFocus property, put EventProcedure and put

 me.cboComboBoxName.Requery

 

[ReWeFe]

Kewl, Thanks...