need to get a sum of values to a text box

[cowboy]

I have a setup of relationships as you can see in the first picture. In the estimate picture is a form that shows the 3 different tables I am pulling the sum from. The query is a query I have that displays all the sums for all the bids. I need to figure out how to get the sum for the bid I am accessing to a textbox on the form.

Help plz.

[aytee111]

You can use the DLookUp function to populate a value or expression from a query/table.

[cowboy]

I tried
=DLookUp("TotalLabor","q_SumOfLabor","BidID = " & [BidID])
=DLookUp("TotalLabor","q_SumOfLabor")
=DLookUp("TotalLabor","q_SumOfLabor","BidID = '" & [BidID]"'")
=DLookUp("TotalLabor","q_SumOfLabor","BidID = " & [Form]![BidID])

None of which worked, what syntax am I missing?

[aytee111]

=DLookUp("TotalLabor","q_SumOfLabor","BidID=Forms! Formname!BidID")

[cowboy]

=DLookUp("TotalLabor","q_SumOfLabor","BidID=Forms! f_Estimate!BidID")
=DLookUp("TotalLabor","q_SumOfLabor","BidID=Forms![f_Estimate]!BidID")

I tried those and they did not work :-(

[aytee111]

I'm not sure why the second one did not work. I presume these are in the control source?

The picture of the form does not show the BidID. Is this a search form - at what point does the BidID get populated with the correct value? Do you requery the text box when you get a new BidID? These are all questions that need to be thought about.

Try this - make a copy of the query and put that same criteria put under BidID (equal to the form value). Have the form and the query open at the same time. Make sure the query is working - that way you will know if it is the Dlookup statement that is wrong or else the timing of the form - maybe BidID on the form hasn't been populated yet.

[cowboy]

record source of the form [f_Estimate] is q_Bids which is a query that I have put in a new picture.
On f_Estimate is a textbox called txtLabor which the control source is =DLookUp("TotalLabor","q_SumOfLabor","BidID=Forms![f_Estimate]!BidID")

The bid id gets passed in this query and is used to filter out which record it should be opening, but it does not currently populate any field on the form.

[aytee111]

If I have this right, the user selects a customer and the form populates the rest of the fields on the form based on the query "q-Bids" for that customer. At which time the Total Labor should also show. Did you do as I suggested in my previous post?

[cowboy]

I have posted the customer form now, as you can see in the bottom left there is a list of bids that are assigned to the current customer. Upon clicking on that box it runs code

Private Sub txtLaborHours_Click()    DoCmd.OpenForm "f_Estimate", , , "Bids.BidID =" & Me!BidID
End Sub

No I did try that because I am not sure how to do what you were saying.

[aytee111]

Can you attach your database or part thereof?

[cowboy]

Here is my database.

[cowboy]

Did you find what the problem was?

[aytee111]

I sent my reply hours ago - I wonder what happened to it?

Yes, the problem was the parameter required in the query q-SumOfLabor. I removed it and the dlookup worked.

=DLookUp("TotalLabor","q_SumOfLabor","BidID=Forms![f_Estimate]!BidID")

[cowboy]

Awesome, i had the parameter in there when I was trying out a different way to get it to work. Anything else you see that I should change?

[aytee111]

I hadn't looked at anything else!

Just one thing to say about the fields on your tables, the text fields are all 255 characters long. It would be better to define each one the size that you need, the long sizes are not only cumbersome for Access to handle (such as in querying the fields) but also if you ever go over 1000 records it will take up a huge amount of unnecessary space. (Also is a bit hokey!) And your numerics are all integer type, i.e. no decimals. One field was called Quantity (Equipment table) but it was a text. I would just check all your fields and make sure they set up are exactly as they should be.

Otherwise very impressive. There are some nice tricks there.