CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Carl
Carl

Reputation: 5779

Set Text Box equal to Query

I have a query in an Access Db that counts the number of observations that meet a certain criteria as defined by two comboboxes. The query works fine, and I can get the result of the query to display and update live with the comboboxes using subforms, but the subforms are an extremely ugly way to represent a single number.

I would like to use a textbox to display that single number from the query instead of a subreport. I've tried inputting the SQL of the query into the Control Source and the Default Value, but no success.

After reading through the forum it appears I can write a function and then set my control source equal to that function.

Here is my VBA:

Public Function AdultCount()
Dim rs As Recordset
Set rs=CurrentDb.OpenRecordset("Select Count([name]) as [# Vars] from Masterdb where ((masterdb.year=[forms]![Masterform]![NavigationSubform].[form]![year]) and (masterdb.recipient=[forms]![Masterform]![NavigationSubform].[form]![recipient]) and (masterdb.group="adult"))")

AdultVar=rs!Result

rs.Close

Set rs=Nothing

End Function

I get "compile error: Expected:list separator or )" and it highlights "adult" at the end of my SQL query.

I don't know why this isn't working. Does anyone know why I'm getting this error, and tell me if I'm even doing the right thing to get what I want?

edit:

I now have 

Dim strSQL AS String

strSQL = "Select Count([name]) as [# Vars] from Masterdb where [year] = " & Me.NavigationSubform.year.value & " and [recipient] = " & Me.NavigationSubform.recipient.value & " and [group]='adult'"

Set rs=CurrentDb.OpenRecordset(strSQL)

AdultVar=rs![# Vars].Value

rs.Close

Set rs=Nothing

End Function

But the textbox reads "#Name?"

Any ideas?

Upvotes: 1

Views: 1347

Answers (2)

Nicholai
Nicholai

Reputation: 818

A second option is to build your string first, like so:

Dim strSQL AS String

strSQL = "Select Count([name]) as [# Vars] from Masterdb where [year] = " & Me.NavigationSubform.year.value & " and [recipient] = " & Me.NavigationSubform.recipient.value & " and [group]='adult'"

Set rs=CurrentDb.OpenRecordset(strSQL)

If you're not familiar with using the Me keyword, do a bit of reading up as it will shorten your code and save you time.

Upvotes: 1

Nicholai
Nicholai

Reputation: 818

The double quotes for "adult" are throwing the error because you're closing and reopening a string without telling VB what to do with adult"))"). SQL needs single quotes anyway, so change it to 'adult' and the error will go away.

Upvotes: 2

Related Questions