Form Record Source Variable

[Guest]

I have a form that needs to have a Variable for a Record Source.

I have 4 temporary tables that are set up based on the TeamName a person is
in at log in. Based on who is logged in TEMPTbl1 should appear in the Form.
If someone else is logged in it should TEMPTbl2 shold be use in the Form.
What is the Syntax for a vaiable in the Record Source of a form.

 

[Guest]

You may or may not need to use a variable. What you want to do set set the
form's record source property based on who is logged in. It would be
something like this:

If LoginUser = "Elmer Fudd" Then
Me.Recordsource = "TEMPTbl1"
Else
Me.Recordsource = "TEMPTbl2"
End If

 

[Perry]

Variable for a recordsource (?)
Can't you do something like below lines of code?

Select Case environ("UserName")
case "first user"
MyForm.Form.Recordsource = "TEMPTbl1"
case "second user"
MyForm.Form.Recordsource = "TEMPTbl2"
case "third user"
MyForm.Form.Recordsource = "TEMPTbl3"
'....etc
End Select

Kindly repost, if this is not what y're after

Krgrds,
Perry

 

[Guest]

Why not a variable for a recordsource (?)

strSQL1 = "SELECT SomeField, AnotherField, AnyOldField FROM Table1"
strSQL2 = "SELECT SomeField, AnotherField, AnyOldField FROM Table2"

If Me.LoginUser = "Miss Molly" Then
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If

Not that I would do it that way, but demonstarting a variable can be used as
a recordsource

 

[Perry]

Dave, that question was directed to the OP )
Look at the thread sequence ...
;-)

Perry

 

[Perry]

Why not a variable for a recordsource (?)

Forgot to answer:
Yeah, why not ...

Krgrds,
Perry

 

[Guest]

Your point is?

 

[Guest]

Okay.
In reality, I would not use the technique the OP is using.
I would have one query as the record source and filter it based on who is
logged in in the Load event of the form.

 

[Guest]

To the Original Poster (OP)

Last message is the way to go.
Don't change the recordsource if the only thing that changes is a where
clause on one or more fields.

Like Dave indicated, would also be my solution, use the filter settings to
forms (or reports).

But, so as to answer yr initial question: yes, recordsources can be
paramatrised using (for instance) the examples as made available in this
thread...

 

[Guest]

Ok I got the process now I think however I still did not see any method of
placing a variable in the Record Source of a form so it would be generic
enough to pull the right table based on who was logged in.

I did see other methods of CHANGING the Recordset with code that I can use I
just wondered if there were actually a way of going into properties and
placing a variable name there and then setting that variable based on what
table was in the variable.

THANKS FOR THE OTHER GOOD INFORMATION have not had this issue come up
before. The reason I wanted it was because there are many Random Files being
retrieved in order to get specific numbers and then based on those numbers
only the top 10 results are kept these are put in several temp tables so a
second set of randomizations can take place. The results are placed in the
form based on who is logged in so I thought it would save code but I guess I
have to at least write enought to set the RecordSource ahead of opening the
form.

THANKS AGAIN......GREAT SITE.

Dwight