on click SQL error because table is not specified

[amjjam]

Hi,

I have a contacts form with 3 tabs: 1) contact’s name and business
information; 2) communications – a subform; and 3) contact’s general
information. The communications subform requires the ContactsID number so
data is linked and stored appropriately in the underlying communications
table. Form tabs 1 and 3 have a task button labeled View Contact Fact Sheet.
I have linked the button to an on click macro to open the Contact Fact Sheet
in print preview. It reads:

Action: OpenReport
Report Name: Contact Fact Sheet
View: Print Preview
Filter: (blank)
Where Condition: [ContactsID]=[Forms]![Contacts]![ContactsID]
Window Mode: Normal

When I click on the button to open the contact fact sheet, I get an error
message which reads: The specified field ‘[ContactsID]’ could refer to more
than one table listed in the FROM clause of your SQL statement. Can anyone
tell me how to get this button to work? When I hit the help button on the
error message, it indicates that the SQL statement must specify which table
the ContactsID should be drawn from. I tried using code builder to insert:

Private Sub ViewContactCommand_Click()
SELECT Contacts.ContactsID FROM Contacts, [ContactsID];
End Sub

That gives me a syntax error and I don’t know if I need to delete the macro
altogether to test run that appropriately. Working with Access 2003 is new
for me, so if you can give detailed instructions, I’d appreciate it. Thanks!
~ amjjam

 

[Beetle]

Sounds like your form is based on a query that joins two
tables and it has two ContactID fields (one form each table),
so you need to specify which one you want to reference.
Usually in this case the query will add the table name to
indicate the difference, so the actual fields in your query might
like [Contacts].[ContactsID] and [Communications].[ContactsID].
Try this;

Action: OpenReport
Report Name: Contact Fact Sheet
View: Print Preview
Filter: (blank)
Where Condition: [ContactsID]=[Forms]![Contacts]![Contacts].[ContactsID]
Window Mode: Normal

 

[amjjam]

The Contacts form is based on a contacts table. The communication form is
based on a communication table. The ContactsID is the primary key of the
Contacts table (an AutoNumber) and a regular field in the Communication table
(a Number). The relationship is one-to-many from the Contacts Table to the
Communication Table. Neither form is based on a query. The Communication form
is a subform of the Contacts form. I tried altering the Where Condition in
the macro as you suggested and got the same error message I've been getting.
Any other ideas? ~ amjjam

Sounds like your form is based on a query that joins two
tables and it has two ContactID fields (one form each table),
so you need to specify which one you want to reference.
Usually in this case the query will add the table name to
indicate the difference, so the actual fields in your query might
like [Contacts].[ContactsID] and [Communications].[ContactsID].
Try this;

Action: OpenReport
Report Name: Contact Fact Sheet
View: Print Preview
Filter: (blank)
Where Condition: [ContactsID]=[Forms]![Contacts]![Contacts].[ContactsID]
Window Mode: Normal

--
_________

Sean Bailey

Hi,

I have a contacts form with 3 tabs: 1) contact’s name and business
information; 2) communications – a subform; and 3) contact’s general
information. The communications subform requires the ContactsID number so
data is linked and stored appropriately in the underlying communications
table. Form tabs 1 and 3 have a task button labeled View Contact Fact Sheet.
I have linked the button to an on click macro to open the Contact Fact Sheet
in print preview. It reads:

Action: OpenReport
Report Name: Contact Fact Sheet
View: Print Preview
Filter: (blank)
Where Condition: [ContactsID]=[Forms]![Contacts]![ContactsID]
Window Mode: Normal

When I click on the button to open the contact fact sheet, I get an error
message which reads: The specified field ‘[ContactsID]’ could refer to more
than one table listed in the FROM clause of your SQL statement. Can anyone
tell me how to get this button to work? When I hit the help button on the
error message, it indicates that the SQL statement must specify which table
the ContactsID should be drawn from. I tried using code builder to insert:

Private Sub ViewContactCommand_Click()
SELECT Contacts.ContactsID FROM Contacts, [ContactsID];
End Sub

That gives me a syntax error and I don’t know if I need to delete the macro
altogether to test run that appropriately. Working with Access 2003 is new
for me, so if you can give detailed instructions, I’d appreciate it. Thanks!
~ amjjam