Listbox control source error when controls used in expression are assigned with VBA

Question

I'm just beginning to dive further into VBA and ran into something I don't understand.

I'm building a Parts database for our maintenance shop as a knowledge base and purchasing tool. I have a form to search the database for a part needing to be ordered ("frmPartSearch"). If they know the Vendor Part Number, I have a popup form ("frmVendorPNLookUp") to search for matches for the user to approve, then I want to copy that record into the main form when they click a command button.

frmPartSearch has two comboboxes to select the classification and family of the part, which are used in the criteria of the listbox control source to show only parts with that classification/family designation. The listbox control source is:

SELECT tblPart.chrPartNumber
  , tblPart.chrDescription
  , tblKnowledgeBase.idsKB
FROM (tblFamily 
RIGHT JOIN tblPart 
  ON tblFamily.idsFamily = tblPart.lngFamilyId) 
LEFT JOIN tblKnowledgeBase 
  ON tblPart.idsPart = tblKnowledgeBase.lngPartId
WHERE (((tblPart.chrDescription) 
  Like "*" & [Forms]![frmPartSearch]![txtKeywordSearchCriteria] & "*") 
AND ((tblFamily.lngClassificationId)
  =[Forms]![frmPartSearch]![cboClassificationSelection]) 
AND ((tblFamily.chrFamilyTitle)
  =[Forms]![frmPartSearch]![cboFamilySelection]));

I have written this code and placed it in the OnClick Event of my "Copy to Main Form" button on frmVendorPNLookUp:

Private Sub cmdAddTofrmPartSearch_Click()
DoCmd.OpenForm ("frmPartSearch")
  Forms("frmPartSearch").CboClassificationSelection 
    = Forms("frmVendorPNLookUp").lstVendorPNSearchResults.Column(3)
  Forms("frmPartSearch").cboFamilySelection 
    = Forms("frmVendorPNLookUp").lstVendorPNSearchResults.Column(4)
  Forms("frmPartSearch").lstSearchResults.Requery
End Sub

I have confirmed that I am pulling the correct columns from lstVendorPNSearchResults based on the values entered into frmPartSearch's controls. However,

Forms("frmPartSearch").lstSearchResults.Requery

is not populating my list box. When I run the control source alone, I get an error ("This expression is typed incorrectly, or it is too complex to be evaluated...") It works fine if I use the comboboxes on their own, which requery the listbox AfterUpdate of each combobox.

Do I need to declare the ID field when I set the combobox value?

Shouldn't it relate the ID field when I set the value equal to a matching control source row?

Is the problem that I am using criteria in the listbox control source based on the foreign key of the table driving the Classification combobox?

Is there an easier way to accomplish this using ListBox.Selected? I don't care as much about filling in the comboboxes, just so long as the listbox shows only matching Parts based on the Vendor PN they entered.

Thanks for any help you can provide.

You've used far too many images. They're not necessary (the vast majority of the information contained could have been described in text). Images are difficult to read, impossible to copy/paste information from, and are often blocked by corporate proxies, meaning the content is unavailable to those readers. In addition, they're extremely difficult for those readers using a mobile device. Can you edit to remove as many as possible and use text instead? (And while you're at it, fix the formatting of that SQL mess you posted so that it's readable and doesn't scroll horizontally?) Thanks. — Ken White
The combo boxes on frmPartSearch, are they single-column, or do they have 2 columns with the bound column = an ID (width 0) and the text as visible column? — Andre
2 Columns with the bound ID field being hidden and the text field visible. I am setting the value of the text in my VBA. — MoondogsMaDawg
What is "frmPastSearch"?. It appears in Forms("frmPastSearch").lstSearchResults.Requery. — dev1998

Andre Andre · Accepted Answer · 2016-02-22T15:06:47

(Note: I missed your reply until now, see Replying in comments )

2 Columns with the bound ID field being hidden and the text field visible. I am setting the value of the text in my VBA

There is your problem. You need to set the ID (the bound column), not the text.

Easiest solution is probably to also include the IDs in your lstVendorPNSearchResults row source (with width = 0), and then use the values of these hidden columns when setting the combo box values in frmPartSearch instead of the text columns.

Listbox control source error when controls used in expression are assigned with VBA

1 Answers