Relational query problem???

[WANNABE]

I have 2 tables an employee table and a projects table, a one to many
relation from employee to projects on empid. Employee table primary key is
empid (autoNumber), there is also a userid field which when an employee is
entered has a default setting that pulls in the network user ID
<environ("username")> I'm trying to create a form for project entry and
update. The form has all project table fields including empid, and an
unbound field named userid which is set to deafult to environ("username").

I am trying to build a query on the tasks.empid field default value to asign
the empid field to the emp.empid field where userid =emp.userid, and I've
tried this >>>

select Emp.EmpID from emp where me.userid = emp.userid

and I get a syntax error. Does this need to be a relation query, and how
would it be constructed?

 

[Allen Browne]

The keyword "Me" is permitted only in VBA code.
In a SQL statement you must use the full reference to the form, e.g.:
select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];

If you are actually building the query string in code:
strSql = "select Emp.EmpID from emp where emp.userid = """ & Me.userid &
""";"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[WANNABE]

Thanks Allen, I've been to your web site, and it has been very helpful.
I'm still doing something wrong, I tried all of the following, (not sure if
you meant [Form1] or that I should use the actual name of the form.

=select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
=select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];

Each one gives me the same error message "THE SYNTAX OF THE SUBQUERY IN THIS
EXPRESSION IS INCORRECT"
next line "CHECK THE SUBQUERY'S SYNTAX AND ENCLOSE THE SUBQUERY IN
PARENTHESES"
then there are the OK and HELP buttons, but here is another problem, (I'm
hoping you might also know the solution to this) when I click help, I get
only a blank grey window that looks just like the help screen but there's no
help..

Thank you.
========================================================

The keyword "Me" is permitted only in VBA code.
In a SQL statement you must use the full reference to the form, e.g.:
select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];

If you are actually building the query string in code:
strSql = "select Emp.EmpID from emp where emp.userid = """ & Me.userid
& """;"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have 2 tables an employee table and a projects table, a one to many
relation from employee to projects on empid. Employee table primary key
is empid (autoNumber), there is also a userid field which when an employee
is entered has a default setting that pulls in the network user ID
<environ("username")> I'm trying to create a form for project entry and
update. The form has all project table fields including empid, and an
unbound field named userid which is set to deafult to environ("username").

I am trying to build a query on the tasks.empid field default value to
asign the empid field to the emp.empid field where userid =emp.userid,
and I've tried this >>>

select Emp.EmpID from emp where me.userid = emp.userid

and I get a syntax error. Does this need to be a relation query, and how
would it be constructed?

 

[Allen Browne]

Hang on: in what context are you trying to use this?

If you are placing a query into the Control Source of a text box, that won't
work. Use DLookup() instead:
http://allenbrowne.com/casu-07.html

If you are typing the expression into the Field row of a query, then the
subquery message makes sense, and you need to put brackets around it.

Yes: you do use your form name in place of the Form1 example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, I've been to your web site, and it has been very helpful.
I'm still doing something wrong, I tried all of the following, (not sure
if you meant [Form1] or that I should use the actual name of the form.

=select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
=select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];

Each one gives me the same error message "THE SYNTAX OF THE SUBQUERY IN
THIS EXPRESSION IS INCORRECT"
next line "CHECK THE SUBQUERY'S SYNTAX AND ENCLOSE THE SUBQUERY IN
PARENTHESES"
then there are the OK and HELP buttons, but here is another problem, (I'm
hoping you might also know the solution to this) when I click help, I get
only a blank grey window that looks just like the help screen but there's
no help..

Thank you.
========================================================

The keyword "Me" is permitted only in VBA code.
In a SQL statement you must use the full reference to the form, e.g.:
select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];

If you are actually building the query string in code:
strSql = "select Emp.EmpID from emp where emp.userid = """ & Me.userid
& """;"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

I have 2 tables an employee table and a projects table, a one to many
relation from employee to projects on empid. Employee table primary key
is empid (autoNumber), there is also a userid field which when an
employee is entered has a default setting that pulls in the network user
ID <environ("username")> I'm trying to create a form for project entry
and update. The form has all project table fields including empid, and
an unbound field named userid which is set to deafult to
environ("username").

I am trying to build a query on the tasks.empid field default value to
asign the empid field to the emp.empid field where userid =emp.userid,
and I've tried this >>>

select Emp.EmpID from emp where me.userid = emp.userid

and I get a syntax error. Does this need to be a relation query, and
how would it be constructed?

 

[WANNABE]

Yes, I was putting this query in the Default Value property of an unbound
text field.
have not used Dlookup, yet, will post results soon.
thanks again

Hang on: in what context are you trying to use this?

If you are placing a query into the Control Source of a text box, that
won't work. Use DLookup() instead:
http://allenbrowne.com/casu-07.html

If you are typing the expression into the Field row of a query, then the
subquery message makes sense, and you need to put brackets around it.

Yes: you do use your form name in place of the Form1 example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, I've been to your web site, and it has been very helpful.
I'm still doing something wrong, I tried all of the following, (not sure
if you meant [Form1] or that I should use the actual name of the form.

=select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
=select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];

Each one gives me the same error message "THE SYNTAX OF THE SUBQUERY IN
THIS EXPRESSION IS INCORRECT"
next line "CHECK THE SUBQUERY'S SYNTAX AND ENCLOSE THE SUBQUERY IN
PARENTHESES"
then there are the OK and HELP buttons, but here is another problem, (I'm
hoping you might also know the solution to this) when I click help, I get
only a blank grey window that looks just like the help screen but there's
no help..

Thank you.
========================================================

The keyword "Me" is permitted only in VBA code.
In a SQL statement you must use the full reference to the form, e.g.:
select Emp.EmpID from emp where emp.userid =
[Forms].[Form1].[userid];

If you are actually building the query string in code:
strSql = "select Emp.EmpID from emp where emp.userid = """ &
Me.userid & """;"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

"WANNABE" <breichenbach AT istate DOT com> wrote in message
I have 2 tables an employee table and a projects table, a one to many
relation from employee to projects on empid. Employee table primary key
is empid (autoNumber), there is also a userid field which when an
employee is entered has a default setting that pulls in the network user
ID <environ("username")> I'm trying to create a form for project entry
and update. The form has all project table fields including empid, and
an unbound field named userid which is set to deafult to
environ("username").

I am trying to build a query on the tasks.empid field default value to
asign the empid field to the emp.empid field where userid =emp.userid,
and I've tried this >>>

select Emp.EmpID from emp where me.userid = emp.userid

and I get a syntax error. Does this need to be a relation query, and
how would it be constructed?

 

[WANNABE]

Thanks Allen, This worked fine,
=DLookUp("EmpID","Emp","UserID='" & [UserID] & "'")
I've bookmarked your ELookup for later, not needed here but surely soon.

The Other part of my question now is.>
If when this form is opened, the value in Emp.EmpID is NULL, (that means
that no mother record exists, and one needs to be created before the project
form can be filled in. I'm thinking that it would be VBA code On Open
Procedure that tests the Emp.EmpID field and if NULL opens the EmpEntry Form
to create that record first. I appreciate any thoughts and comments..
-------
ALSO; I have a great little procedure that I would like to share, where
could I do that??
It is what I call a self propagating dropdown list, I don't remember where I
got it or if it was something I thought of, but I'm sure I had help creating
it.
Using a comboBox that retrieves from a table, and restricting entries to
what is in the list and then (IfNotInList) prompts if you would like to add
the entry to the list.
=================================

Yes, I was putting this query in the Default Value property of an unbound
text field.
have not used Dlookup, yet, will post results soon.
thanks again

Hang on: in what context are you trying to use this?

If you are placing a query into the Control Source of a text box, that
won't work. Use DLookup() instead:
http://allenbrowne.com/casu-07.html

If you are typing the expression into the Field row of a query, then the
subquery message makes sense, and you need to put brackets around it.

Yes: you do use your form name in place of the Form1 example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, I've been to your web site, and it has been very helpful.
I'm still doing something wrong, I tried all of the following, (not
sure if you meant [Form1] or that I should use the actual name of the
form.

=select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Project
Tracker].[userid];
=select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];
select Emp.EmpID from emp where emp.userid = [Forms].[Form1].[userid];

Each one gives me the same error message "THE SYNTAX OF THE SUBQUERY IN
THIS EXPRESSION IS INCORRECT"
next line "CHECK THE SUBQUERY'S SYNTAX AND ENCLOSE THE SUBQUERY IN
PARENTHESES"
then there are the OK and HELP buttons, but here is another problem,
(I'm hoping you might also know the solution to this) when I click help,
I get only a blank grey window that looks just like the help screen but
there's no help..

Thank you.
========================================================

The keyword "Me" is permitted only in VBA code.
In a SQL statement you must use the full reference to the form, e.g.:
select Emp.EmpID from emp where emp.userid =
[Forms].[Form1].[userid];

If you are actually building the query string in code:
strSql = "select Emp.EmpID from emp where emp.userid = """ &
Me.userid & """;"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

"WANNABE" <breichenbach AT istate DOT com> wrote in message
I have 2 tables an employee table and a projects table, a one to many
relation from employee to projects on empid. Employee table primary
key is empid (autoNumber), there is also a userid field which when an
employee is entered has a default setting that pulls in the network
user ID <environ("username")> I'm trying to create a form for project
entry and update. The form has all project table fields including
empid, and an unbound field named userid which is set to deafult to
environ("username").

I am trying to build a query on the tasks.empid field default value to
asign the empid field to the emp.empid field where userid =emp.userid,
and I've tried this >>>

select Emp.EmpID from emp where me.userid = emp.userid

and I get a syntax error. Does this need to be a relation query, and
how would it be constructed?

 

[Allen Browne]

I'm not sure I have your structure here, so I can't really comment on the
best solution, but in general the idea of automatically creating records as
soon as the user moves to a new one is anathema to me. You end up with
blank, meaningless records, merely because the user visited a new record.

By all means, OpenForm so the user can enter one. Or if you are talking
about a subform, cancel its BeforeUpdate event if there is no record in the
main form, so the user enters the primary record first.

If you wrote the code (or have rights to it), you can post the code in a new
thread. Prefix the thread name with "INF:" or "Solution:" or something that
indicates your thread is not a request for help but a solution or
information offered.

 

[WANNABE]

anathema = Any person or thing that is intensely disliked.

OK. I have done a very poor job of explaining this project, allow me to try
one last time
There are only 2 tables (EMP and TASKS) EMP holds one record per user and
each user needs to have a record before they can enter task information.
When they open the task form, a check is done to match their login ID to a
field in the EMP table, if there is NO match then the emp form needs to
open, allow them to create there record, close and return the user to the
task form.

Here is the field structure for each table;
EMP
EmpID AutoNumber
FirstName Text
LastNAme Text
Phone Text
UserID Text
TASKS
TaskID AutoNumber
EmpID Text
TaskType Text
.........

UserId auto fills from the default properties of the form
<<environ("username")>> and this is what is tested when the task for opens.

So, if that is any more clear, do you have any suggestions as to how to best
code the test, and then if no match open the emp form and return to the task
form only upon completing an emp record...
Thanks.

I'm not sure I have your structure here, so I can't really comment on the
best solution, but in general the idea of automatically creating records
as soon as the user moves to a new one is anathema to me. You end up with
blank, meaningless records, merely because the user visited a new record.

By all means, OpenForm so the user can enter one. Or if you are talking
about a subform, cancel its BeforeUpdate event if there is no record in
the main form, so the user enters the primary record first.

If you wrote the code (or have rights to it), you can post the code in a
new thread. Prefix the thread name with "INF:" or "Solution:" or something
that indicates your thread is not a request for help but a solution or
information offered.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, This worked fine,
=DLookUp("EmpID","Emp","UserID='" & [UserID] & "'")
I've bookmarked your ELookup for later, not needed here but surely soon.

The Other part of my question now is.>
If when this form is opened, the value in Emp.EmpID is NULL, (that means
that no mother record exists, and one needs to be created before the
project form can be filled in. I'm thinking that it would be VBA code On
Open Procedure that tests the Emp.EmpID field and if NULL opens the
EmpEntry Form to create that record first. I appreciate any thoughts and
comments..
-------
ALSO; I have a great little procedure that I would like to share, where
could I do that??
It is what I call a self propagating dropdown list, I don't remember
where I got it or if it was something I thought of, but I'm sure I had
help creating it.
Using a comboBox that retrieves from a table, and restricting entries to
what is in the list and then (IfNotInList) prompts if you would like to
add the entry to the list.

 

[Allen Browne]

So, you want to open another form where the user can enter their details
before this one proceeds.

Private Sub Form_Open(Cancel As Integer)
Dim strUser as String
Dim iTryCount as Integer

strUser = Environ("username")
Do While IsNull(DLookup("EmpID", "Emp", "EmpID = """ & strUser & """)
And Not Cancel
iTryCount = iTryCount + 1
If iTryCount > 3 Then
Cancel = True
MsgBox "Ask your supervisor for help."
DoCmd.Quit
End If
DoCmd.OpenForm "frmEmp", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=strUser
Loop

End Sub

The other form will need to be opened modally and in data entry mode, and
you can then test whether it succeeded. You can pass the new emp value in
OpenArgs, and then use the events of the target form to put it into the new
record for you.

(BTW, what's horrifying is not you, but the concept of automatically
creating a record every time a visits the new record.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

anathema = Any person or thing that is intensely disliked.

OK. I have done a very poor job of explaining this project, allow me to
try one last time
There are only 2 tables (EMP and TASKS) EMP holds one record per user and
each user needs to have a record before they can enter task information.
When they open the task form, a check is done to match their login ID to a
field in the EMP table, if there is NO match then the emp form needs to
open, allow them to create there record, close and return the user to the
task form.

Here is the field structure for each table;
EMP
EmpID AutoNumber
FirstName Text
LastNAme Text
Phone Text
UserID Text
TASKS
TaskID AutoNumber
EmpID Text
TaskType Text
........

UserId auto fills from the default properties of the form
<<environ("username")>> and this is what is tested when the task for
opens.

So, if that is any more clear, do you have any suggestions as to how to
best code the test, and then if no match open the emp form and return to
the task form only upon completing an emp record...
Thanks.

I'm not sure I have your structure here, so I can't really comment on the
best solution, but in general the idea of automatically creating records
as soon as the user moves to a new one is anathema to me. You end up with
blank, meaningless records, merely because the user visited a new record.

By all means, OpenForm so the user can enter one. Or if you are talking
about a subform, cancel its BeforeUpdate event if there is no record in
the main form, so the user enters the primary record first.

If you wrote the code (or have rights to it), you can post the code in a
new thread. Prefix the thread name with "INF:" or "Solution:" or
something that indicates your thread is not a request for help but a
solution or information offered.

Thanks Allen, This worked fine,
=DLookUp("EmpID","Emp","UserID='" & [UserID] & "'")
I've bookmarked your ELookup for later, not needed here but surely soon.

The Other part of my question now is.>
If when this form is opened, the value in Emp.EmpID is NULL, (that means
that no mother record exists, and one needs to be created before the
project form can be filled in. I'm thinking that it would be VBA code
On Open Procedure that tests the Emp.EmpID field and if NULL opens the
EmpEntry Form to create that record first. I appreciate any thoughts
and comments..
-------
ALSO; I have a great little procedure that I would like to share, where
could I do that??
It is what I call a self propagating dropdown list, I don't remember
where I got it or if it was something I thought of, but I'm sure I had
help creating it.
Using a comboBox that retrieves from a table, and restricting entries to
what is in the list and then (IfNotInList) prompts if you would like to
add the entry to the list.

 

[Bob]

Would not have taken it any other way,
clearly your perception of my intent
justifies deep concern and shivers up
your (and My) spine. (I understood)....

Thank you very much, I just opened this
and it looks like what I was looking
for. What I was working on was close,
not really . This is much more, what
I had was the dlookup and a couple of if
statements. Now my test to myself is to
get it implemented and working, with no
more help. I will post my success or
failure. Thank you again...
========================================
=====
"Allen Browne"
message
bl...
So, you want to open another form where
the user can enter their details
before this one proceeds.

Private Sub Form_Open(Cancel As Integer)
Dim strUser as String
Dim iTryCount as Integer

strUser = Environ("username")
Do While IsNull(DLookup("EmpID",
"Emp", "EmpID = """ & strUser & """)
And Not Cancel
iTryCount = iTryCount + 1
If iTryCount > 3 Then
Cancel = True
MsgBox "Ask your supervisor
for help."
DoCmd.Quit
End If
DoCmd.OpenForm "frmEmp",
DataMode:=acFormAdd, _
WindowMode:=acDialog,
OpenArgs:=strUser
Loop

End Sub

The other form will need to be opened
modally and in data entry mode, and
you can then test whether it succeeded.
You can pass the new emp value in
OpenArgs, and then use the events of the
target form to put it into the new
record for you.

(BTW, what's horrifying is not you, but
the concept of automatically
creating a record every time a visits
the new record.)

--
Allen Browne - Microsoft MVP. Perth,
Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne
at mvps dot org.

"WANNABE" <breichenbach AT istate DOT
com> wrote in message
gbl...

anathema = Any person or thing that is intensely disliked.

OK. I have done a very poor job of

explaining this project, allow me to

try one last time
There are only 2 tables (EMP and

TASKS) EMP holds one record per user and

each user needs to have a record

before they can enter task information.

When they open the task form, a check

is done to match their login ID to a

field in the EMP table, if there is NO

match then the emp form needs to

open, allow them to create there

record, close and return the user to the

task form.

Here is the field structure for each table;
EMP
EmpID AutoNumber
FirstName Text
LastNAme Text
Phone Text
UserID Text
TASKS
TaskID AutoNumber
EmpID Text
TaskType Text
........

UserId auto fills from the default properties of the form
<<environ("username")>> and this is

what is tested when the task for

opens.

So, if that is any more clear, do you

have any suggestions as to how to

best code the test, and then if no

match open the emp form and return to

the task form only upon completing an emp record...
Thanks.

"Allen Browne"
..gbl...

BeforeUpdate event if there is no record
in

the main form, so the user enters the primary record first.

If you wrote the code (or have rights to it), you can post the code in a
new thread. Prefix the thread name with "INF:" or "Solution:" or
something that indicates your thread is not a request for help but a
solution or information offered.

"WANNABE" <breichenbach AT istate DOT com> wrote in message

bl...

Thanks Allen, This worked fine,
=DLookUp("EmpID","Emp","UserID='" & [UserID] & "'")
I've bookmarked your ELookup for later, not needed here but surely soon.

The Other part of my question now is.>
If when this form is opened, the value in Emp.EmpID is NULL, (that means
that no mother record exists, and one needs to be created before the
project form can be filled in. I'm thinking that it would be VBA code
On Open Procedure that tests the Emp.EmpID field and if NULL opens the
EmpEntry Form to create that record first. I appreciate any thoughts
and comments..

procedure that I would like to share,
wheresomething I thought of, but I'm sure I
had(IfNotInList) prompts if you would like
to

 

[WANNABE]

Sorry about any name confusion, not
working from the office today and I
forgot to change my screen name..
"Bob" <SameAsB4> wrote in message
bl...
Would not have taken it any other way,
clearly your perception of my intent
justifies deep concern and shivers up
your (and My) spine. (I understood)....

Thank you very much, I just opened this
and it looks like what I was looking
for. What I was working on was close,
not really . This is much more, what
I had was the dlookup and a couple of if
statements. Now my test to myself is to
get it implemented and working, with no
more help. I will post my success or
failure. Thank you again...
========================================
=====
"Allen Browne"
message
bl...
So, you want to open another form where
the user can enter their details
before this one proceeds.

Private Sub Form_Open(Cancel As Integer)
Dim strUser as String
Dim iTryCount as Integer

strUser = Environ("username")
Do While IsNull(DLookup("EmpID",
"Emp", "EmpID = """ & strUser & """)
And Not Cancel
iTryCount = iTryCount + 1
If iTryCount > 3 Then
Cancel = True
MsgBox "Ask your supervisor
for help."
DoCmd.Quit
End If
DoCmd.OpenForm "frmEmp",
DataMode:=acFormAdd, _
WindowMode:=acDialog,
OpenArgs:=strUser
Loop

End Sub

The other form will need to be opened
modally and in data entry mode, and
you can then test whether it succeeded.
You can pass the new emp value in
OpenArgs, and then use the events of the
target form to put it into the new
record for you.

(BTW, what's horrifying is not you, but
the concept of automatically
creating a record every time a visits
the new record.)

--
Allen Browne - Microsoft MVP. Perth,
Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne
at mvps dot org.

"WANNABE" <breichenbach AT istate DOT
com> wrote in message
gbl...

anathema = Any person or thing that is intensely disliked.

OK. I have done a very poor job of

explaining this project, allow me to

try one last time
There are only 2 tables (EMP and

TASKS) EMP holds one record per user and

each user needs to have a record

before they can enter task information.

When they open the task form, a check

is done to match their login ID to a

field in the EMP table, if there is NO

match then the emp form needs to

open, allow them to create there

record, close and return the user to the

task form.

Here is the field structure for each table;
EMP
EmpID AutoNumber
FirstName Text
LastNAme Text
Phone Text
UserID Text
TASKS
TaskID AutoNumber
EmpID Text
TaskType Text
........

UserId auto fills from the default properties of the form
<<environ("username")>> and this is

what is tested when the task for

opens.

So, if that is any more clear, do you

have any suggestions as to how to

best code the test, and then if no

match open the emp form and return to

the task form only upon completing an emp record...
Thanks.

"Allen Browne"
..gbl...

BeforeUpdate event if there is no record
in

the main form, so the user enters the primary record first.

If you wrote the code (or have rights to it), you can post the code in a
new thread. Prefix the thread name with "INF:" or "Solution:" or
something that indicates your thread is not a request for help but a
solution or information offered.

"WANNABE" <breichenbach AT istate DOT com> wrote in message

bl...

Thanks Allen, This worked fine,
=DLookUp("EmpID","Emp","UserID='" & [UserID] & "'")
I've bookmarked your ELookup for later, not needed here but surely soon.

The Other part of my question now is.>
If when this form is opened, the value in Emp.EmpID is NULL, (that means
that no mother record exists, and one needs to be created before the
project form can be filled in. I'm thinking that it would be VBA code
On Open Procedure that tests the Emp.EmpID field and if NULL opens the
EmpEntry Form to create that record first. I appreciate any thoughts
and comments..

procedure that I would like to share,
wheresomething I thought of, but I'm sure I
had(IfNotInList) prompts if you would like
to

 

[WANNABE]

Thanks again Allen, That worked beautifully!!
One little issue, one line posted as
Do While IsNull(DLookup("EmpID","Emp", "EmpID = """ & strUser & """) And Not
Cancel
And I'm guessing that the """ got converted some where from single quotes to
double quotes.
When I first looked at it, I just figured he knows something, I don't (a lot)
maybe I didn't fully understand what I read, but it errored out and after I
changed it to
' " & strUser & " ' ")) without spaces, everything worked great.
Thanks again for all the help, this is so much better then what I would have
come up with..

