Assigning result of query to text box

[red6000]

Hi, I have the following SQL:

SELECT Sum(TimeSpent) AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)=Forms!InputForm2!MainTimesheet.Form!WorkDate)
And ((Activities.CSTRName)=Forms!InputForm2!MainTimesheet.Form!EmployeeID));

As you can see, it returns a single column, single row answer.

Waht I would like todo is attach this result to a text box on a form.

I assume I need to do this thru VBA, but how? What is teh VBA equivalent of
the SQL?

Thanks.

 

[Rick Brandt]

Hi, I have the following SQL:

SELECT Sum(TimeSpent) AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)=Forms!InputForm2!MainTimesheet.Form!WorkDate)
And ((Activities.CSTRName)=Forms!InputForm2!MainTimesheet.Form!EmployeeID));

As you can see, it returns a single column, single row answer.

Waht I would like todo is attach this result to a text box on a form.

I assume I need to do this thru VBA, but how? What is teh VBA equivalent of
the SQL

No code required. Save your SQL in a query and then in your TextBox
ControlSource enter...

=DLookup("TotalTime","NameOfQuery")

If you prefer using VBA...

Dim db as Database
Dim rs as Recordset

Set db = CurrentDB
Set rs = db.OpenRecordset("Your SQL", dbOpenSnapshot)
Forms!YourFormName!TextBoxName = rs!TotalTime

 

[red6000]

I'm still trying to get this oen to work. I have now created the VBA code
below which 'I believe' is equivalent to my SQL, but I get RunTime error
2001 You have cancelled the previous operation' any ideas?

Private Sub Command22_Click()
Dim totX As Integer
Dim wd As String
Dim eID As String
wd = WorkDate.Value
eID = EmployeeID.Value
totX = DSum("[TimeSpent]", "Activities", _
"[CSTRName] = eID AND [WorkDate] = wd")

MsgBox (totX)

End Sub

 

[tina]

open a recordset, using either the name of the query or the SQL string, and
assign the value of TotalTime to the textbox control on the form, as

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("QueryName", dbOpenDynaset)

Me!ControlName = rst("TotalTime")

rst.Close
Set rst = Nothing

substitute the correct name of the query for "QueryName", of course.

if you want to run the SQL statement instead of a query object, change the
first two lines above to

Dim rst As DAO.Recordset, str As String

str = "SELECT Sum(TimeSpent) AS TotalTime " _
& "FROM Activities WHERE WorkDate=#" _
& Me!MainTimesheet.Form!WorkDate _
& "# And CSTRName=" _
& Me!MainTimesheet.Form!EmployeeID

Set rst = CurrentDb.OpenRecordset(str, dbOpenDynaset)

the above SQL string assumes that the code is running in form InputForm2,
that WorkDate is actually a date value, and that CSTRName is actually a
Number data type. if CSTRName is a Text data type, change the syntax to

& "# And CSTRName='" _
& Me!MainTimesheet.Form!EmployeeID & "'"

hth

 

[Rick Brandt]

I'm still trying to get this oen to work. I have now created the VBA code
below which 'I believe' is equivalent to my SQL, but I get RunTime error 2001
You have cancelled the previous operation' any ideas?

Private Sub Command22_Click()
Dim totX As Integer
Dim wd As String
Dim eID As String
wd = WorkDate.Value
eID = EmployeeID.Value
totX = DSum("[TimeSpent]", "Activities", _
"[CSTRName] = eID AND [WorkDate] = wd")

MsgBox (totX)

Your variables need to be outside the quotes and quote delimited.

totX = DSum("[TimeSpent]", "Activities", "[CSTRName] = '" & eID & "' AND
[WorkDate] = '" & wd & "'")