fully qualified field names on same form.

[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

 

[Allen Browne]

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.

 

[Anthony]

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.

 

[Allen Browne]

If you know how to write the SQL statement, then you know how to:
SELECT Table2.ID AS T2ID
FROM ...