I am designing a form for sales entry for an exhibition. Only products
that are sold are entered into the database. The products and the
customer details are entered as the sale transaction is recorded (in
the case of the products, using the “notinlist” event ).

The form is based on a query (qrySalesTransactions) which is based on
3 tables:

1. tblSalesTransactions (PK:fldSalesTransactionID)
2. tblCustomers (PK: fldCustomerID FK : fldSalesTransactionID)
3. tblProducts (PK : fldProductID FK : fldCustomerID)

In the qrySalesTransactions, the joins are all “ Include rows where
the joined fields from both tables are equal”.

When I open the form, it is completely blank – no records, not even
form controls or formatting. I have deduced this is because there are
no records yet entered.

It makes no difference if I change the query joins to “Include all
records from tblSalesTransactions and only those from [the other
table] where the joined fields are equal”.

If I enter a sales transaction directly into the
tblSalesTransactions, the form will open and correctly display all the
form controls. However, it will not allow me to add records (keeps
saying I cannot go to the specified record).

I am running Access97. Can someone explain to me where I am going
wrong with this form design?

Thanks

Gordon

On 15 July, 15:42, Marshall Barton <marshbar...@wowway.com> wrote:
> Gordon wrote:
> >I am designing a form for sales entry for an exhibition. Only products
> >that are sold are entered into the database. The products and the
> >customer details are entered as the sale transaction is recorded (in
> >the case of the products, using the “notinlist” event ).
>
> >The form is based on a query (qrySalesTransactions) which is based on
> >3 tables:
>
> >1. *tblSalesTransactions (PK:fldSalesTransactionID)
> >2. *tblCustomers (PK: fldCustomerID *FK : fldSalesTransactionID)
> >3. *tblProducts (PK : fldProductID *FK : fldCustomerID)
>
> >In the qrySalesTransactions, the joins are all “ Include rows where
> >the joined fields from both tables are equal”.
>
> >When I open the form, it is completely blank – no records, not even
> >form controls or formatting. *I have deduced this is because there are
> >no records yet entered.
>
> >It makes no difference if I change the query joins to “Include all
> >records from tblSalesTransactions and only those from [the other
> >table] where the joined fields are equal”.
>
> >If I enter *a sales transaction directly into the
> >tblSalesTransactions, the form will open and correctly display all the
> >form controls. *However, it will not allow me to add records (keeps
> >saying I cannot go to the specified record).
>
> >I am running Access97. *Can someone explain to me where I am going
> >wrong with this form design?
>
> The reaon the form is blank is, as you deduced, because
> there are no records to display AND because *new records can
> not be added. *Check the form's AllowAdditions property, it
> should be set to Yes.
>
> If additions are allowed, verify that your record source
> query is updatable by opening the query directly from the db
> window and trying to add a new record. *I suspect that,
> because the query includes multiple tables, it is not.
> Instead of trying to do everything in a single form based on
> 3 tables, use a query based on a single table for the form.
> Then use a separate subform for each of the other tables.
>
> --
> Marsh
> MVP [MS Access]- Hide quoted text -
>
> - Show quoted text -

Thanks for the quick response Marsh,

Allow additions was set to Yes but as you predicted the underlying
query was not updateable. I'll try your alternative approach.

Thanks again.

Gordon