Dlookup "where" clause problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anywone help, I have this cod

Private Sub OriginalCapitalCost_DblClick(Cancel As Integer
Me!OriginalCapitalCost = DLookup("[GrandTotal]", "[EquipmentDetailsQuery3]", "[ProposalID] = ProposalID"
End Su

Basically on my proposals table I when I double click the field OriginalCapitolCost on my form I want it to get the Grand total for all the equipment on a subform where ProposalID's (PK) both match on the EquipmentDetailsQuery3 and the Proposals Form/Table
This always come back with the first record set in the query but on the first proposal. I can see it's thinking the ProposalID is unique
I've tried this to

Private Sub OriginalCapitalCost_DblClick(Cancel As Integer
Me!OriginalCapitalCost = DLookup("[GrandTotal]", "[EquipmentDetailsQuery3]", "[ProposalID] = _& forms!Proposals!ProposalID"
End Su

And thi

Private Sub OriginalCapitalCost_DblClick(Cancel As Integer
Me!OriginalCapitalCost = DLookup("[GrandTotal]", "[EquipmentDetailsQuery3]", "[ProposalID] = forms!Proposals!ProposalID"
End Su

I even tried re-naming the ProposalsID Field in the Query to ProposalID2:ProposalsID, but it then doesn't recognise it on the Dlookup

I know the answer is simple tweak, but I can't get it right now as I am new to this, can anyone help
Much appreciated. (First Correct answer gets a mouldy snickers bar and a faded Blue Peter Badge from 1986...)
 
For the third Argument, is ProposalID supposed to be a String value or a varialbe

If the former then try "[ProposalID] = 'ProposalID'" (enclosed in single quotes to denote String type

If the latter then try "[ProposalID] = " & ProposalI

Also I think you need to change "[EquipmentDetailsQuery3]" to "EquipmentDetailsQuery3" as the second argument as the square brackets are nor required here

This would leave you wit

Private Sub OriginalCapitalCost_DblClick(Cancel As Integer
Me!OriginalCapitalCost = DLookup("[GrandTotal]", "EquipmentDetailsQuery3", "[ProposalID] = 'ProposalID'"
End Su

o

Private Sub OriginalCapitalCost_DblClick(Cancel As Integer
Me!OriginalCapitalCost = DLookup("[GrandTotal]", "EquipmentDetailsQuery3", "[ProposalID] = " & ProposalID
End Sub
 
ProposalID is an Autonumber in the Proposals Table But I solved it from your help to thi

Private Sub OriginalCapitalCost_DblClick(Cancel As Integer
Me!OriginalCapitalCost = DLookup("[GrandTotal]", "[EquipmentDetailsQuery3]", "[ProposalID] = " & Forms!proposals!ProposalID & ""
End Su

I haven't tried taking out the middle brackets as it works fine. Thanks!
 
Back
Top