adding fields in a table and having the output fill fields on a form

[Jon123]

I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.

SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));

That is the query.

 

[namliam]

cant you use the query as a recordsource for your form?

If you want to "simply" fetch the sum, perhaps a DLookup instead of your DCount.

 

[Jon123]

so I run the query and then have the Dlookup grad the sum from that query?

The dlookup will be the control source for the field?

 

[namliam]

The dlookup will run the query for you... But cant you use the query as the rowsource for your form?

 

[Jon123]

yes I can do that. Let me mess around with this for a bit

 

[Jon123]

=DLookUp("[HoursDelay]","queryname","[CausedBy]=" & [apple])

I still cant get the the total hours of the delays out of the query. Again the query on its own runs and does do the totals. I want to get the totals.

any help please

 

[MarkK]

To get table data out of a query you use a recordset, so if you have a query you like, with mutiple fields of data you want to use, do something like this . . .

dim rst as dao.recordset
set rst = currentdb.openrecordset( _
   "SELECT Field1, Field2 " & _
   "FROM YourQuery " & _
   "WHERE YourCondition")
with rst
   if not .eof then
[COLOR="Green"]      'get data from fields . . .[/COLOR]
      Debug.Print "Field1: " & .Fields("Field1")
      Debug.Print "Field2: " & !Field2
   else
      Debug.Print "no records"
   end if
   .close
end with

. . . this is an exceedingly common process in code to get at data defined in a SQL statement. Even more common is a loop inside the With...End With block to retrieve data from multiple records in the recordset, corresponding to multiple rows in the table.
Cheers,

 

[namliam]

Are you not trying to do something as simple as:

Select [tble-wcdelays].*, SumOfHoursDelay
from [Tble-wcDelays]
Left Join (
           SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
           FROM [Tble-wcDelays]
           GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
           HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID])) ) as Totals
 on [Tble-wcDelays].Causedby = Totals.Causedby

 

[Jon123]

that is exactly what im trying to do but cant get the code right.

 

[namliam]

The code / query I made should be a proper query, I think..

 

[Jon123]

The query runs fine but how do I get the data out of the query and into the fields on the form. The total query is going to add all the hours for 2 companys for a certian record. There will alway only be the same 2 companys but the total hrs pre record may differ. So on my form I have [cust1hrs] and [cust2hrs]
when the qry runs how do I get those total values into those fields?

jon

 

[namliam]

make a subform in continues view and show them below eachother....

Or if you always will have 2 exact companies you can try working with a crosstab query and link that to the subform....

 

[MarkK]

Or, if it's just a couple of summaries from a table or query, open a recordset during the main Form's Current event, and assign the values to a couple of unbound textboxes.

 

[Jon123]

Ok so If I use this code

Me.[Field1] = DLookup("[Sumofhoursdelay]", "QryName")

If this runs I get the hrs I'm looking for but I can't figure out the WHERE end of this code. So again when the query runs it returns only 2 lines Company1 plus the total hrs and Complany2 plus its hours. So I want the code to pull the Sumofhoursdelay for company 2

jim

 

[namliam]

Really using dlookup to get two values is a bad idea, performance wize. really consider using a form for this.
Or use a recordset something along the lines of

dim rs as dao.recordset
set rs = currentdb.openrecordset("Yourquery")
Me!Control1 = rs!Total
rs.movenext
Me!Control2 = rs!Total
rs.close
set rs = nothing