query in textbox

[Brennan]

I have a form with multiple queries that I need to drop into textboxes. Some
of the queries are built on top of other queries. In the past, I have used
VBA with SQL to accomplish this, but it doesn't seem to be working. Here is
an example of a line of code that I am trying to use. Thanks for your help.
I am using access 2003.

Private Sub presaleupdate()
Dim str As String
Dim rs As Recordset
Dim combo1 As String


combo1 = Me.AccountingPeriod


str = "SELECT Sum([Lent - Postsale].Lentcredit) AS SumOfLentcredit FROM
[Lent - Postsale]GROUP BY [Lent - Postsale].[Cost Summary Period] HAVING
((([Lent - Postsale].[Cost Summary Period])='" & combo1 & "'));"

Set rs = CurrentDb.OpenRecordset(str)

Me.postsalelent1 = ????



End Sub

 

[Klatuu]

Anytime you think "drop a query in a text box", instead, think Domain
Aggregate function. In this case it would be:

Me.postsalelent1 = DSum("[Lentcredit]", "[Lent - Postsale]", "[Cost
Summary Period] = """ & Me.combo1 & """")

 

[Sietske]

Thanks for the hint!

I was struggling with select queries, "openrecordset" etc in an attempt to
get things working, but now I use the domain function DLookup and it works
just fine.

Anytime you think "drop a query in a text box", instead, think Domain
Aggregate function. In this case it would be:

Me.postsalelent1 = DSum("[Lentcredit]", "[Lent - Postsale]", "[Cost
Summary Period] = """ & Me.combo1 & """")

--
Dave Hargis, Microsoft Access MVP

I have a form with multiple queries that I need to drop into textboxes. Some
of the queries are built on top of other queries. In the past, I have used
VBA with SQL to accomplish this, but it doesn't seem to be working. Here is
an example of a line of code that I am trying to use. Thanks for your help.
I am using access 2003.

Private Sub presaleupdate()
Dim str As String
Dim rs As Recordset
Dim combo1 As String


combo1 = Me.AccountingPeriod


str = "SELECT Sum([Lent - Postsale].Lentcredit) AS SumOfLentcredit FROM
[Lent - Postsale]GROUP BY [Lent - Postsale].[Cost Summary Period] HAVING
((([Lent - Postsale].[Cost Summary Period])='" & combo1 & "'));"

Set rs = CurrentDb.OpenRecordset(str)

Me.postsalelent1 = ????



End Sub