Trouble with syntax

[Afrosheen via AccessMonster.com]

I have a form that is using a query joined called Supervisor2. With in that
form I have a field called [email1] a true/false field. I also have the usual
stuff. [Lname], [Fname] and another field with the email address of the
people. Some have email addresses and some don't. That's where I'm having
problems as to how to display the people that just has email addresses so I
can display them in a combo box.

I know I can create another query form and do it that way, but that means
another query that would have to be started from scratch. I know there is a
way of getting the information I need. I just forgot how.

I've tried using a control source on the combo box. ="email1"=True and that
didn't work.
I've tried putting that same statement in the filter control of the form and
still nothing.

When I do this all the names still show up.

I know there's an answer to this and it just baffles me.

I've appreciated the help I've gotten here before and I thank you for it.

Thank you for reading this post and helping me out.

 

[pietlinden]

I have a form that is using a query joined called Supervisor2. With in that
form I have a field called [email1] a true/false field. I also have the usual
stuff. [Lname], [Fname] and another field with the email address of the
people. Some have email addresses and some don't. That's where I'm having
problems as to how to display the people that just has email addresses soI
can display them in a combo box.

I know I can create another query form and do it that way, but that means
another query that would have to be started from scratch. I know there isa
way of getting the information I need. I just forgot how.

set the control source of the combobox to something like
SELECT ...
FROM [YourTable]
WHERE [email1] = True
ORDER BY...;

or
SELECT ...
FROM [YourTable]
WHERE [email1] = -1
ORDER BY...;

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me. This is what I put in for the code in the
control source on the combo box and it didn't work.

SELECT tblmain.lname & _
FROM qrySupervisor2 & _
WHERE [Email1] = True ORDER BY [lname];

I tried your suggestion with the true = 1 and it didn't change any thing.
When I ran the form the combo box had all the names with and with out email
addresses.

I have a form that is using a query joined called Supervisor2. With in that
form I have a field called [email1] a true/false field. I also have the usual

[quoted text clipped - 6 lines]

another query that would have to be started from scratch. I know there is a
way of getting the information I need. I just forgot how.

set the control source of the combobox to something like
SELECT ...
FROM [YourTable]
WHERE [email1] = True
ORDER BY...;

or
SELECT ...
FROM [YourTable]
WHERE [email1] = -1
ORDER BY...;

 

[John W. Vinson]

Thanks for getting back to me. This is what I put in for the code in the
control source on the combo box and it didn't work.

SELECT tblmain.lname & _
FROM qrySupervisor2 & _
WHERE [Email1] = True ORDER BY [lname];

I tried your suggestion with the true = 1 and it didn't change any thing.
When I ran the form the combo box had all the names with and with out email
addresses.

Try returning the name from the query where it is to be found:

SELECT qrySupervisor2.lname & _
FROM qrySupervisor2 & _
WHERE qrySupervisor2.[Email1] = True ORDER BY [lname];

Or, if there is an Email address field in the query, check it directly:

SELECT qrySupervisor2.lname & _
FROM qrySupervisor2 & _
WHERE [qrySupervisor2].Email IS NOT NULL ORDER BY [lname];

 

[Afrosheen via AccessMonster.com]

Hi John and thanks for getting back to me. Sorry I'm having such a problem
with this email thing.

I tried putting your code in the control source of the combo box. When I did
then I saw a little triangle in the upper left hand corner. I guess that
meant that the code was wrong or I just had it in the wrong place. I don't
believe the code was wrong.

Is the control source the correct place to place your code?

Thanks for getting back to me. This is what I put in for the code in the
control source on the combo box and it didn't work.

[quoted text clipped - 6 lines]

When I ran the form the combo box had all the names with and with out email
addresses.

Try returning the name from the query where it is to be found:

SELECT qrySupervisor2.lname & _
FROM qrySupervisor2 & _
WHERE qrySupervisor2.[Email1] = True ORDER BY [lname];

Or, if there is an Email address field in the query, check it directly:

SELECT qrySupervisor2.lname & _
FROM qrySupervisor2 & _
WHERE [qrySupervisor2].Email IS NOT NULL ORDER BY [lname];

 

[John W. Vinson]

Hi John and thanks for getting back to me. Sorry I'm having such a problem
with this email thing.