I am still working on getting the empid from the emp form to the task form but I
will work on that for a few days before I post a new thread...
AGAIN, Thank you, and I hope you have a great weekend!!!
=================================================
"WANNABE" <SameAsB4> wrote in message
Sorry about any name confusion, not
working from the office today and I
forgot to change my screen name..
"Bob" <SameAsB4> wrote in message
bl...
Would not have taken it any other way,
clearly your perception of my intent
justifies deep concern and shivers up
your (and My) spine. (I understood)....

Thank you very much, I just opened this
and it looks like what I was looking
for. What I was working on was close,
not really . This is much more, what
I had was the dlookup and a couple of if
statements. Now my test to myself is to
get it implemented and working, with no
more help. I will post my success or
failure. Thank you again...
========================================
=====
"Allen Browne"
message
bl...
So, you want to open another form where
the user can enter their details
before this one proceeds.

Private Sub Form_Open(Cancel As Integer)
Dim strUser as String
Dim iTryCount as Integer

strUser = Environ("username")
Do While IsNull(DLookup("EmpID",
"Emp", "EmpID = """ & strUser & """)
And Not Cancel
iTryCount = iTryCount + 1
If iTryCount > 3 Then
Cancel = True
MsgBox "Ask your supervisor
for help."
DoCmd.Quit
End If
DoCmd.OpenForm "frmEmp",
DataMode:=acFormAdd, _
WindowMode:=acDialog,
OpenArgs:=strUser
Loop

End Sub

The other form will need to be opened
modally and in data entry mode, and
you can then test whether it succeeded.
You can pass the new emp value in
OpenArgs, and then use the events of the
target form to put it into the new
record for you.

(BTW, what's horrifying is not you, but
the concept of automatically
creating a record every time a visits
the new record.)

--
Allen Browne - Microsoft MVP. Perth,
Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne
at mvps dot org.

"WANNABE" <breichenbach AT istate DOT
com> wrote in message
gbl...

anathema = Any person or thing that is intensely disliked.

OK. I have done a very poor job of

explaining this project, allow me to

try one last time
There are only 2 tables (EMP and

TASKS) EMP holds one record per user and

each user needs to have a record

before they can enter task information.

When they open the task form, a check

is done to match their login ID to a

field in the EMP table, if there is NO

match then the emp form needs to

open, allow them to create there

record, close and return the user to the

task form.

Here is the field structure for each table;
EMP
EmpID AutoNumber
FirstName Text
LastNAme Text
Phone Text
UserID Text
TASKS
TaskID AutoNumber
EmpID Text
TaskType Text
........

UserId auto fills from the default properties of the form
<<environ("username")>> and this is

what is tested when the task for

opens.

So, if that is any more clear, do you

have any suggestions as to how to

best code the test, and then if no

match open the emp form and return to

the task form only upon completing an emp record...
Thanks.

"Allen Browne"
..gbl...

BeforeUpdate event if there is no record
in

the main form, so the user enters the primary record first.

If you wrote the code (or have rights to it), you can post the code in a
new thread. Prefix the thread name with "INF:" or "Solution:" or
something that indicates your thread is not a request for help but a
solution or information offered.

"WANNABE" <breichenbach AT istate DOT com> wrote in message

bl...

Thanks Allen, This worked fine,
=DLookUp("EmpID","Emp","UserID='" & [UserID] & "'")
I've bookmarked your ELookup for later, not needed here but surely soon.

The Other part of my question now is.>
If when this form is opened, the value in Emp.EmpID is NULL, (that means
that no mother record exists, and one needs to be created before the
project form can be filled in. I'm thinking that it would be VBA code
On Open Procedure that tests the Emp.EmpID field and if NULL opens the
EmpEntry Form to create that record first. I appreciate any thoughts
and comments..

procedure that I would like to share,
wheresomething I thought of, but I'm sure I
had(IfNotInList) prompts if you would like
to