Building an expression control on a form created through queries (1 Viewer)

Divinite

New member
Local time
Today, 09:05
Joined
Feb 26, 2003
Messages
5
I have a form that is created from multiple queries. In the form specific relationships, I have two tables, Events and Registration. I also have a query SupervisorName.

They contain the following pertinent fields:

Events
EmployeeID

Registration
EmployeeID

SupervisorName (Query)
EmployeeID (From Registration Table)
FirstName (From Employees Table)
LastName (From Employees Table)

This started with just the first two tables and only the reference number would appear in regards to EmployeeID. I need to have the actual name showing in that field, so I added the query that links to the FirstName and LastName fields. The EmployeeID field also appears on the Employees table.

If I just try to add the Employees table to the query for this form, I get a joint expression not allowed error, hence the query.

With the query I currently have in place

Registration![EmployeeID], SupervisorName![LastName] & ", " & SupervisorName![FirstName]

I get a #Name? error when viewing the form. I've looked through all the help files I can find, but have had no luck in creating a working expression. All I need is for it reference the EmployeeID that is linked to the particular item (in this case it's for a class registration through the Registration table/form) and for that reference to be converted to the LastName, FirstName standard via the Employees table.

Thank you in advance for your help!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
You need to modify the query that you are using as the recordsource of the form, to include the supervisor information. Then the fields will be available as selections in the controlsource combo.
 

Divinite

New member
Local time
Today, 09:05
Joined
Feb 26, 2003
Messages
5
Still need more than one field...

The fields are included in the recordsource already, however they are not tieing in correctly. I get a #Name? error for the expression that I'm using with them.

The main problem is that I need 2 fields to appear LastName, FirstName which are drawn from the field EmployeeID when it is input on another form.

The picture included with this post is of the SQL Query Builder for this particular form. All of the forms that I need are there, I don't know if I need to add more relationships to get this to work or what might else be the problem. I know that if I add the table that the fields originally reside on (Employees) that I get an error...joint expression not allowed.

Thanks!
 

Attachments

  • access1.jpg
    access1.jpg
    19.6 KB · Views: 98

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
You should fix the Employee table to have a primary key. Then you can check the enforce referential integrity box for the relationship as shown in your picture. But, this has nothing to do with your #error problem. That is caused by having an unbound control name that is the same name as a field from the form's recordsource. Check the control name for the control where you are seeing #error and change it so that it does NOT duplicate the name of one of the fields in the form's recordsource.
 

Divinite

New member
Local time
Today, 09:05
Joined
Feb 26, 2003
Messages
5
More fun problems

I went back and added the Employees table to the SQL Query instead of the other query (SupervisorNames). Now I'm getting an error (as seen in the picture attached). No clue how to fix it (of course the help file doesn't actually HELP!)...

If I can fix the query so that it works...it should be fairly easy going from there to find where the problem is coming about.

Thanks!
 

Attachments

  • access2.jpg
    access2.jpg
    36.2 KB · Views: 92

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
EmployeeID should NOT be in both places. Notice that Access has drawn two join lines to the Employee table. I think it should be removed from the Registration table.
 

Divinite

New member
Local time
Today, 09:05
Joined
Feb 26, 2003
Messages
5
Only problem is that the registration uses that field in another form. If I remove it from the Registration table other forms will fail to work because of it.

The picture attached here is one of the relationship structure for the whole database. My next post will have a screenshot of the actual db forms and what they need to be doing.
 

Attachments

  • access3.jpg
    access3.jpg
    51.6 KB · Views: 94

Divinite

New member
Local time
Today, 09:05
Joined
Feb 26, 2003
Messages
5
db layout

The Attendees form holds a subform (Attendees Subform) which gathers data that is input on the Registration form. The registration form is separate and contains the information shown. The purpose is to add classes to the listings for each of the Attendees/Volunteers.

What I need to happen is for the Shift Supervisor field to show up as a name instead of a number on the Attendees Subform.

There's two options...

1) Figure out how this mess works and get that relationship to work.

2) Create a new relationship (or field, etc) that allows me to chose a Supervisor in the Attendees field that corresponds with the choices in the Employees field

Either of these will work, however I'm not quite sure how to actually go around doing either of them. Can I just add a new field in the Attendees table for EmployeeID and link it that way to complete the second option? If that is the case, then I can just remove all references to Supervisor on the subform as it is not necessary.

Thank you!
 

Attachments

  • access4.jpg
    access4.jpg
    43.7 KB · Views: 90

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
Registration is the table that makes the many-to-many relationship between Attendees and Events. The employeeID is already present in the Event table so it does not make any sense to have it also in the Registration table. Would the values ever be different?

To get the employee name to show in the subform, use a query as the recordsource of the subform. The query would join the Registration table to the Event table and the Event table to the Employee table to obtain the employee name.
 

Users who are viewing this thread

Top Bottom