Domain Frustration

[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?

 

[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)

 

[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.