fully qualified field names on same form.

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Something is happening with my form fields and it is stumping me. I have 2
fields on the same form with identical field names but in different tables.
Both fields are called "ID". Because of this, I need to use the fully
qualified field names of each field to differentiate the field names on the
form. e.g. <tablename1>.ID, <tablename2>.ID. Here is the complicated part.

Depending on who the user is ( you must sign into the database so the
database knows who you are) I use 2 different recordsources for the form.
One recordsource has both <tablename1>.ID and <tablename2>.ID in it and the
other recordsource only has <tablename1>.ID in it. When I use the
recordsource that only has the one "ID" field, Access won't let me use the
fully qualified field name in the controlsource of the field. If I do use
the fully qualified name I get a "#NAME?" when the field displays. So I have
to use just "ID" as the controlsource when one ID is on the form, but
"<tablename1>.ID" as the controlsource when both "ID" fields are on the form.
Is that how Access suppose to work?
Thanks
 
Alias one of the ID fields in your query.

For example, say it looks like this in the query design grid:
Field: ID ID
Table: Table1 Table2
Sort:
then type an alias and a colon in front of one of the IDs, like this:
Field: ID T2ID: ID
Table: Table1 Table2
Sort:

The fields now have different names, so the problem is solved.
 
Thanks Allen. However, I don't use a query design grid. I use a
Me.recordsource "SELECT" statement in VB. Is there something different I
would have to do to do it in VB.
 
If you know how to write the SQL statement, then you know how to:
SELECT Table2.ID AS T2ID
FROM ...
 
Back
Top