Changed relationships has stopped a form working (1 Viewer)

[John O'Malvern]

A form to enter a new member's details has stopped working, after adding an intermediate table.
Previously:

now:

(the red squiggles mark fields I know are not needed)
the tblContact_Categories was needed between Contacts and tblCategoryTypes as one contact can have more than one CategoryType (ie qualified on more than one type of vehicle)
I have tried to modify the query on which the input form is based, but cannot get the SQL to work (Access shows syntax errors)

FWIW here is the current SQL:

SELECT Contacts.ContactTypeID, Contacts.[CategoryType ID], [Category Types].[Category Type], Contacts.IAMNo, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.Location, Contacts.City, Contacts.StateOrProvince, Contacts.PostalCode, Contacts.MobilePhone, Contacts.Email, Contacts.MWAMRenewal, Contacts.[Date Joined], [Contact Types].ContactType, Contacts.ObserverID, Contacts.ContactID, Contacts.MemberNo
FROM [Contact Types] INNER JOIN ([Category Types] INNER JOIN (Contacts ON [tblCategoryTypes].[CategoryTypeID] = [tblContact_Categories].[ContactID]) ON [Contact Types].[ContactTypeID] = [Contacts].[ContactTypeID])
i.e.

SELECT Contacts.ContactTypeID, Contacts.[CategoryType ID], [Category Types].[Category Type], Contacts.IAMNo, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.Location, Contacts.City, Contacts.StateOrProvince, Contacts.PostalCode, Contacts.MobilePhone, Contacts.Email, Contacts.MWAMRenewal, Contacts.[Date Joined], [Contact Types].ContactType, Contacts.ObserverID, Contacts.ContactID, Contacts.MemberNo
FROM [Contact Types] INNER JOIN ([Category Types] INNER JOIN (Contacts ON [tblCategoryTypes].[CategoryTypeID] = [tblContact_Categories].[ContactID]) ON [Contact Types].[ContactTypeID] = [Contacts].[ContactTypeID])

Any help gratefully received!

 

[XPS35]

FROM [Contact Types] INNER JOIN ([Category Types]

Check your SQL. There is no such relationship.

 

[Ken Sheridan]

Try this:

SELECT
    Contacts.ContactTypeID,
    Contacts.CategoryTypeID,
    tblCategoryTypes.[Category Type],
    Contacts.IAMNo,
    Contacts.FirstName,
    Contacts.LastName,
    Contacts.Address,
    Contacts.Location,
    Contacts.City,
    Contacts.StateOrProvince,
    Contacts.PostalCode,
    Contacts.MobilePhone,
    Contacts.Email,
    Contacts.MWAMRenewal,
    Contacts.[Date Joined],
    [Contact Types].ContactType,
    Contacts.ObserverID,
    Contacts.ContactID,
    Contacts.MemberNo
FROM
    (
        (
            Contacts
            INNER JOIN tblContact_Categories ON Contacts.ContactID = tblContact_Categories.ContactID
        )
        INNER JOIN tblCategoryTypes ON tblCategoryTypes.CategoryTypeID = Contacts.CategoryTypeID
    )
    INNER JOIN [Contact Types] ON [Contact Types].ContactID = Contacts.ContactID;

 

[Pat Hartman]

When you don't know how to write SQL, use the QBE to build at least the Select clause and the Joins. It doesn't create pretty strings but it does get the syntax right.

Keep in mind that this query may not be updateable.