additions-yes, data entry-yes, cannot add new record

[Guest]

Hi,
I've got Allow Additions set to Yes, and Data Entry set to yes, but still
cannot add new records in my form. Spent too long on this with still no idea
whre to go. Can anyone advise?

Regards,

 

[Rick Brandt]

Hi,
I've got Allow Additions set to Yes, and Data Entry set to yes, but
still cannot add new records in my form. Spent too long on this with
still no idea whre to go. Can anyone advise?

Regards,

Most likely your form is bound to a query that doesn't allow edits. Does it
include more than one table? That's a common reason for a query to be read
only.

See if you can edit directly in the query.

 

[Guest]

Run the Record Source of the form separetly, without the form.
Can you add record directly to there, if not, it could be that the record
source of the form include more then one table, and the link between the
tables cause the query not to allow additions.

In that case, use either a sub form for the other table.
Or, use dlookup to retrieve values if the second table is used to display
data only.

 

[Guest]

Hi Guys,
So Far no queries, just 2 tables. Have tried 1 table but want customer name
recognised so have created customer table with CustID and Customer Name
fields, and related to my outbound manifest table ( date, Job No., Part No.
etc..)

Ofer, Run record source, do you mean open form and run in datasheet view? If
so, no go there either. Want to use this form for data entry, query by form
and single record display for view & editing. Have never tried a subform,
will have to check it out though don't know where to start. Derived from the
main form I take it?

I've spent weeks going round in circles trying different things. Any help
would be much appreciated.

 

[Guest]

Hello again,
Have created a form with UserID table instead of Customer table and this
works fine. Use all 3 tables and again can't add new record. Does this help
anyone? Must be something wrong with my Customer table.

Regards,
Floyd.

 

[Dirk Goldgar]

Hello again,
Have created a form with UserID table instead of Customer table and
this works fine. Use all 3 tables and again can't add new record.
Does this help anyone? Must be something wrong with my Customer table.

If your form is based on three tables, or even two, then its
recordsource is a query -- but you may not see this query on the Queries
tab of the database window, if it's an inline SQL statement stored in
the form's RecordSource property.

Open the form in design view. Bring up the property sheet of the form.
Go to the Data tab of the property sheet, copy what it has on the Record
Source line, and paste it into your next reply message. It's altogether
likely that the record source is a non-updatable query.

 

[Guest]

Hi Dirk,
Record source for all 3 tables:
SELECT User.FirstName, Outbound.OutDate, Outbound.OutTime, Outbound.[Part
No], Outbound.PurchaseOrderNumber, Outbound.PurchaseOrderNumber1,
Outbound.Quantity, Customers.CompanyName, Outbound.ShipperTrackingCode,
Outbound.Comments FROM [User] INNER JOIN (Customers INNER JOIN Outbound ON
Customers.CustomerID=Outbound.CustomerID) ON User.UserID=Outbound.UserID;

Thank you for your time,
Regards,
Floyd.

 

[John Vinson]

Have created a form with UserID table instead of Customer table and this
works fine. Use all 3 tables and again can't add new record. Does this help
anyone? Must be something wrong with my Customer table.

If you're trying to update three tables - you *cannot* use just a
single form based on all three tables.

Instead use a Form with Subforms.

See the Orders form in the Northwind sample database for an example -
it deals with the Orders, OrderDetails and Products tables, and it's
updateable.

John W. Vinson[MVP]

 

[John Vinson]

Hi Guys,
So Far no queries, just 2 tables. Have tried 1 table but want customer name
recognised so have created customer table with CustID and Customer Name
fields, and related to my outbound manifest table ( date, Job No., Part No.
etc..)

You can base the Form on the Manifest table, and use a Combo Box for
the CustID field. This can *store* the CustID while displaying the
customer Name. Same deal for the Part and Job fields - you do *not*
need to include all the tables in your database as the form's
recordsource in order to see data from the tables!

John W. Vinson[MVP]

 

[Dirk Goldgar]

Hi Dirk,
Record source for all 3 tables:
SELECT User.FirstName, Outbound.OutDate, Outbound.OutTime,
Outbound.[Part No], Outbound.PurchaseOrderNumber,
Outbound.PurchaseOrderNumber1, Outbound.Quantity,
Customers.CompanyName, Outbound.ShipperTrackingCode,
Outbound.Comments FROM [User] INNER JOIN (Customers INNER JOIN
Outbound ON Customers.CustomerID=Outbound.CustomerID) ON
User.UserID=Outbound.UserID;

I'm not sure what the relationships among these tables are. The help
file, under "Troubleshoot queries", tells us a "query based on three or
more tables in which there is a many-to-one-to-many relationship" is not
normally updatable. However, it also advises, "Though you can't update
the data in the query directly, you can update the data in a form or
data access page based on the query if the form's RecordsetType property
is set to Dynaset (Inconsistent Updates)." So in your case, you may be
able to make a form based on this query updatable by changing the form's
Recordset Type property -- on the Data tab of the form's property
sheet -- to "Dynaset (Inconsistent Updates)".

That said, John Vinson's suggestion of using combo boxes to store the ID
value but display the related text sounds much better to me.