Let's look at how to use the DLookup function in MS Access:
DLookup("[UnitPrice]", "Order Details", "OrderID = 10248")
In this example, you would be retrieving the UnitPrice field from the Order Details table
where the OrderID is 10248. This is the same as the following SQL statement:
SELECT UnitPrice AS Expr1
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10248));
You can also retrieve a calculation using the DLookup function. For example:
DLookup("UnitPrice * Quantity", "Order Details", "OrderID = 10248")
This example would return the UnitPrice field multiplied by the Quantity field from
the Order Details table where the OrderID is 10248. This is the same as the
following SQL statement:
SELECT UnitPrice * Quantity AS Expr1
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10248));
Public Function DLookup( field, domain, "criteria" ) as Variant
Dim strSQL as String
Dim rsDLK as DAO.Recordset
strSQL = "SELECT " & field & " FROM " & domain
IF NZ( criteria, "" ) <> "" THEN strSQL = strSQL & " WHERE " & criteria
strSQL = strSQL & ";"
SET rsDLK = CurrentDB.OpenRecordset( strSQL )
rsDLK.MoveFirst
DLookup = rsDLK.Fields(0)
rsDLK.Close
End Function
I seem to recall (that's dangerous these days) someone/article or a youtube saying that the domain of a DLookup (or possibly other domain aggregate functions) could be an SQL statement. Having said that I can not find any reference to same.
Add me to the list of those who say do it in queries rather than code. A stored query is a lot faster than running code.
I do wonder with "thousands of querydefs" if you are perhaps not making as much use of bound forms as you could be.
Actually, my position is that it is best to write as little code as possible. I've written my million lines of code so I don't need the practice nor do I have any illusion that code I write is somehow better than code written by Microsoft which has been optimized and tested millions of times. It is much more efficient to use action queries and property settings than to write custom code to do things that Access will do for you for "free". Save your efforts for the things Access cannot do for you.
Yes and no. DAO code is more flexible than a query. Allen Browne made a code converter which was later modified by Gina Whipp that makes writing SQL code a no brainer.
View attachment 70813
Nobody is suggesting that VBA code should be avoided where it is needed.
There are many occasions where a query can't do the job.
However if a query can do so, it should be faster/more efficient due to optimisation
I've not seen Gina's version of Allen Browne's utility before but I created my own which does a bit more still.
See SQL to VBA and back again
This is Colin?
I know no one is saying that. Gina's version added action Queries. Yours converts it back again? that's cool.
I usually just run it through the debug to get it back to queryDef format.
You sound surprised....
I'm not the Essex troll if you were wondering.
My version also allows you to paste the SQL into a query.
Where I do disagree with you is mainly using inbound forms.
As you say they are much more work and IMHO are rarely worth all that extra effort.
all popups and data entry is done with unbound. It takes longer to build them but you have complete control over when records are created and have much more flexibility to conform to business rules.
Access is a Rapid Application Development (RAD) tool. The point of using a RAD tool is to let it do stuff for you. By using unbound forms, you are burdened with all the baggage of using Access without the biggest benefit. If you understand how form events work, you have all the control you need. Writing all that management code yourself isn't better than letting Access do it for you. Validation code is one place where you do need to drop into VBA, rarely would you need queries or property settings to validate. You just need to understand which events to use for which purpose and if you are working with ODBC, you need to understand how Access works with ODBC so yo make the bound form as efficient as it can be.The only bound forms I use are for Continuous Forms,
When editing in the popup forms, do you check that the record you have loaded has not been edited by another user since it was opened? Without that you could be overwriting the changes made by another user.
Bound forms do this testing for you.