I tried putting your code in the control source of the combo box. When I did
then I saw a little triangle in the upper left hand corner. I guess that
meant that the code was wrong or I just had it in the wrong place. I don't
believe the code was wrong.

Is the control source the correct place to place your code?

Afrosheen, I cannot see your database, and my code was a PURE GUESS at
possible fieldnames. It assumes that there is a field named in your
query which contains the actual email address. If there is not such a field
you'll get the triangle error. Sorry I wasn't more explicit about my
assumptions!

Adapt the query, or the code, to *your* own fieldnames and structures.

 

[Afrosheen via AccessMonster.com]

Not a problem John.

Here is what I now have and it seems to display correctly. The problem is
that when I click on the name it "Control can't be edited. It is bound to an
unknown field.

Private Sub cmboSupervisor_GotFocus()

cmboSupervisor.ControlSource = "SELECT empsuper.tblmain.[lname] & ', '&
empsuper.tblmain.[fname] " & _
"FROM EmpSuper " & _
"WHERE (((EmpSuper.tblmain.Email) Is not Null))" & _
"ORDER BY EmpSuper.location;"

End Sub

I tried OnClick but still got the same error.

Private Sub cmboSupervisor_Click()
Dim tet As String
tet = cmboSupervisor
MsgBox tet
End Sub

What you see for the fields are correct and the ones I use.

Hi John and thanks for getting back to me. Sorry I'm having such a problem
with this email thing.

[quoted text clipped - 5 lines]

Is the control source the correct place to place your code?

Afrosheen, I cannot see your database, and my code was a PURE GUESS at
possible fieldnames. It assumes that there is a field named in your
query which contains the actual email address. If there is not such a field
you'll get the triangle error. Sorry I wasn't more explicit about my
assumptions!

Adapt the query, or the code, to *your* own fieldnames and structures.[/QUOTE]

 

[John W. Vinson]

Not a problem John.

Here is what I now have and it seems to display correctly. The problem is
that when I click on the name it "Control can't be edited. It is bound to an
unknown field.

Private Sub cmboSupervisor_GotFocus()

cmboSupervisor.ControlSource = "SELECT empsuper.tblmain.[lname] & ', '&
empsuper.tblmain.[fname] " & _
"FROM EmpSuper " & _
"WHERE (((EmpSuper.tblmain.Email) Is not Null))" & _
"ORDER BY EmpSuper.location;"

End Sub

I tried OnClick but still got the same error.

Private Sub cmboSupervisor_Click()
Dim tet As String
tet = cmboSupervisor
MsgBox tet
End Sub

What you see for the fields are correct and the ones I use.

The syntax Empsuper.tblmain.[fieldname] is certainly incorrect. What is
empsuper? is it a Query? What does it have to do with tblmain?

If it's a Query, and if it contains the email address of the supervisor, then
just remove all the .tblmain refererences.

 

[Afrosheen via AccessMonster.com]

The EmpSuper of EmpSuper.tblMain.[fieldname] is the join query that I set up
from a previous you helped me with so I could get all the Supervisors and
Employees together as one.

I tried your suggestion and took out the EmpSuper and just left the tblMain
and still got the same error.

cmboSupervisor.ControlSource = "SELECT tblmain.[lname] & ', '& tblmain.[fname]
" & _
"FROM EmpSuper " & _
"WHERE (((tblmain.Email) Is not Null))" & _
"ORDER BY location;"

When I took out the tblMain from tblMain.[lname], for example than an error
is produced saying a possible duplicate field exists. That would be tblMain
[lname] and Supervisor[Lname] from that query.

Maybe I can change the property of the Supervisor.[lname] to something else.
I'll have to try it.

By the way, Thanks for all the help you've been. I really appreciate it.

Not a problem John.

[quoted text clipped - 21 lines]

What you see for the fields are correct and the ones I use.

The syntax Empsuper.tblmain.[fieldname] is certainly incorrect. What is
empsuper? is it a Query? What does it have to do with tblmain?

If it's a Query, and if it contains the email address of the supervisor, then
just remove all the .tblmain refererences.

 

[John W. Vinson]

The EmpSuper of EmpSuper.tblMain.[fieldname] is the join query that I set up
from a previous you helped me with so I could get all the Supervisors and
Employees together as one.

