My form refuses new data entry!

[Guest]

I have a "Transaction" form which I have based on a query. The form allows me
to enter new data into some fields, but not all.

I then looked at the underlying query in datasheet view, and I found I had
the same problem. When trying to crate a new record, some fields can be
filled in, but not all.

Can anyone explain what the cause of this is?

The query is based on a transaction type table, a customers table, a
countries table and a transaction header table.

 

[John W. Vinson]

I have a "Transaction" form which I have based on a query. The form allows me
to enter new data into some fields, but not all.

I then looked at the underlying query in datasheet view, and I found I had
the same problem. When trying to crate a new record, some fields can be
filled in, but not all.

Can anyone explain what the cause of this is?

Not without seeing the query, no. Please post the SQL, indicate how
the tables are related, and which fields you cannot update.

John W. Vinson [MVP]

 

[Guest]

Not without seeing the query, no. Please post the SQL, indicate how
the tables are related, and which fields you cannot update.

John W. Vinson [MVP]

John,

The unwieldy (Access-generated) SQL statement is:

SELECT TransactionsHeader.[Document No], TransactionsHeader.CustomerID,
[Transaction Type].[Transaction Type], TransactionsHeader.Date,
TransactionsHeader.ShipName, TransactionsHeader.ShipAddressLine1,
TransactionsHeader.ShipAddressLine2, TransactionsHeader.ShipAddressLine3,
TransactionsHeader.ShipAddressLine4, TransactionsHeader.CountryID,
Customers.CompanyName, Customers.CompanyOrDepartment, Customers.[Address Line
1], Customers.[Address Line 2], Customers.[Address Line 3],
Customers.[Address Line 4]
FROM [Transaction Type] INNER JOIN (Customers INNER JOIN TransactionsHeader
ON Customers.CustomerID=TransactionsHeader.CustomerID) ON [Transaction
Type].TransactionTypeID=TransactionsHeader.TransactionTypeID;

The tables are related in the following manner:
The Transaction Type table has a one-to-many relationship with the
Transaction Header table (one transaction type may occur many times).
The Customers table has a one-to-many relationship with the Transaction
Header table (one customer may deal with us on any number of occasions).

The fields I cannot fill in are:
Document No
Date
Company Name
Ship Name
Ship Address Line 1
Ship Address Line 2
Ship Address Line 3
Ship Address Line 4
Country

Many thanks

 

[John W. Vinson]

The tables are related in the following manner:
The Transaction Type table has a one-to-many relationship with the
Transaction Header table (one transaction type may occur many times).
The Customers table has a one-to-many relationship with the Transaction
Header table (one customer may deal with us on any number of occasions).

It's rare that a three-table query will be updateable at all, and
typically only the "manyest" table (Transactions, here) will be.
Normally one would use a Form with Subforms rather than basing a form
on a multitable query. Might that be a possiblity here?

John W. Vinson [MVP]