Linking Master/Child fields with SubForm control but also need CurrentUser??

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

Hello,

I have a form and subform. Both these fields are linked by the ID in the
main form/table. The SubForm control properties are as follows:

LinkMasterFields = "lngID"
LinkChildFields = "lngMainID"

This works fine. The problem that I have is that I also need to filter
records that only belong to the CurrentUser (in the SubForm - kept in the
table). There is no need to keep the CurrentUser in the main form (in fact
it would be more hassle than it is worth) so I can't link the CurrentUser to
a field in the main table. Does anyone know a way round this? My last resort
would be to let users view all records filtered into the subform but they
are only allowed to update/modify the records that they created.

TIA,

Neil.
 
I assume one of the fields in the query that is the recordsource of the
subform contains the user name who created the record. If this is the case
then add a criteria to this query to only return those records matching the
currentuser.

In SQL if your field is called UserName then add

WHERE UserName = [CurrentUser]

HTH
Sam
 
Thanks Sam,

I am assuming you meant for me to put the below in the RecordSource property
of the form. If not, it doesn't matter because it works in there...

Neil.

Sam said:
I assume one of the fields in the query that is the recordsource of the
subform contains the user name who created the record. If this is the case
then add a criteria to this query to only return those records matching the
currentuser.

In SQL if your field is called UserName then add

WHERE UserName = [CurrentUser]

HTH
Sam

Neil said:
Hello,

I have a form and subform. Both these fields are linked by the ID in the
main form/table. The SubForm control properties are as follows:

LinkMasterFields = "lngID"
LinkChildFields = "lngMainID"

This works fine. The problem that I have is that I also need to filter
records that only belong to the CurrentUser (in the SubForm - kept in the
table). There is no need to keep the CurrentUser in the main form (in fact
it would be more hassle than it is worth) so I can't link the
Click to expand...
CurrentUser
to
a field in the main table. Does anyone know a way round this? My last resort
would be to let users view all records filtered into the subform but they
are only allowed to update/modify the records that they created.

TIA,

Neil.
Click to expand...
Click to expand...
 
Marshall Barton said:
I don't know where you're getting the "CurrentUser" info,
but all you need is an invisible text box on the main form
with that value in it. Probably as simple as using the
expression: =CurrentUser

Then you can use the text box's name as a Link Master item.

LinkMasterFields = "lngID;txtUser"
LinkChildFields = "lngMainID;UserName"
Click to expand...

Thanks for your reply,

I thought it was going to be as simple as that 2. Only thing is, I could
only choose a field in the underlying table, not the text box on the form. I
have solved the problem by still having the link child and link master
fields as I have above, but used the following in the recordsource of the
sub form:

SELECT tblWatch.* FROM tblWatch WHERE
(((tblWatch.strUser)=CurrentUser()));

This actually works (as far as I can tell) and you know what they say, if it
ain't broke...

Thanks again,

Neil.
 
Back
Top