MS Access Combo Box acts like Text Box in query

[qajaq49]

In MS Access2007, I want to query (q_Notes) a table (t_Notes) in which one of the fields (t_Notes.Topic) is populated from a Combo Box based on a field in another table (t_Topics.Topic). I will have a table of several hundred records. I want to use the q_Notes query as the basis for a report showing all the notes posted for any given topic.

I've created a form (f_TopicSelect) that includes an unbound Combo Box (cboTopics) based on that same table and field (t_Topics.Topic) as in the t_Notes table that will be queried. When viewed as a stand-alone form, the Combo Box displays the list of values as expected.

I've specified the Combo Box as the criterion for the Topic field in my q_Notes query as follows:

[Forms]![f_TopicSelect]![cboTopics]

(I know the spellings are correct because I used the "Build..." function to insert these values.)

When I run the query I get a Text Box that prompts me for the value of "Forms!f_TopicSelect!cboTopics" rather than the expected Combo Box listing of allowed values. The Property Sheet for this control assures me that it is a Combo Box, that the Row/Source type is "Table/Query" and the Row Source is "t_Topics".

If I edit the properties of the Combo Box in any way and save the changes (and even if I then change them back to what they were in the start), no prompt of any kind appears for the Topic -- neither a Text Box nor a Combo Box.

Any ideas what might be going wrong here?

[ChrisPadgham]

if you are being prompted for "Forms!f_TopicSelect!cboTopics" then it does not recognise it as an object. To cut to the chase, here is what I think you want to achieve.

Create a tabular form to display notes and topics from the notes table using the form wizard. In design view add an unbound combo box that populates the list from the topics table. In the click event add the following code.

Private Sub Combo6_Click()
Me.Filter = "Topic='" & Me.Combo6.Text & "'"
Me.FilterOn = True
End Sub

[qajaq49]

Thanks, Chris -- I appreciate the "cut to the chase" help. I'll give this a try when I'm back at the office (next week).

I'm also curious, for the sake of learning this aspect of Access generally, why the object is not being recognized.

[ChrisPadgham]

Hard to say without fiddling with it.

Was the reference in a query that was specified as the source for the form? If so you were probably trying to access the object before it had been instanciated.

[qajaq49]

No, the source for the form is a separate table created just for the purposes of populating the Combo Boxes in the form and in the table being queried. The form works as expected when I open it simply as a form. It's just when I try to use it as a criterion within the query that I get the disappointing behavior. But you've given me another avenue to look into. I'll check the relationships links when I get back to the office, and see if there's something missing there.

[boblarson]

What is the SQL of the query. I'll bet you included the combo inside the SQL and didn't concatenate it in so that it would pass the VALUE and not have to try to evaluate it.

[qajaq49]

Bob, the relevant section of the SQL is this:

...WHERE (((t_Notes.Topic)=[Forms]![f_EditNotes]![cboTopics]) AND...

What do you mean by your reference to concatenating?

[boblarson]

1. Is this a saved query (not being done in code)?

2. Is this combo box on a subform perhaps?

3. As for concatenating, it has to do with the way the value is inserted and not the form reference so the query doesn't have to do an evaluation of the control.

4. Please post the ENTIRE SQL string, not just what YOU think is relevant.

[wafs]

I have the same issue as above. I did what you suggested, but my query still brings up the box with [Forms]![frmPopUpBox]![CboQuoteListComboBox] and not my drop down list. Is there any other suggestions. Below is my sql for the query.

SELECT [Quote Information Table].[Norma Quote Number], [Part Entry Table].NPart, [Part Entry Table].OriginalPart, [Part Entry Table].PartName, [Part List].PartName2, [Part Entry Table].customer_part, [Quote Information Table].customer, [Quote Information Table].qty_break, [Part Entry Table].AnnualVol, [Part Entry Table].price, [Part Entry Table].lead_time, [Part Entry Table].min_on_order, [Quote Information Table].Name, [Quote Information Table].terms, [Quote Information Table].custom3, [Quote Information Table].Address, [Quote Information Table].City, [Quote Information Table].State, [Quote Information Table].Country, [Quote Information Table].PostalCode, [Quote Information Table].contact, [Quote Information Table].[Cust Serv Rep], [Part Entry Table].[Box Quantity], [Quote Information Table].ARC, [Quote Information Table].Prototype, [Quote Information Table].SOP, [Quote Information Table].[Ex Work], [Quote Information Table].Email, [Quote Information Table].[Account Manager], [Legal Entities].Entity, [Legal Entities].Address, [Legal Entities].City, [Legal Entities].State, [Legal Entities].Zip, [Part Entry Table].Surcharge, [Quote Information Table].Comments, [Part Entry Table].Weight, [Part Entry Table].[price by], [Quote Information Table].[Quote Exp], [Part Entry Table].PriceSum, [Part Entry Table].GrandTotal, [Quote Information Table].[T&C]
FROM (([Legal Entities] INNER JOIN [Quote Information Table] ON [Legal Entities].Entity = [Quote Information Table].Entity) INNER JOIN [Part Entry Table] ON [Quote Information Table].[Norma Quote Number] = [Part Entry Table].SubformNQN) LEFT JOIN [Part List] ON ([Part Entry Table].NPart = [Part List].NPart) AND ([Part Entry Table].OriginalPart = [Part List].OrigPart)
WHERE ((([Quote Information Table].[Norma Quote Number])=[Forms]![frmPopUpBox]![CboQuoteListComboBox]));