Check my Select Statement in Form

[OMGsh Y did I say Yes]

Good morning,



I was wondering if someone could take a look at my Select Statement and give me some insight as to how it needs to be changed to perform a specific task. Thank you in advance!

Statement in Form (QuoteLineItems2):

SELECT [Repair Directory].[PartNumber], [Repair Directory].[RepairDes] FROM [Repair Directory] WHERE ((([Repair Directory].[PartNumber])=([Forms]![QuoteLineItems2]![combo17])));

Table is Repair Directory (includes PartNumber, RepairDes)
Form is QuoteLineItems2 (combo17 ----> which is PartNumber taken from another table named Parts using the statement: "SELECT [Parts].[PartNumber], [Parts].[PartDescription] FROM Parts ORDER BY [PartNumber]; ")

The Goal:
There are several "RepairDes" for each "PartNumber". I am attempting to have the available "RepairDes" appear in a dropdown for the "PartNumber" that has been chosen on the "QuoteLineItems2" form.

Any guidance would be much appreciated!!

[TheShabz]

remove the partNumber from your SELECT portion, you dont need it. Once you remove it, just make a combobox or listbox's rowsource be that query.
so:
SELECT RepairDes FROM [Repair Directory] WHERE PartNumber = Forms!QuoteLIneItems2!combo17

further, I'd recommend renaming combo17 to something relevant so that it's easier to recall. Also, try to not have any spaces or special characters in table names or field names. just bad practice.

[OMGsh Y did I say Yes]

Thank you so much for the quick response and the tips on spaces and field names.

When I removed the PartNumber, my combobox returned blank values. When I had PartNumber in the Select Statement it returned data that did not relate to PartNumber. Any other suggestions?

[TheShabz]

throw that SELECT statement into a blank query and see if it returns results correctly. If so, there's no reason why it shouldnt work in the combobox. If not, make sure everything is spelled correctly.

[OMGsh Y did I say Yes]

Select Statement works perfect in the Query, but not in the Form. I copied and pasted from the Query, so spelling should not be an issue.

In the Query, I typed in a PartNumber manually. Is there any possibility that since PartNumber is also a SELECT type field, Access doesn't know what to do? Could any predicates or other clauses help? Drawing straws here...

[TheShabz]

Are you putting this into Combo17? If so, it wont work. Wherever the ID is stored on the form should be the control you use.

[OMGsh Y did I say Yes]

No, I believe that would cause a circular reference error. PartNumber is in Combo17 and I am trying to populate Combo19 (RepairDes).

[TheShabz]

Ok. Put the query as the rowsource (or controlsource, i forget which one) of combo19 and then in the after_update event of combo 17 put Me.combo19.Requery. That should work.

[OMGsh Y did I say Yes]

For Combo19, I have the following:
ControlSource: RepairDescription (field in the table where the data will be stored)
RowSource: SELECT RepairDes FROM [Repair Directory] WHERE PartNumber=Forms!QuoteLIneItems2!combo17; (my select statement to retrieve the partnumber in the form and tell me what types of RepairDes's are available)

For Combo17, I have the following Event:
Me.combo19.Requery

Error Message: Microsoft Office Access can't fine the object 'Me'

I must have read your instructions incorrectly, can you tell me where I screwed up? Thank you!

[ajetrumpet]

love that name!

[TheShabz]

Did you put the Requery just in the line or open up the VBA editor in put it in there? It should be in the VBA editor.

[OMGsh Y did I say Yes]

Thank you Adam!!

[OMGsh Y did I say Yes]

Hi Shabz,

I apologize for not responding sooner. Just wanted to let you know that I did the requery using a Macro and it works great. Thank you so much for the guidance and patience!!