can a login filter records so users can share forms

J

John_Lehmus

Hello,

With access running on a network, I want each user to see only their records
after their login. I have tried using the "one form for each user approach",
but this is for a large group of users and the form may change every so
often.

Is there a safe/secure way to have the [UserID] as a parent/child link to a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
 
D

Damon Heron

Using just a main form, you can put the recordsource in the form's load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you could also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the same userid
as the current user. Of course, your table would have to have a field for
UserID for this to work.


HTH
Damon
 
J

John_Lehmus

Hi Damon,

Thanks for your reply. Using sql as a record source is brand new for me.

Will this code allow multiple users to share the same form at the same time?

The sql code becomes the records source? What do the CWO letters refer to?

Regards,
John

--
John_Lehmus
Maine, USA


Damon Heron said:
Using just a main form, you can put the recordsource in the form's load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you could also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the same userid
as the current user. Of course, your table would have to have a field for
UserID for this to work.


HTH
Damon

John_Lehmus said:
Hello,

With access running on a network, I want each user to see only their
records
after their login. I have tried using the "one form for each user
approach",
but this is for a large group of users and the form may change every so
often.

Is there a safe/secure way to have the [UserID] as a parent/child link to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
 
D

Damon Heron

Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and add the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"
Click to expand...
Click to expand...

As to your question about multiple users, I will have to defer to others who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be addressed
when more than one user is allowed access to a form at the same time.

Damon


John_Lehmus said:
Hi Damon,

Thanks for your reply. Using sql as a record source is brand new for me.

Will this code allow multiple users to share the same form at the same
time?

The sql code becomes the records source? What do the CWO letters refer to?

Regards,
John

--
John_Lehmus
Maine, USA


Damon Heron said:
Using just a main form, you can put the recordsource in the form's load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the same
userid
as the current user. Of course, your table would have to have a field
for
UserID for this to work.


HTH
Damon

John_Lehmus said:
Hello,

With access running on a network, I want each user to see only their
records
after their login. I have tried using the "one form for each user
approach",
but this is for a large group of users and the form may change every so
often.

Is there a safe/secure way to have the [UserID] as a parent/child link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
J

John_Lehmus

Hi Damon,

I have replaced the info. and I think I'm getting close. However, right
after the login screen closes my data form opens and pauses at a break-point
I entered. When I check the value for curUser it displays the word "Empty".

So It seems that the value of curUser, which does work from the login form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the value will
be remembered?

Cordially,
John_Lehmus
Maine, USA


Damon Heron said:
Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and add the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"
Click to expand...
Click to expand...

As to your question about multiple users, I will have to defer to others who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be addressed
when more than one user is allowed access to a form at the same time.

Damon


John_Lehmus said:
Hi Damon,

Thanks for your reply. Using sql as a record source is brand new for me.

Will this code allow multiple users to share the same form at the same
time?

The sql code becomes the records source? What do the CWO letters refer to?

Regards,
John

--
John_Lehmus
Maine, USA


Damon Heron said:
Using just a main form, you can put the recordsource in the form's load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the same
userid
as the current user. Of course, your table would have to have a field
for
UserID for this to work.


HTH
Damon

Hello,

With access running on a network, I want each user to see only their
records
after their login. I have tried using the "one form for each user
approach",
but this is for a large group of users and the form may change every so
often.

Is there a safe/secure way to have the [UserID] as a parent/child link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
D

Damon Heron

In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for use in
all routines.
If you already have a dim statement for curuser on one of your forms, remove
it.
Damon

John_Lehmus said:
Hi Damon,

I have replaced the info. and I think I'm getting close. However, right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the word
"Empty".

So It seems that the value of curUser, which does work from the login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


Damon Heron said:
Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"
Click to expand...

As to your question about multiple users, I will have to defer to others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be addressed
when more than one user is allowed access to a form at the same time.

Damon


John_Lehmus said:
Hi Damon,

Thanks for your reply. Using sql as a record source is brand new for
me.

Will this code allow multiple users to share the same form at the same
time?

The sql code becomes the records source? What do the CWO letters refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the same
userid
as the current user. Of course, your table would have to have a field
for
UserID for this to work.


