This is my first post.. I am relatively novice with access vba. My questions relates to dlookup; I would like to compare a field in my form with the contents of a duplicate query. when I use dlookup without any criteria, the expression always only looks at the first record of the query. What would the syntax of dlookup be if I want to compare to ANY record of the query?
Many thanks, toulouse1402
Do you mean that you would like to know if a value entered in a textbox already exist in a column of a row in a table (or query)?
If so, you can indeed use the third argument (criteria) of the DLookUp() function. Let's suppose that the textbox name is TextBox_1, the Table Table_1 and the column where to search Column_1. You can use:
If IsNull(DLookUp("Column_1", "Table_1", "Column_1 = '" & Me.TextBox_1.Value & "'")) Then
' DLookUp returns Null --> The value does not exist.
' DLookUp returns the value.
If the value (and the definition of Column_1) is numeric, you don't use the single quotes around the value:
If IsNull(DLookUp("Column_1", "Table_1", "Column_1 = " & Me.TextBox_1.Value)) Then
However, as you don't need the value that DLookUp would return (you already know it: it's in TextBox_1), you can also use:
If DCount("Column_1", "Table_1", "Column_1 = '" & Me.TextBox_1.Value & "'") = 0 Then
' DCount returns zero --> The value does not exist.
This method is easier to use in a complex expression because you don't have to care for Null values (DCount always returns a number >= 0).
Notice that these domain functions (DCount, DLookUp, DMax, DMin, etc.) are rather slow and can degrade the performances of your application if they are used extensively and if their domain of application (the data set: Table or query) is large (has many rows), but only a test can tell for sure. It is then advisable to create an index on the column submitted to the search to improve the performance.
If they are called repeatedly in a procedure or a class module, the alternative to these functions consists in performing the search on a RecordSet object that remains open for the whole duration of the procedure or the existence of the instance of the class.
Thanks for your quick response! You helped me figure it out. Here is my vba:
the query is finddup (find duplicates), number field is the column name of the query contains the values of the field number that I want to check... the msg box is my way of debugging...
for some reason, i need to use the [ ] or it's not happy.. using ms access 2000.
Why would that be? thanks. toulouse1402
If IsNull(DLookup("[number field]", "finddup", "[number field] = " & Me.number)) Then
MsgBox "is null, unique"
Me.duplicateMA = False
MsgBox "not null, duplicate"
Me.duplicateMA = True
You need to enclose the names of objects (tables, columns, queries, controls, etc.) in square brackets () if those names contain spaces or other non-alphanumeric characters, with the exception of the underscore (_).
It's usually advisable to limit your naming conventions to valid characters (e.g. number_field instead of [number field]). Doing so will make your code more readable and less prone to errors.