You'll probably need to assign an alias to the names of duplicate fields. I
haven't seen the actual SQL of your EmpSuper query so I can't be specific, but
if you have the same table and the same fieldname twice in the query, you
could use syntax like

SELECT tblMain.LName, Super.LName AS SupervisorLName
FROM tblMain INNER JOIN tblMain AS Super
ON tblMain.SupervisorID = Super.EmployeeID;

This would give you two different fieldnames (LName and SupervisorLName) for
the two different people's last names.

 

[Afrosheen via AccessMonster.com]

Good morning John. Here is the SQL

SELECT tblMain.Lname, tblMain.Fname, tblMain.Location, qrytaps.WorkPlan,
qrytaps.WorkRec, qrytaps.IntDue, qrytaps.IntRec, qrytaps.FinalDue, qrytaps.
FinalRec, Supervisors.Lname, Supervisors.Fname, tblMain.Email, tblMain.Email1
FROM (tblMain INNER JOIN tblMain AS Supervisors ON tblMain.Super1 =
Supervisors.StaffId) INNER JOIN qrytaps ON tblMain.StaffId = qrytaps.
tblTaps_StaffId;

The EmpSuper of EmpSuper.tblMain.[fieldname] is the join query that I set up
from a previous you helped me with so I could get all the Supervisors and
Employees together as one.

You'll probably need to assign an alias to the names of duplicate fields. I
haven't seen the actual SQL of your EmpSuper query so I can't be specific, but
if you have the same table and the same fieldname twice in the query, you
could use syntax like

SELECT tblMain.LName, Super.LName AS SupervisorLName
FROM tblMain INNER JOIN tblMain AS Super
ON tblMain.SupervisorID = Super.EmployeeID;

This would give you two different fieldnames (LName and SupervisorLName) for
the two different people's last names.

 

[John W. Vinson]

Good morning John. Here is the SQL

SELECT tblMain.Lname, tblMain.Fname, tblMain.Location, qrytaps.WorkPlan,
qrytaps.WorkRec, qrytaps.IntDue, qrytaps.IntRec, qrytaps.FinalDue, qrytaps.
FinalRec, Supervisors.Lname, Supervisors.Fname, tblMain.Email, tblMain.Email1
FROM (tblMain INNER JOIN tblMain AS Supervisors ON tblMain.Super1 =
Supervisors.StaffId) INNER JOIN qrytaps ON tblMain.StaffId = qrytaps.
tblTaps_StaffId;

You can assign aliases in the query grid by typing the alias followed by a
colon before the fieldname. Try editing your SQL to

SELECT tblMain.Lname, tblMain.Fname, tblMain.Location, qrytaps.WorkPlan,
qrytaps.WorkRec, qrytaps.IntDue, qrytaps.IntRec, qrytaps.FinalDue, qrytaps.
FinalRec, Supervisors.Lname AS SupLName, Supervisors.Fname AS SupFName,
tblMain.Email, tblMain.Email1
FROM (tblMain INNER JOIN tblMain AS Supervisors ON tblMain.Super1 =
Supervisors.StaffId) INNER JOIN qrytaps ON tblMain.StaffId = qrytaps.
tblTaps_StaffId;

and then look at it in query design view to see how Access handles it.

 

[Afrosheen via AccessMonster.com]

Thanks John. I did change the SQL to your statement and it seems to be
working.

One problem solved out of many.

Good morning John. Here is the SQL

[quoted text clipped - 4 lines]

Supervisors.StaffId) INNER JOIN qrytaps ON tblMain.StaffId = qrytaps.
tblTaps_StaffId;

You can assign aliases in the query grid by typing the alias followed by a
colon before the fieldname. Try editing your SQL to

SELECT tblMain.Lname, tblMain.Fname, tblMain.Location, qrytaps.WorkPlan,
qrytaps.WorkRec, qrytaps.IntDue, qrytaps.IntRec, qrytaps.FinalDue, qrytaps.
FinalRec, Supervisors.Lname AS SupLName, Supervisors.Fname AS SupFName,
tblMain.Email, tblMain.Email1
FROM (tblMain INNER JOIN tblMain AS Supervisors ON tblMain.Super1 =
Supervisors.StaffId) INNER JOIN qrytaps ON tblMain.StaffId = qrytaps.
tblTaps_StaffId;

and then look at it in query design view to see how Access handles it.