HTH
Damon

Hello,

With access running on a network, I want each user to see only their
records
after their login. I have tried using the "one form for each user
approach",
but this is for a large group of users and the form may change every
so
often.

Is there a safe/secure way to have the [UserID] as a parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
J

John_Lehmus

Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks for
"curUser" expecting me to type something in. CurUser is remembered because I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE (((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

Damon Heron said:
In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for use in
all routines.
If you already have a dim statement for curuser on one of your forms, remove
it.
Damon

John_Lehmus said:
Hi Damon,

I have replaced the info. and I think I'm getting close. However, right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the word
"Empty".

So It seems that the value of curUser, which does work from the login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


Damon Heron said:
Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"

As to your question about multiple users, I will have to defer to others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be addressed
when more than one user is allowed access to a form at the same time.

Damon


Hi Damon,

Thanks for your reply. Using sql as a record source is brand new for
me.

Will this code allow multiple users to share the same form at the same
time?

The sql code becomes the records source? What do the CWO letters refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the same
userid
as the current user. Of course, your table would have to have a field
for
UserID for this to work.


HTH
Damon

Hello,

With access running on a network, I want each user to see only their
records
after their login. I have tried using the "one form for each user
approach",
but this is for a large group of users and the form may change every
so
often.

Is there a safe/secure way to have the [UserID] as a parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
D

Damon Heron

Are you assigning a value to curUser in the logon form? Is it numeric? Is
the field UserId in your table numeric?
put a debug.print right after the strsql statement and see if it reads
correctly.

It works on my end, so not sure where the problem is, usually when you get
a parameter box, it doesn't recognize something in your string statement.

Damon

John_Lehmus said:
Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks for
"curUser" expecting me to type something in. CurUser is remembered because
I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE
(((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

Damon Heron said:
In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for use in
all routines.
If you already have a dim statement for curuser on one of your forms,
remove
it.
Damon

John_Lehmus said:
Hi Damon,

I have replaced the info. and I think I'm getting close. However, right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the word
"Empty".

So It seems that the value of curUser, which does work from the login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


:

Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and
add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"

As to your question about multiple users, I will have to defer to
others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be
addressed
when more than one user is allowed access to a form at the same time.

Damon


Hi Damon,

Thanks for your reply. Using sql as a record source is brand new for
me.

Will this code allow multiple users to share the same form at the
same
time?

The sql code becomes the records source? What do the CWO letters
refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the
same
userid
as the current user. Of course, your table would have to have a
field
for
UserID for this to work.


HTH
Damon

message
Hello,

With access running on a network, I want each user to see only
their
records
after their login. I have tried using the "one form for each user
approach",
but this is for a large group of users and the form may change
every
so
often.

Is there a safe/secure way to have the [UserID] as a parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
J

John_Lehmus

Hi Damon,

I tried setting both of the field types for curUser in the employee table
and curUser in the record set to Number / Long Integer. I also reset the
public statement to "Public CurUser As Long". But, when the called form opens
and I check the "On Open" event, the value for variable "curUser" it still
displays the word Empty.

I'm wondering if maybe the issue here is that I'm using a TabControl with 3
tabbed subforms, but shouldn't setting the curUser as public cover these
options too?

Maybe there is a way to "merge" the login to the top of form with the
TabControl/subforms are located. Thoughts??

Best Regards,
John

~

Damon Heron said:
Are you assigning a value to curUser in the logon form? Is it numeric? Is
the field UserId in your table numeric?
put a debug.print right after the strsql statement and see if it reads
correctly.

It works on my end, so not sure where the problem is, usually when you get
a parameter box, it doesn't recognize something in your string statement.

Damon

John_Lehmus said:
Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks for
"curUser" expecting me to type something in. CurUser is remembered because
I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE
(((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

Damon Heron said:
In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for use in
all routines.
If you already have a dim statement for curuser on one of your forms,
remove
it.
Damon

Hi Damon,

I have replaced the info. and I think I'm getting close. However, right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the word
"Empty".

So It seems that the value of curUser, which does work from the login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


:

Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and
add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"

As to your question about multiple users, I will have to defer to
others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be
addressed
when more than one user is allowed access to a form at the same time.

Damon


Hi Damon,

Thanks for your reply. Using sql as a record source is brand new for
me.

Will this code allow multiple users to share the same form at the
same
time?

The sql code becomes the records source? What do the CWO letters
refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. - you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " & _
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the
same
userid
as the current user. Of course, your table would have to have a
field
for
UserID for this to work.


HTH
Damon

message
Hello,

With access running on a network, I want each user to see only
their
records
after their login. I have tried using the "one form for each user
approach",
but this is for a large group of users and the form may change
every
so
often.

Is there a safe/secure way to have the [UserID] as a parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
D

Damon Heron

Lemme see.
If the curUser is null then you are not assigning a value to it. On the
logon form is there something that sets the curUser to the value of the
userID? Have you named the UserID field in your table to CurUser? That's a
no-no. Change it back to UserID. The curUser variable is just a container
for the user's id that is established at logon time.

Damon

John_Lehmus said:
Hi Damon,

I tried setting both of the field types for curUser in the employee table
and curUser in the record set to Number / Long Integer. I also reset the
public statement to "Public CurUser As Long". But, when the called form
opens
and I check the "On Open" event, the value for variable "curUser" it still
displays the word Empty.

I'm wondering if maybe the issue here is that I'm using a TabControl with
3
tabbed subforms, but shouldn't setting the curUser as public cover these
options too?

Maybe there is a way to "merge" the login to the top of form with the
TabControl/subforms are located. Thoughts??

Best Regards,
John

~

Damon Heron said:
Are you assigning a value to curUser in the logon form? Is it numeric?
Is
the field UserId in your table numeric?
put a debug.print right after the strsql statement and see if it reads
correctly.

It works on my end, so not sure where the problem is, usually when you
get
a parameter box, it doesn't recognize something in your string statement.

Damon

John_Lehmus said:
Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks for
"curUser" expecting me to type something in. CurUser is remembered
because
I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE
(((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

:

In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for use
in
all routines.
If you already have a dim statement for curuser on one of your forms,
remove
it.
Damon

Hi Damon,

I have replaced the info. and I think I'm getting close. However,
right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the word
"Empty".

So It seems that the value of curUser, which does work from the
login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the
value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


:

Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and
add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the
form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"

As to your question about multiple users, I will have to defer to
others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be
addressed
when more than one user is allowed access to a form at the same
time.

Damon


message
Hi Damon,

Thanks for your reply. Using sql as a record source is brand new
for
me.

Will this code allow multiple users to share the same form at the
same
time?

The sql code becomes the records source? What do the CWO letters
refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the
form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. -
you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " &
_
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the
same
userid
as the current user. Of course, your table would have to have a
field
for
UserID for this to work.


HTH
Damon

message
Hello,

With access running on a network, I want each user to see only
their
records
after their login. I have tried using the "one form for each
user
approach",
but this is for a large group of users and the form may change
every
so
often.

Is there a safe/secure way to have the [UserID] as a
parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
J

John_Lehmus

Hi Damon,

Guess what? It's working great and I thank you a thousand times.

I did have the field named curUser, plus I had started with a blank mdb and
copied only the needed objects. I also started with a basic login form and
resulting record form.

Once I changed back to userID,it started working. I just kept updating the
forms until I got back to what I needed in the first place and it still
works! I keep testing it to be sure I didn't dream it.

One quick question though, does each form need it's own strsql variable name
to set the record source for that form or would have the original one worked
for all of the tabbed forms? Obviously, I made new one for each form, but I
wondered for next time.

Kind Regards,
John Lehmus
Maine, USA


Damon Heron said:
Lemme see.
If the curUser is null then you are not assigning a value to it. On the
logon form is there something that sets the curUser to the value of the
userID? Have you named the UserID field in your table to CurUser? That's a
no-no. Change it back to UserID. The curUser variable is just a container
for the user's id that is established at logon time.

Damon

John_Lehmus said:
Hi Damon,

I tried setting both of the field types for curUser in the employee table
and curUser in the record set to Number / Long Integer. I also reset the
public statement to "Public CurUser As Long". But, when the called form
opens
and I check the "On Open" event, the value for variable "curUser" it still
displays the word Empty.

I'm wondering if maybe the issue here is that I'm using a TabControl with
3
tabbed subforms, but shouldn't setting the curUser as public cover these
options too?

Maybe there is a way to "merge" the login to the top of form with the
TabControl/subforms are located. Thoughts??

Best Regards,
John

~

Damon Heron said:
Are you assigning a value to curUser in the logon form? Is it numeric?
Is
the field UserId in your table numeric?
put a debug.print right after the strsql statement and see if it reads
correctly.

It works on my end, so not sure where the problem is, usually when you
get
a parameter box, it doesn't recognize something in your string statement.

Damon

Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks for
"curUser" expecting me to type something in. CurUser is remembered
because
I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE
(((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

:

In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for use
in
all routines.
If you already have a dim statement for curuser on one of your forms,
remove
it.
Damon

Hi Damon,

I have replaced the info. and I think I'm getting close. However,
right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the word
"Empty".

So It seems that the value of curUser, which does work from the
login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the
value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


:

Oops! sorry, the CWO reference was an example. You will have to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event and
add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the
form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] & "))"

As to your question about multiple users, I will have to defer to
others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be
addressed
when more than one user is allowed access to a form at the same
time.

Damon


message
Hi Damon,

Thanks for your reply. Using sql as a record source is brand new
for
me.

Will this code allow multiple users to share the same form at the
same
time?

The sql code becomes the records source? What do the CWO letters
refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the
form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. -
you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID " &
_
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have the
same
userid
as the current user. Of course, your table would have to have a
field
for
UserID for this to work.


HTH
Damon

message
Hello,

With access running on a network, I want each user to see only
their
records
after their login. I have tried using the "one form for each
user
approach",
but this is for a large group of users and the form may change
every
so
often.

Is there a safe/secure way to have the [UserID] as a
parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
D

Damon Heron

John,
Your question:
One quick question though, does each form need it's own strsql variable
name
to set the record source for that form or would have the original one
worked
for all of the tabbed forms? Obviously, I made new one for each form, but
I
wondered for next time.
Click to expand...
Answer: I don't have a clue... It depends on what is on the tabbed forms.
Are you using those for different recordsets, or ???
this is the first time you have mentioned tabbed forms. Are they for
different users? If so, you should disable the tabs that don't correspond
to the user that is logged on. that way one user cannot get access to
another user's records.

Damon

John_Lehmus said:
Hi Damon,

Guess what? It's working great and I thank you a thousand times.

I did have the field named curUser, plus I had started with a blank mdb
and
copied only the needed objects. I also started with a basic login form and
resulting record form.

Once I changed back to userID,it started working. I just kept updating the
forms until I got back to what I needed in the first place and it still
works! I keep testing it to be sure I didn't dream it.

One quick question though, does each form need it's own strsql variable
name
to set the record source for that form or would have the original one
worked
for all of the tabbed forms? Obviously, I made new one for each form, but
I
wondered for next time.

Kind Regards,
John Lehmus
Maine, USA


Damon Heron said:
Lemme see.
If the curUser is null then you are not assigning a value to it. On the
logon form is there something that sets the curUser to the value of the
userID? Have you named the UserID field in your table to CurUser?
That's a
no-no. Change it back to UserID. The curUser variable is just a
container
for the user's id that is established at logon time.

Damon

John_Lehmus said:
Hi Damon,

I tried setting both of the field types for curUser in the employee
table
and curUser in the record set to Number / Long Integer. I also reset
the
public statement to "Public CurUser As Long". But, when the called form
opens
and I check the "On Open" event, the value for variable "curUser" it
still
displays the word Empty.

I'm wondering if maybe the issue here is that I'm using a TabControl
with
3
tabbed subforms, but shouldn't setting the curUser as public cover
these
options too?

Maybe there is a way to "merge" the login to the top of form with the
TabControl/subforms are located. Thoughts??

Best Regards,
John

~

:

Are you assigning a value to curUser in the logon form? Is it numeric?
Is
the field UserId in your table numeric?
put a debug.print right after the strsql statement and see if it reads
correctly.

It works on my end, so not sure where the problem is, usually when
you
get
a parameter box, it doesn't recognize something in your string
statement.

Damon

Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks for
"curUser" expecting me to type something in. CurUser is remembered
because
I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE
(((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

:

In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for
use
in
all routines.
If you already have a dim statement for curuser on one of your
forms,
remove
it.
Damon

message
Hi Damon,

I have replaced the info. and I think I'm getting close. However,
right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the
word
"Empty".

So It seems that the value of curUser, which does work from the
login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the
value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


:

Oops! sorry, the CWO reference was an example. You will have
to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event
and
add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the
form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] &
"))"

As to your question about multiple users, I will have to defer
to
others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be
addressed
when more than one user is allowed access to a form at the same
time.

Damon


message
Hi Damon,

Thanks for your reply. Using sql as a record source is brand
new
for
me.

Will this code allow multiple users to share the same form at
the
same
time?

The sql code becomes the records source? What do the CWO
letters
refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the
form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. -
you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID
" &
_
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have
the
same
userid
as the current user. Of course, your table would have to
have a
field
for
UserID for this to work.


HTH
Damon

message
Hello,

With access running on a network, I want each user to see
only
their
records
after their login. I have tried using the "one form for
each
user
approach",
but this is for a large group of users and the form may
change
every
so
often.

Is there a safe/secure way to have the [UserID] as a
parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.

John_Lehmus
Maine, USA
Click to expand...
Click to expand...
Click to expand...
 
J

John_Lehmus

Good Morning Damon,

Thanks again.

Regarding the tabbed forms, they all relate to the person who logged in, but
they are from different queries, but they all have [userID] so I guess I'm
all set. However, if I do need different record-sets, I suppose the
additional variables can be assigned in much the same way at the user login.

Why is the number format preferred versus text for the userID?

Cordially,
John_Lehmus
Maine, USA


Damon Heron said:
John,
Your question:
One quick question though, does each form need it's own strsql variable
name
to set the record source for that form or would have the original one
worked
for all of the tabbed forms? Obviously, I made new one for each form, but
I
wondered for next time.
Click to expand...
Answer: I don't have a clue... It depends on what is on the tabbed forms.
Are you using those for different recordsets, or ???
this is the first time you have mentioned tabbed forms. Are they for
different users? If so, you should disable the tabs that don't correspond
to the user that is logged on. that way one user cannot get access to
another user's records.

Damon

John_Lehmus said:
Hi Damon,

Guess what? It's working great and I thank you a thousand times.

I did have the field named curUser, plus I had started with a blank mdb
and
copied only the needed objects. I also started with a basic login form and
resulting record form.

Once I changed back to userID,it started working. I just kept updating the
forms until I got back to what I needed in the first place and it still
works! I keep testing it to be sure I didn't dream it.

One quick question though, does each form need it's own strsql variable
name
to set the record source for that form or would have the original one
worked
for all of the tabbed forms? Obviously, I made new one for each form, but
I
wondered for next time.

Kind Regards,
John Lehmus
Maine, USA


Damon Heron said:
Lemme see.
If the curUser is null then you are not assigning a value to it. On the
logon form is there something that sets the curUser to the value of the
userID? Have you named the UserID field in your table to CurUser?
That's a
no-no. Change it back to UserID. The curUser variable is just a
container
for the user's id that is established at logon time.

Damon

Hi Damon,

I tried setting both of the field types for curUser in the employee
table
and curUser in the record set to Number / Long Integer. I also reset
the
public statement to "Public CurUser As Long". But, when the called form
opens
and I check the "On Open" event, the value for variable "curUser" it
still
displays the word Empty.

I'm wondering if maybe the issue here is that I'm using a TabControl
with
3
tabbed subforms, but shouldn't setting the curUser as public cover
these
options too?

Maybe there is a way to "merge" the login to the top of form with the
TabControl/subforms are located. Thoughts??

Best Regards,
John

~

:

Are you assigning a value to curUser in the logon form? Is it numeric?
Is
the field UserId in your table numeric?
put a debug.print right after the strsql statement and see if it reads
correctly.

It works on my end, so not sure where the problem is, usually when
you
get
a parameter box, it doesn't recognize something in your string
statement.

Damon

Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks for
"curUser" expecting me to type something in. CurUser is remembered
because
I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE
(((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

:

In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public for
use
in
all routines.
If you already have a dim statement for curuser on one of your
forms,
remove
it.
Damon

message
Hi Damon,

I have replaced the info. and I think I'm getting close. However,
right
after the login screen closes my data form opens and pauses at a
break-point
I entered. When I check the value for curUser it displays the
word
"Empty".

So It seems that the value of curUser, which does work from the
login
form,
is not getting passed on to the form that displays the records.

Is there a way to set the Dim statement or something else so the
value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


:

Oops! sorry, the CWO reference was an example. You will have
to
substitute your own table and field names to make the code work.
When the form is in design mode, click on the form's Load Event
and
add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on the
form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser] &
"))"

As to your question about multiple users, I will have to defer
to
others
who
have more knowledge of this. All of my dbs are in a single user
environment. There are a whole bunch of issues that have to be
addressed
when more than one user is allowed access to a form at the same
time.

Damon


message
Hi Damon,

Thanks for your reply. Using sql as a record source is brand
new
for
me.

Will this code allow multiple users to share the same form at
the
same
time?

The sql code becomes the records source? What do the CWO
letters
refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in the
form's
load
event, like this example:
(I used a global variable "curUser" that I assign at logon. -
you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate, tblCWO.UserID
" &
_
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that have
the
same
userid
as the current user. Of course, your table would have to
have a
field
for
UserID for this to work.


HTH
Damon

message
Hello,

With access running on a network, I want each user to see
only
their
records
after their login. I have tried using the "one form for
each
user
approach",
but this is for a large group of users and the form may
change
every
so
often.

Is there a safe/secure way to have the [UserID] as a
parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.
Click to expand...
Click to expand...
Click to expand...
 
D

Damon Heron

The userid can be generated by autonumber in the field. Plus, if you use
text, you may have 2 "John Smith" names and have to take an extra step to
differentiate them.

Damon

John_Lehmus said:
Good Morning Damon,

Thanks again.

Regarding the tabbed forms, they all relate to the person who logged in,
but
they are from different queries, but they all have [userID] so I guess I'm
all set. However, if I do need different record-sets, I suppose the
additional variables can be assigned in much the same way at the user
login.

Why is the number format preferred versus text for the userID?

Cordially,
John_Lehmus
Maine, USA


Damon Heron said:
John,
Your question:
One quick question though, does each form need it's own strsql variable
name
to set the record source for that form or would have the original one
worked
for all of the tabbed forms? Obviously, I made new one for each form,
but
I
wondered for next time.
Click to expand...
Answer: I don't have a clue... It depends on what is on the tabbed
forms.
Are you using those for different recordsets, or ???
this is the first time you have mentioned tabbed forms. Are they for
different users? If so, you should disable the tabs that don't
correspond
to the user that is logged on. that way one user cannot get access to
another user's records.

Damon

John_Lehmus said:
Hi Damon,

Guess what? It's working great and I thank you a thousand times.

I did have the field named curUser, plus I had started with a blank mdb
and
copied only the needed objects. I also started with a basic login form
and
resulting record form.

Once I changed back to userID,it started working. I just kept updating
the
forms until I got back to what I needed in the first place and it still
works! I keep testing it to be sure I didn't dream it.

One quick question though, does each form need it's own strsql variable
name
to set the record source for that form or would have the original one
worked
for all of the tabbed forms? Obviously, I made new one for each form,
but
I
wondered for next time.

Kind Regards,
John Lehmus
Maine, USA


:

Lemme see.
If the curUser is null then you are not assigning a value to it. On
the
logon form is there something that sets the curUser to the value of
the
userID? Have you named the UserID field in your table to CurUser?
That's a
no-no. Change it back to UserID. The curUser variable is just a
container
for the user's id that is established at logon time.

Damon

Hi Damon,

I tried setting both of the field types for curUser in the employee
table
and curUser in the record set to Number / Long Integer. I also reset
the
public statement to "Public CurUser As Long". But, when the called
form
opens
and I check the "On Open" event, the value for variable "curUser" it
still
displays the word Empty.

I'm wondering if maybe the issue here is that I'm using a TabControl
with
3
tabbed subforms, but shouldn't setting the curUser as public cover
these
options too?

Maybe there is a way to "merge" the login to the top of form with
the
TabControl/subforms are located. Thoughts??

Best Regards,
John

~

:

Are you assigning a value to curUser in the logon form? Is it
numeric?
Is
the field UserId in your table numeric?
put a debug.print right after the strsql statement and see if it
reads
correctly.

It works on my end, so not sure where the problem is, usually when
you
get
a parameter box, it doesn't recognize something in your string
statement.

Damon

message
Hi Damon,

Thanks for your time and efforts.

The value now is remembered, but a paramater box pops-up as asks
for
"curUser" expecting me to type something in. CurUser is
remembered
because
I
added a "watch" on both the login forma and the record-set form.

Here is the code set to the "On Load" event:

Sub Form_Load()
Dim strsql As String

strsql = "SELECT RPUserRev.* " & "FROM RPUserRev WHERE
(((RPUserRev.UserID)=
" & curUser & "))"

Me.RecordSource = strsql
Me.Requery

End Sub

Regards,
John_Lehmus
Maine, USA

~~~

:

In your global code module at the top, where it says
Option Compare Database
add
Public curUser As Long

This sets the variable (in this case a long integer) to public
for
use
in
all routines.
If you already have a dim statement for curuser on one of your
forms,
remove
it.
Damon

message
Hi Damon,

I have replaced the info. and I think I'm getting close.
However,
right
after the login screen closes my data form opens and pauses at
a
break-point
I entered. When I check the value for curUser it displays the
word
"Empty".

So It seems that the value of curUser, which does work from
the
login
form,
is not getting passed on to the form that displays the
records.

Is there a way to set the Dim statement or something else so
the
value
will
be remembered?

Cordially,
John_Lehmus
Maine, USA


:

Oops! sorry, the CWO reference was an example. You will
have
to
substitute your own table and field names to make the code
work.
When the form is in design mode, click on the form's Load
Event
and
add
the
VBA code with your own table info.
so the code would be
strsql = "SELECT yourtable.ID, etc (any fields you show on
the
form),
yourtable.UserID " & _
"FROM yourtable WHERE (((yourtable.UserID)= " & [curUser]
&
"))"

As to your question about multiple users, I will have to
defer
to
others
who
have more knowledge of this. All of my dbs are in a single
user
environment. There are a whole bunch of issues that have to
be
addressed
when more than one user is allowed access to a form at the
same
time.

Damon


message
Hi Damon,

Thanks for your reply. Using sql as a record source is
brand
new
for
me.

Will this code allow multiple users to share the same form
at
the
same
time?

The sql code becomes the records source? What do the CWO
letters
refer
to?

Regards,
John

--
John_Lehmus
Maine, USA


:

Using just a main form, you can put the recordsource in
the
form's
load
event, like this example:
(I used a global variable "curUser" that I assign at
logon. -
you
could
also
use CurrentUser if you are using Workgroups. see help)

Dim strsql As String
strsql = "SELECT tblCWO.CWOID, tblCWO.CWODate,
tblCWO.UserID
" &
_
"FROM tblCWO WHERE (((tblCWO.UserID)= " & [curUser] & "))"
Me.RecordSource = strsql
Me.Requery

This restricts the records displayed to just those that
have
the
same
userid
as the current user. Of course, your table would have to
have a
field
for
UserID for this to work.


HTH
Damon

in
message
Hello,

With access running on a network, I want each user to
see
only
their
records
after their login. I have tried using the "one form for
each
user
approach",
but this is for a large group of users and the form may
change
every
so
often.

Is there a safe/secure way to have the [UserID] as a
parent/child
link
to
a
sub-form or maybe a way of using "filters"?

Thanks, and I look forward to your thoughts and ideas.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter main form to records with no related records in subform,maintain updateable recordset 4
Subform linked to Main form via user login 2
User filtering 9
can a form display current table field names? 4
Perform Action Only on Filtered Records 1
login form 1
Record Level Filtering 4
Reconstruct Filter string on Subform 2

Top