query recordset expression field

[Guest]

Hello all, i have the function below trying to open a recordset based on a
query.
The problem i am having is setting userName = (rs![Expr3])

Expr 3 is an expression field in the query. I am getting a
"Run-time error '3265'; Item not found in this collection."

When i look in the locals window of the program, i see that the recordset
does not contain the Expr3 or any other expression field. How can i get it so
that the expression field will be accessible to the VBA program?

Thanks!

Function Main()
Const strqryName = "servicemom_alerts"
Dim userAlias As String
Dim userName As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strqryName)
userName = (rs![Expr3])
Call sbSendMessage(userAlias, userName)
fullname = userAlias
Main = fullname
End Function

 

[Marshall Barton]

Hello all, i have the function below trying to open a recordset based on a
query.
The problem i am having is setting userName = (rs![Expr3])

Expr 3 is an expression field in the query. I am getting a
"Run-time error '3265'; Item not found in this collection."

When i look in the locals window of the program, i see that the recordset
does not contain the Expr3 or any other expression field. How can i get it so
that the expression field will be accessible to the VBA program?

Function Main()
Const strqryName = "servicemom_alerts"
Dim userAlias As String
Dim userName As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strqryName)
userName = (rs![Expr3])
Call sbSendMessage(userAlias, userName)
fullname = userAlias
Main = fullname
End Function

It sure sounds like the query with the calculated fields is
not named servicemom_alerts. At least, that's the only
reason I can think of.

 

[Guest]

The problem has to be in your query servicemom_alerts. Expressions in
queries are to calculate a value, change the names of fields to avoid
conflicts, or concatenate fields. Let's say you are trying to combine two
fields in your table into one. I am guessing you want First Name and Last
Name to be concatenated into one name. So in the Field row:
Exp3: [MyTableNameHere]![FirstName] & " " & [MyTableNameHere]![LasttName]
or another example. Let's say your username is made up of the first 3
letters of the last name and first 2 letters of the First Name:
Exp3: Left([MyTableNameHere]![LastName], 3) &
Left([MyTableNameHere]![FirsttName], 3)

 

[Guest]

Marshall,

thanks for your help, you were correct.. i put in the table name by mistake.
it lets me set using expr3 now.

One more question. i'm not sure if it belongs here or in reports.
I want the program to run for every name that the query returns.
How would i insert it into the report?

As i have it now, if i put in a textbox and set the recordsource to program,
it only returns the first value. And if i put a loop in the program, it just
cycles through and returns the last one.

thanks in advance.

Hello all, i have the function below trying to open a recordset based on a
query.
The problem i am having is setting userName = (rs![Expr3])

Expr 3 is an expression field in the query. I am getting a
"Run-time error '3265'; Item not found in this collection."

When i look in the locals window of the program, i see that the recordset
does not contain the Expr3 or any other expression field. How can i get it so
that the expression field will be accessible to the VBA program?

Function Main()
Const strqryName = "servicemom_alerts"
Dim userAlias As String
Dim userName As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strqryName)
userName = (rs![Expr3])
Call sbSendMessage(userAlias, userName)
fullname = userAlias
Main = fullname
End Function

It sure sounds like the query with the calculated fields is
not named servicemom_alerts. At least, that's the only
reason I can think of.

 

[Marshall Barton]

Report, what report?

You may(?) be able to do what you want by looping through
the recordset:

Set rs = db.OpenRecordset(strqryName)
Do Until rs.EOF
userName = (rs![Expr3])
Call sbSendMessage(userAlias, userName)
' fullname = userAlias
' Main = fullname
rs.MoveNext
Loop

But that doesn't seem to gibe with the fact that this code
is in a function that apparently returns a name. I have no
idea what the sbSendMessage procedure does, but I think you
should reexamine the logic over a larger scope than this one
procedure.

If you want to print a report for each user, then I don't
think you want to use the user name. You probably need to
use the primary key field in the table, which will also have
to be in the query. The line:
DoCmd.OpenReport "reportname", , , "key =" & rs!key
will have to be inside the loop.
--
Marsh
MVP [MS Access]

thanks for your help, you were correct.. i put in the table name by mistake.
it lets me set using expr3 now.

One more question. i'm not sure if it belongs here or in reports.
I want the program to run for every name that the query returns.
How would i insert it into the report?

As i have it now, if i put in a textbox and set the recordsource to program,
it only returns the first value. And if i put a loop in the program, it just
cycles through and returns the last one.

Hello all, i have the function below trying to open a recordset based on a
query.
The problem i am having is setting userName = (rs![Expr3])

Expr 3 is an expression field in the query. I am getting a
"Run-time error '3265'; Item not found in this collection."

When i look in the locals window of the program, i see that the recordset
does not contain the Expr3 or any other expression field. How can i get it so
that the expression field will be accessible to the VBA program?

Function Main()
Const strqryName = "servicemom_alerts"
Dim userAlias As String
Dim userName As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(strqryName)
userName = (rs![Expr3])
Call sbSendMessage(userAlias, userName)
fullname = userAlias
Main = fullname
End Function

It sure sounds like the query with the calculated fields is
not named servicemom_alerts.