Commenting on @Remou's absolutely correct and useful answer, I referred to the fact that the wizard with the list of finds creates really bad code. Here is the code created by the wizard when you select the PK autonomous number for the associated column (there are minor changes in the code that the wizard creates if you are looking for a text field instead of a numeric one, but this is not enough):
Private Sub Combo2_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[InventoryID] = " & Str(Nz(Me![Combo2], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub
One thing that is wrong about this is that you cannot run it on an existing control, so you end up with a combo box with a random name, and when you change the name of the combo box, you must reapply it to event, and edit it to display the name change. But this is relatively minor compared to other problems in the wizard code, which has an error of at least 2.5 problems per line of generated code.
Here is my alternative code for it:
Private Sub cmbFind_AfterUpdate() If IsNull(Me!cmbFind) Then Exit Sub With Me.RecordsetClone .FindFirst "[InventoryID] = " & Me!cmbFind If Not .NoMatch Then If Me.Dirty Then Me.Dirty = False Me.Bookmark = .Bookmark Else ' put your not found code here, but you really shouldn't need it End If End With End Sub
Firstly, there is no reason to define a record set variable of any type at all, because you can easily work with the corresponding record set directly.
Secondly, if you declare it, it's really pretty defensive programming to declare it as an Object variable. Given that .FindFirst only works in a DAO record set, it will always be a DAO record set, which is the only type of record set that the rest of the code can run on (regardless of whether the Recordset object is always a DAO record set - I " I’m not even sure that it’s true. ”Therefore, using a variable of type Object is necessary only if you are working without a DAO reference in your application.
This seems too cautious, but I believe there is no reason to declare a variable in the first place.
Thirdly, if you assign a record set to a variable, you need to clear after yourself and set the Nothing variable at the end of sub and close the clone of the form record set you created.
Fourthly, there is no reason to use a clone of a record set of forms, because RecordsetClone already exists, and all its reason for the existing one is for such use.
Fifth, handling a null value in a combo box is crazy - going ahead and cloning a recording source, even if you don't find anything for me, makes no sense. If it is Null, just exit the sub (or create a label for the exit point and go to it), instead of overcoming the cloning problem of the recordset and performing the FindFirst operation, which is known to be fruitless.
Sixth, FindFirst is inefficient - it performs sequential scanning through the field index or through the table if there is no index, so you want to avoid starting it if you do not need it in the first place.
Seventh, using Nz () to return 0 if the Null combo box will produce incorrect results, if 0 is indeed a valid value for the desired field.
Eighth, by making FindFirst, even when you deleted the value from the combo box, move the current record back to the first, and the logical behavior should instead leave the current record where it was first, before you delete the value from the search field . That is, if you are not looking, do not find something!
Ninth, using EOF as a test assumes that FindFirst does a table scan, not an index scan (I don’t know what it does or not), and that FindFirst moves the pointer to the cloned record set even if there are no results (unlike when they are not).
Tenth, why use EOF when each record set has a NoMatch property for this purpose, and another? There is no ambiguity as to what this means when testing after the FindFirst command, unlike EOF, which reports whether the record pointer has reached the end of the table or not. One NoMatch property has a narrow meaning and cannot mean anything else, and exists exactly for use after the FindFirst operation, while EOF has a much wider value, which is used as a proxy here for something else.
The eleventh and most serious defect is that the wizard code does not explicitly force SAVE if the entry is dirty before bookmarking. This is a key mistake, as it is an area in which Access has been unreliable for many years - errors that result from implicit saving, triggered by going back to the original recording by setting a bookmark, can be lost and lead to data loss. Theoretically, this is a bug that has been fixed a long time ago, but explicitly force saving before moving on to another record is best practice, since you resolve any errors in the save operation, apparently from the navigation operation.
Do I need to say more?
Why is that? My first assumption was that the wizard creates the same code in MDB / ACCDB and ADP, but ADP forms cannot return DAO sets, so you won't have FindFirst. Perhaps in ADP it uses Find instead of FindFirst. This explains why EOF is used instead of NoMatch, as there is no NoMatch on ADO records.
But why will my MDB / ACCDB be crippled by ADP requirements that have nothing to do with them? And if I am right that there is conditional code for determining whether to use Find or FindFirst, then why not go whole hangs and use the most suitable methods in the context in which the wizard is running?
This is terrible code, and you need to rewrite it every time you call the wizard. It might have been the best code, but for some unknown reason, MS decided to create the failed code. This is completely different from the code created by all the other access wizards I have ever used - in some cases they may be slightly overworked, but there is sufficient justification for this in terms of extensibility. I just can't understand why this particular wizard creates such awful code.