Can't add a new record in Form View

[Stuart Grant]

I have a database with one main form which has 9 text boxes and two combos
which list the details of each record as you step through them in a list box
with a list of all the last names. I also have command buttons for Add New
Record, Delete Record, Print and Quit.
The code for the AddNew Record button is

Private Sub AddNew_Click()
DoCmd.GotoRecord , , acNewRec
.......
.......
End Sub

It used to work but now when I click the boxes all go blank. I can select
an item from the first combo but when I tab to a text box, I cannot enter
anything. The text boxes are not locked but none of them will allow me to
enter anything. What can be wrong ?

I did not have navigation buttons in the form but have tried adding them.
Same problem.

Stuart

 

[Van T. Dinh]

1. Have you changed the RecordSource Query / SQL recently?
If you have, the RecordSource Query / SQL might have become non-updatable
and you can't add record using a non-updatable Query?

Open the DatasheetView of the Query / SQL and see if you can modify / add
record.

2. Have you check the AllowAdditions Property of the Form?

 

[Stuart Grant]

Thanks for jumping in.
The Allow Additions Property of the Form Is YES.
I have not altered the Record Source Query recently but looked at it in
Datasheet view. I can modify it - but, you are right, I cannot add to it.
How did it become not updatable ?
Stuart

 

[Van T. Dinh]

Can you see the "New Record" row in the DatasheetView of the Query?

Please post the details of the relevant Table(s) and the SQL String of the
RecordSource Query.

 

[Stuart Grant]

Yes, I can see the "New Record" in the Datasheet View of the Query. I can
go to this row direct or with the * button, but cannot enter anything. The
first column stays at (AutoNumber). It should go to 79 when you tab to the
next column. It doesn't and I can't enter anything in any of the other
columns.

The SQL String is as follows -
SELECT [Personal Data].RecNo, [Personal Data].FirstName, [Personal
Data].LastName, [Personal Data].Address1, [Personal Data].Address2,
[Personal Data].Address3, [Personal Data].Telephone1, [Personal
Data].Telephone2, [Personal Data].Partner, [Personal Data].Notes,
Countries.Country, Titles.Title
FROM Titles INNER JOIN (Countries INNER JOIN [Personal Data] ON
Countries.CountryID=[Personal Data].CountryID) ON Titles.TitleID=[Personal
Data].TitleID;

From this you can see the details of the tables. Most of the information is
in Personal Data. Countries and Titles provide the data for the two combos.

I'm sure Data Entry worked before. I'm baffled as to why it no longer does.

Stuart

 

[Van T. Dinh]

SQL that involve more than 2 Tables are often not updatable. Check Access
Help topic "Updatable Queries".

Since you store the TitleID and CountryID in the Table [Personal Data], my
recommendation is to get rid of the Tables [Titles] and [Countries] from the
Form's RecordSource, i.e. the RecordSource only has the Table [Personal
Data] and on the Form, use ComboBoxes to show and to allow user to select
Title / Country by text but the BoundColumns of the ComboBoxes are the
Fields [TitleID] and [CountryID] so that the ID values are sotred in the
Table [Personal Data].

 

[Stuart Grant]

Van: Thank you for the advice.
I still don't understand why it no longer works and I found the Help files
hard to understand. No matter.
I followed your advice and of course it works . Problem solved.
Thank you for your patience.
Stuart

 

[Van T. Dinh]

I always try to make Forms being used for data entry / data edit as simple
as possible since it is generally more than enough trying to predict the
user's actions. Lot of developers got stuck because they think the users
will follow a certain logical path (same as the developers' logical path)
for data entry / data edit but invariably, the users will do something
different.

Glad you got it working.