Selecting an item from an Unbound Combo Boxes dropdown list has stopped working

[Dealbarta]

I have a database of plants (trees, shrubs, herbaceous perennials, etc) which I have planted in two gardens (Blackwithies and The Nook) originally developed using Access 2002. From time to time I have modified it to add features which I required and it has always worked perfectly with successive versions of Access.
After a hard drive failure I had to do a clean install of Windows 11 and all my apps. Fortunately I had a backup of my data. However, since the reinstallation all the Unbound Combo Boxes have stopped working giving the VBA error message ‘Can’t find project or library’. Other controls such as List Boxes and Option Buttons using macros continue to work perfectly.
There are three forms which contain Unbound Combo Boxes in which I can enter details of plants – PLANTS, PLANTS at BLACKWITHIES and PLANTS at THE NOOK – all based on a table named PLANTS. All the forms have fields for entering Genus, Species, Variety and Cultivar into the PLANTS table. There are also other fields from the PLANTS table which are not pertinent to this question. A query (FIND PLANT) combines the four fields mentioned above into a FullPlantName. It also has a hidden field which sorts the plants into alphabetical order. The FIND PLANT query is used as the Row Source in an Unbound Combo Box to select a plant to be displayed by the form. As the problem is common to all three forms I will describe its manifestation in the PLANTS form
The PLANTS form displays pictures of plants using an ActiveX control DBPics 2.0 downloaded from Ammara (see Figure 1). I use the FIND PLANT query in an Unbound
Combo Box (labelled FIND) to generate a list of plants in alphabetical order which I can scroll down to select the plant which I want the PLANTS form to display (see Figure 2).
However, when I click on a plant name I get the VBA window for the Form_PLANTS code with the error message,
Compile error:


Can’t find project or library
The VBA for the After Update [Event Procedure] for the Unbound Combo Box using the FIND PLANT query as the Row Source is:
Private Sub Combo249_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![Combo74])
Me.Bookmark = rs.Bookmark
End Sub
The ID is the key to PLANTS and is used in the FIND PLANT query to link the two together. When a failure occurs the first line is highlighted in yellow and the Str in blue (see Figure 3).
The Data for the Combo Box is shown in Figure 4.
I am not really conversant with Access VBA so I cannot work out what is wrong. Can anyone help? I would be most grateful.

[Bob Fitz]

Have you checked for missing References on the Tools menu when in the Visual Basic Editor window?

[Micron]

What is Str for? I looks like it might be a conversion function (that would be CStr, not Str). Or Str could be a UDF. AFAIK, there is no Str function in Access.

[Dealbarta]

Hello Bob, I've checked the References - see screenshot - and the only one which is shown as missing is dbPix 3.0 ActiveX Control module which I do no use. I am not sure why some references are ticked.

[Gicu]

As Micron pointed out you need to remove the Str() function. Is the ID field numeric (it should, usually autonumber)? If yes then simply remove the Str() and make sure the combo (I would suggest you give it a more meaningful name like cboFind) has two columns (ID and FullPlantName) and its Columns property is set to 2 and Column Widths set to 0";5" (or whatever width is big enough to display the longest plant name). Once you do that it should work.
I suggest you also remove the missing reference if not in use.
Cheers,

[Dealbarta]

As Micron pointed out you need to remove the Str() function. Is the ID field numeric (it should, usually autonumber)? If yes then simply remove the Str() and make sure the combo (I would suggest you give it a more meaningful name like cboFind) has two columns (ID and FullPlantName) and its Columns property is set to 2 and Column Widths set to 0";5" (or whatever width is big enough to display the longest plant name). Once you do that it should work.
I suggest you also remove the missing reference if not in use.
Cheers,

Hello Vlad,
I confirm that the ID field is the autonumber. I can also confirm that the Column Count is 2 (ID and FullPlantName), and the Column Widths are 0cm;5.875cm which is fine for displaying the longest name.
Because I know almost nothing about VBA I am not sure how much code to delete to remove the Str( ) function. When I just deleted Str(Me![Combo249]) I got the error message,
Compile Error
Syntax Error
Should it be replaced by something else?

[Gicu]

Like this:

rs.FindFirst "[ID] = " & Me.Combo74   '& Str(Me![Combo74])

Cheers,

[Welshgasman]

Gove yourself some decent control names. combo249 is not going to mean anything to anyone 6 months down the road.

[Dealbarta]

Hello Vlad,
Many thanks. Using your guidance I have now updated the VBA for all my Combo Boxes and everything is working fine.
I can now get back to using my garden database instead of tearing my hair out!

[Dealbarta]

I will do.