using vba to write SQL result to an unbound textbox

[Jaron]

Hi. I have an unbound textbox on a form, and a combobox on the same form. I would like for any change in the combobox value to run an SQL statement and put the results in the textbox. I don't actually have a query, rather I am hoping to just run the SQL from within the VBA. Is this possible?



Either way, I am getting Run-Timeerror 3061: Too few parameters. Expected 2.


The SQL does work fine on it's own.

Here is the code I have in the on change event of the combo:

Private Sub Combo27_Change()

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
    strSQL = "SELECT Sum([Amount]) AS TotalPaidtoDate FROM CreditorPayments;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Me.Text33.Value = rs!TotalPaidtoDate
End Sub

The line that catches is:

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Any ideas?

Thanks.

[pbaldy]

Is CreditorPayments a query with 2 parameters by chance? You might try a DSum() if so.

[Missinglinq]

You cannot set the Control Source of a Textbox to a SQL statement; you need to save your SQL statement as a Query and then, as Paul suggested, use the appropriate Function against the Query.

Linq ;0)>

[pbaldy]

You cannot set the Control Source of a Textbox to a SQL statement

While true, not sure why you've jumped in to point it out. They didn't try to.

[Jaron]

Yes, CreditorPayments does have 2 parameters. It says: Look for all the payment records that have the same VendorID and CreditorAccountID as I've entered in two combo boxes in my form. The CreditorAccountID combo is supposed to have the on change event that says: When changed, run some SQL that pulls the sum of all entries from that query and then stick the result in this textbox to show me how much I've paid towards this account to date.

The query and the table that the form is based on are unrelated, so I've had no success trying to create a separate query that binds them and then basing my form on that so that my textbox can be bound instead of unbound. So the only solution I could think of is to try to force it with VBA. I haven't ever used DSum before, I'll play around with that.

[Jaron]

Ok, DSum was very easy to use. I got the results I want with the following:

Dim SumPaid As Currency
SumPaid = DSum("[Amount]", "CreditorPayments")
Me.Text33.Value = SumPaid
End Sub

Thank you for that suggestion. I'm new as you can tell. Now I just have to address the 'invalid use of null' when I choose something in the combo that doesn't return results....probably an 'if' bit in the code. Thanks for the help.

[pbaldy]

Happy to help. One alternative:

SumPaid = Nz(DSum("[Amount]", "CreditorPayments"), 0)

[Missinglinq]

...not sure why you've jumped in to point it out. They didn't try to...

Let's see the thread title was

"using vba to write SQL result to an unbound textbox"

and then the OP wrote

"run an SQL statement and put the results in the textbox"

[pbaldy]

Both can be interpreted differently, and the code clearly wasn't doing that.

[Jaron]

Nice. The Nz worked well. I forgot I'd used that before in a query expression awhile back, but I didn't think of it this time.