Design of a simple sales entry database form

[Gordon]

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

 

[Gordon]

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