Domain Frustration (1 Viewer)

[Thales750]

I've never been able to get D Lookups, or D Counts, or pretty much any of those D Words.
I never had the patience, so i just use queries.
This DCount works in a click action in a button not as a control source in a textbox.

Sorry for such a basic question. How do I make this work as the Control Source in a Textbox?

DCount("nNoteID", "qryNotesCount")

Thanks all

 

[pbaldy]

Put an = before it.

 

[Thales750]

I use it to conditionally color buttons

 

[pbaldy]

Okay. Did adding = work?

 

[Pat Hartman]

I've never been able to get D Lookups, or D Counts, or pretty much any of those D Words.
I never had the patience, so i just use queries.

It is important to understand what they do so that you don't use them when you should.

A domain function works against a table or a query which is what provides the set of data in the "domain".

DCount("nNoteID", "qryNotesCount")

the first argument is a field name. the function is counting the NON-NULL values of "nNoteID" returned by "qryNotesCount".

Usually, the query selects the domain and would ignore null values of "nNoteID". Therefore

DCount("*", "qryNotesCount")

Would be a more efficient version. The first version needs to read every row in the query to create a count. Using the "*" allows the query engine to use statistics if any are available which don't require reading every row.

Each domain function runs a separate query so if you use a domain function in a query that returns 1000 rows, Access has to run 1000!!! queries. One for each row. There are far better solutions. Also never use domain functions inside a VBA loop for the same reason.

 

[Edgar_]

If you have ever written SQL statements, they follow the same order.

DLookup("ProductName", "Products", "ProductID = 101")
SELECT ProductName FROM Products WHERE ProductID = 101;

DCount("*", "Orders", "CustomerID = 'ALFKI'")
SELECT COUNT(*) FROM Orders WHERE CustomerID = 'ALFKI';

DSum("UnitPrice * Quantity", "OrderDetails", "OrderID = 10248")
SELECT SUM(UnitPrice * Quantity) FROM OrderDetails WHERE OrderID = 10248;

DAvg("UnitPrice", "Products", "CategoryID = 1")
SELECT AVG(UnitPrice) FROM Products WHERE CategoryID = 1;

DMin("UnitPrice", "Products", "CategoryID = 2")
SELECT MIN(UnitPrice) FROM Products WHERE CategoryID = 2;

DMax("UnitsInStock", "Products", "SupplierID = 3")
SELECT MAX(UnitsInStock) FROM Products WHERE SupplierID = 3;

Notice the similarity. Ignore the tooltip, just imagine you're writing SQL.

DCount("nNoteID", "qryNotesCount")
SELECT COUNT(nNoteID) FROM qryNotesCount

 

[Galaxiom]

Note that while many developers think that the first argument in a DLookup() is a fieldname it is actually an expression.

It can be a fieldname but it is not limited to that.

 

[CJ_London]

your question has a simple answer, but I fail to understand the purpose.

How do I make this work as the Control Source in a Textbox?

DCount("nNoteID", "qryNotesCount")

I use it to conditionally color buttons

the dcount as written will simply return a count of all records so will be the same for all records. So your conditional formatting may not be working as expected (assuming the reason for the textbox is so it can be referenced by conditional formatting)

 

[Pat Hartman]

the dcount as written will simply return a count of all records so will be the same for all records.

As written, the dcount will return the count of all records where nNoteID is NOT null. The value will be the same for all rows.

 

[MajP]

@Thales,
It is really a pain to write dlookups in a calculated control and reuse throughout the DB. I often build a ton of wrappers to simplify this. The beauty is you can test these outside of a query or control and verify they work.

In a recent db that had a lot here are a few.

Public Function GetDriverFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetDriverFromPath = Nz(DLookup("driverID", "tblcarShippingPaths", "pathID = " & pathID), 0)
  End If
End Function
Public Function GetPiggyBackDriver(pathID As Variant, EdgeID As Variant) As Long
  Dim CurrentDriver As Long
  If Not IsNull(pathID) And Not IsNull(EdgeID) Then
    CurrentDriver = GetDriverFromPath(pathID)
    If CurrentDriver <> 0 Then GetPiggyBackDriver = Nz(DLookup("driverID", "qrySelectPiggyback", "edgeID_FK = " & EdgeID & " AND driverID <> " & CurrentDriver), 0)
  End If
End Function
Public Function GetPiggyBackTrip(pathID As Variant, EdgeID As Variant) As Long
  Dim CurrentDriver As Long
  If Not IsNull(pathID) And Not IsNull(EdgeID) Then
    CurrentDriver = GetDriverFromPath(pathID)
    If CurrentDriver <> 0 Then GetPiggyBackTrip = Nz(DLookup("tripID", "qrySelectPiggyback", "edgeID_FK = " & EdgeID & " AND driverID <> " & CurrentDriver), 0)
  End If
End Function
Public Function GetNumberAvailablePiggybacks(pathID As Variant, EdgeID As Variant) As Long
  Dim CurrentDriver As Long
  If Not IsNull(pathID) And Not IsNull(EdgeID) Then
    CurrentDriver = GetDriverFromPath(pathID)
    If CurrentDriver <> 0 Then GetNumberAvailablePiggybacks = DCount("*", "qrySelectPiggyback", "edgeID_FK = " & EdgeID & " AND driverID <> " & CurrentDriver)
  End If
End Function
Public Function GetTotalDeliveredFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetTotalDeliveredFromPath = Nz(DLookup("SumOfCarsShipped", "qryTotalShippedPiggyBackByPath", "pathID_FK = " & pathID), 0)
  End If
End Function
Public Function GetTotalPiggyBacksFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetTotalPiggyBacksFromPath = Nz(DLookup("SumOfPiggyback", "qryTotalShippedPiggyBackByPath", "pathID_FK = " & pathID), 0)
  End If
End Function
Public Function GetTotalPassengersFromPath(pathID As Variant) As Long
  If Not IsNull(pathID) Then
    GetTotalPassengersFromPath = DCount("*", "qryPiggyBackData", "DriverPath = " & pathID)
  End If
End Function
Public Function GetNextDriver() As Long
  GetNextDriver = Nz(DLookup("driverID", "qryNextDriver"), 0)
End Function
Public Function GetStartVertexFromEdge(EdgeID As Long) As Long
  GetStartVertexFromEdge = Nz(DLookup("startVertex", "tblCarShippingEdges", "EdgeID = " & EdgeID))
End Function
Public Function GetEndVertexFromEdge(EdgeID As Long) As Long
  GetEndVertexFromEdge = Nz(DLookup("EndVertex", "tblCarShippingEdges", "EdgeID = " & EdgeID))
End Function

Some of them are wrappers on top of wrappers.

but in code or query I can get next driver from a more complicated query like
=GetNexDriver()
or get the total vehicles delivered on a given path
TotalDelivered: GetTotalDeliveredFromPath([PathID])

I can test this in the immediate window
?GetTotalDelivedFromPath(12)

 

[Thales750]

Sorry for being a bother. I didn't really check because I thought this thread would die.
As usual, I solved this problem using queries. In this case query defs.

There is a field in the Record Source "CountOfnNoteID" if this is anything but null it triggers a conditional format.
In this case it counts the number of Notes the match the record in tblSource

 

[Thales750]

When there are notes present:

The textbox is located behind the button with the transparent background.

Thanks for trying to help me.