Full Version: Dlookup Using A Query - Value From Joined Table
UtterAccess Forums > Microsoft® Access > Access Forms
edaroc
I can't seem to figure out how to use a SQL statement to retrieve a value in the DLOOKUP function:
The Form is bound to a table WorkRequests. One of the fields is JobNum, (there is a text box, txtJobNum, on the form).
I want to display the customer name, CustName, which can be retrieved with the SQL statement:
SELECT CustName FROM Orders
LEFT JOIN Customers on Customers.CustID = Orders.CustID
WHERE JobNum = {either the field JobNum in the record or using txtJobNum}
I'm figuring DLookup can be place in the Form_Current event or the text box: txtCustName
In the VBA I know I want the solution something like the following, but I don't know exactly what to put in the table/query argument and what to put in the Where argument:
Me.txtCustName.Value = Nz(IsNull(DLookup(CustName, ? , ? ),"")
I think I'm brain dead, and it's only Thursday morning!
Doug Steele
You can't use a SQL statement with DLookup. You could save the SQL statement as a query (in which case you use the query name instead of a table name in the DLookup function), but you'd be better off using a query that includes the name as the RecordSource for the form.
edaroc
Thanks for the info, Doug.
I have choices...
Since submitting my post I found Allen Browne's eLookup function.
http://allenbrowne.com/ser-42.html
1. Allen's looks good ('faster' caught my eye)
2. DLookup - requires creating a query, it's sounds like this is the least desirable solution
3. Bind the form to a query - hmm, this would require constructing a query, not a killer task though
vtd
>>3. Bind the form to a query - hmm, this would require constructing a query, not a killer task though<<
With this method, you retrieve all required data items in one integrated process, not 2 separate processes (1 for the Form's RecordSource and subsequently, 1 for the CustName). Thus, this method is likely to be more efficient than DLookUp or eLookUp. Nothing is wrong with Allen's method but it was meant for other purposes.
edaroc
Thanks for pointing that out to me.
Why is it that if I think it to myself, i.e. your statement, it doesn't seem to drive the point home, but, when someone else mentions it, the significance is apparent.
(Not that this process would be significant in every day operations, but, (1) I love to be efficient, and (2) every once in a blue moon when multiple users are 'hammering' at the system it can mean the difference between no pause and a little pause - and that makes it worth it to me to minimize.)
vtd
Agree... Like you, I want the database to be as efficient as possible just in case it gets hammered by the users...
I have seen a case where, due to heavy usage, the error "No current record" (Error Number 3021?) happened in the Form_Current Event. The error is absolutely illogical since, if there is no record, i.e. nothing can become the CurrentRecord, the the Form_Current Event should not fire. If the Form_Current Event fires then there must be a CurrentRecord but then why the error "No current record" occurred???
edaroc
That attracts my curiosity ... I would enjoying seeing the process ('under the hood') to see how it got to that point.
It makes me think of the scenario when someone announces something to the effect "Here they come!", referring to someone arriving in a car, everyone leans and looks to see the car arrive ... and they wait, and they wait, and no car appears. (Just my mind wandering around. Imagine - I have to live with this mind.)
vtd
I think the problem is that somehow, the RecordsetClone takes a bit longer than I expected (the error "No current record" comes from the RecordsetClone). I would have thought the RecordsetClone is instantiated immediately, at least with the first record as the CurrentRecord, but it does not appear to be so in this case.
Cheers
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.