Adding new records to query recordset

[Microsoft News]

I am using a form with a subform to perform a query on an Access database.
The sub-form performs the following query:

SELECT DISTINCTROW Payments.* FROM Payments WHERE
(((Payments.WorkorderID)=[forms]![Workorders by Customer]![Workorders by
Customer Subform].[form]![WorkorderID]));


So I have a subset of a single table filtered by WorkorderID. I then want to
be able to add new records directly from the datasheet but I cannot get them
in the table. I have checked the rules on adding to queries and I cannot see
how this fails.

Seems to me the new record needs to have a forced item (ie. the filter
condition) of the WorkorderID (the item used to filter the recordset).

Any help appreciated.

Barry.

 

[Sylvain Lafontaine]

Your subform doesn't work because you don't have follow the rules for
automatic lookup. Instead of doing the filtering yourself, use the Link
Child Fields and Link Master Fields for the subform, don't use the
DistinctRow instruction and don't forget to have a primary key defined for
your table (it doesn't hurt to have well defined foreign keys either).

The query for your subform should be:

Select Payements.* from Payments

The filtering for WorkorderID will be done by the two Link fields. The
field ... .[form]![WorkorderID] in your subform shoudn't be there, either.
Having two fields [WorkoderID] with the same name, one the subform and one
the recordset of the subform, can lead to confusion.

S. L.