Leraning VBA for Access

[Del]

I am self-taught in VBA so I'm reading books on it to fill in the gaps in my
knowledge and strengthen my weak areas. I have come across the concept of
cloning a recordset and running the clone in tandum with the recordset behind
a form so the user sees a cleaner display. Is this a common practice in
smaller database applications?

 

[Klatuu]

I don't know how using a clone would make the display cleaner. I have never
heard that. There are; however, times when using a clone is very helpful.
For example, when you want to provide seach capability to a form, the common
practice is to use an unbound combo box with a row source based on the form's
recordset that will return an entry for each row in the recordset. Say, for
example, you have an employee table and a form to add and edit employee info,
you would probably have a field for the Employee ID and fields for the
employee's fist and last names.

You would want to seach by the Employee ID (the primary key), but show the
user the employee's name. So you could create a query for the combo's row
source like:

SELECT EmployeeID, LastName & ", " & FirstName AS FullName FROM tblEmployee;

Then you would set the bound column to 1, Column count to 2, and column
widths to 0";3"
Now all the user sees is the name as in Baggins, Frodo

Then you use the combo's After Update event to move to the selected employee:

With Me.RecordsetClone
.FindFirst "EmployeeID = " & Me.MyCombo
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With