Can't enter new records

A form I have been using now won't let me add a new record. I have entered over 100 records with the form no problem so I'm not sure were this error is coming from.
One field(named AccessionID) on the form requires a unique number. When I type in a number I know is unique it comes back and tells me I can't go to specified record. Then if I try to close I get the message record can't be recorded as it would "...create a duplicate value in the index, primary key or relationship..." In the underlying table this field is the primary key. When I go back to the table and search to see if it's a duplicate, Access indicates there is no record to be found matching that number. I have not had any problem entering records directly to the table. I have tried several different numbers and the form just won't accept them.
The primary key field in the table is set as: required: no; index: Yes(no duplicates).

I have one field "LastID" that shows the last accessionID number used so I know the next one to use. That seems to be working so I don't think the problem is stemming from that.

The **only** thing I did to this database was to change the form's date field default value from =Now() to =Date(). When I started having problems I changed back to =Now() and deleted the records entered under =Date() and re-entered. So I don't see how that could have caused a problem. But I really have no clue why I can't enter new records.
Sorry so long winded - but I hope someone can help.

Post your answer or comment

comments powered by Disqus
I have a main form with a sub form.

I cannot enter new records into the sub form! Only existing records are displayed.

If I open the record source of the sub form, I can enter new records

The subform's Allow additions, deletions & Edits are all "Yes"

What is causing this?
Where should I be looking?

This is driving me crazy

I have inherited some Foxpro tables which I am trying to convert to Access and I hae set the exported tables up and have tried to set the relationships between the tables so that I can run queries and setup forms to review and add new records. However I am struggling to get the relationships in place that will allow me to do this. When I try to drag the common fields from one table to another to setup the link I get a screen which shows Relationship Type as 'indterminate' rather than 'one-to-many'. Whatever I do I can't change the type of link and when I view a query based on the two linked tables the * in the record navigator is greyed out so I can't add new records.
I have a sample of the tables,links,queries and forms I have setup to date (with a limited number of records), if someone could have a look at it and advise where I am going wrong that would be great.
The data and the structure of the tables is pretty much fixed as this is what is coming through from Foxpro and there is a lot of historical data to bring across.
Many thanks

i have a relationship between a subject version (1) table and a subject cover (2) table.

1 = subject/version
2 = people trained in subject/version

the join is a composite on subjectCode and versionNo.

my main form is from table 1. i then added a subform to 2. the parent child is set up as 1.subject = 2.subject and 1.version = 2.version

i can add new records to the parent form (1) but i can;t add new records to the subform (2).

am i missing something here? can i add new records to table which have composite keys on ??


** edit. i can open the subform on it's own and i am able to add new records.

The last time I built a database I was using MS Access 2003. Now I'm building my first database from scratch in MS 2007. I've come across something that didn't occur in MS 2003 and it's driving me nuts. (I'm sorry if the following explanation is llong/convoluted).

I have a form called frmParks. This form pulls data from two tables: tblParks and tblContacts (which are linked by the field ParkAcronym, the primary key field for tblParks). tblParks has 69 park names and acronyms stored in there along with fields describing each park's location. I filled in the remaining fields for two parks (acronyms: CAVE and KEWE) when I was building the table to test the table. I also filled in the tblContacts fields that link to the CAVE park as a test.

I can edit the information in the tables fine. But when I open frmParks and try to edit any information, none of the fields work. I can see all the fields and their labels, but I can't edit any of them. Why can't I enter or edit any information in the form fields?

Also, I created another form called frmParkList which is a tabular list of all 69 park acronyms and names. There's a command button that opens frmPark. When I click on the command button for any park other than CAVE, frmPark opens up as a blank page with no fields or labels of any kind. When I click on the command button for CAVE, the only park with all of its tblPark and tblContacts fields filled in, I can see all of the fields and labels that should be in the form (but I can't edit them). Why is frmParks only opening properly for that one park?

BTW, frmParks also has a subform called sfmCollections that's based on a query. This subform is working fine: I can enter new records and edit exisiting ones no problem.

Please help!

I'm using Access 2007 and have a datasheet form setup on one of my tables. If I start with no records in the table, when I open the datasheet view I can't create more than one record. Once I've put anything in for the first record and exit the form and then come back, I can add records all day long. When the form is first displayed with no records the * row isn't displayed, the record navigation controls on the bottom are all enabled but even if I click on the New Blank Record control a new record isn't created.

For form properties I don't have a filter, the data is ordered by the primary key. AllowFormView = No, form is PopUp = No, Modal = No.

I've looked through my VBA and don't see anything keyed to restricting the ability to add a new record. I don't get any errors when the datasheet is displayed. Anyone have any ideas on what else I can look at that might cause the issue? I can post the onload vba if it would help.

I hope someone can help. I have a form that is based on a table and a second form that is based on a query. On the first form the person will enter information such as paper lenght and box's produced. When they click a button "Calculate" It takes them to a second form that will display the calculations for the first form. It works OK but when i enter a new record and click "Calculate" the second form is blank. Is there any way i can save the record and open the calculate form in one button click and view my results.

Thank you in advance.

I have a form subform. The subform is based on a query so I can add fields from another table. When I have the query configured with the second table I cannot add new records to the subform. If I take this table out of the underlying query I can add new records.

I've tried every join type with no luck. I'm a little rusty with Access and can't figure this out.

Any ideas?

Good morning, I have been trying to make a database and have run into a dilly of a pickle. I'm attaching the form to this entry so that interested persons can look at the problem if I am unable to explain clearly enough. The form controls of frmPtCharacteristics work fine until you attempt to add new data and then everything stops working. Entries can't be created, I am unable to search between records with the combo box, and i get an error prompt when I move to the next form because it tries to close the original form and can not save it.
I have a form whose underlying table is related to several others in a one-to-many fashion with updated fields and join property 2 (include all records from 'one' table in many table).
When I enter data the form associated with this table, and try to save a record I get the error 'You can't save this record at this time'. I'm not sure what to do about this. The data entered IS saved, but then everything just seems to stop working. Can someone please take a look at this, I'm stumped.

Thanks for the help,
Bevo S.

I have a database with Access 03 as the front end, and SQL sever 2008 as the back end.

I have a form, which uses a query - named 'visit_main' as a record source which used many linked tables, here are 3 example tables it uses:

Visit ID (PK)
Date of visit
Demographic ID

Visit ID (PK)

Visit ID (PK)

I have linked 'visit main' to the form, so that people can enter data into the tables. The Visit ID an auto number, so when a new visit is added one is automatically generated.

I have 'tests' as a subform, with the 'Visit ID' used as the link to the child and master field. This works fine, and the data is entered and stored in SQL Server

I do not have tests in a subform, instead I have the textboxs directly on the form for the data to be entered, with the control source as 'visit_main'.
The problem is I can't enter any data at all into these textboxes.

I'm think this is because it doesn't know what the visit ID is. When I enter the correct visit ID into SQL sever it then allows me to enter the other data via access no problem.

What can I do so that all of the data can be entered via access?

I have tried to explain as best as possible, but if you need to know any other information please ask, thanks in advance for any advice I get given.

I created a simple database with a single table and a form so I can track patients enrolled in a study. I added a few test records to make sure everything was working properly. Now I'm ready to use the database for real. Only trouble is, when I erased the test records I find that I can't add new ones. Can anyone tell me why and what to do about it?



I developed a database in Access 2000 on an XP SP2 machine with linked tables to another Access 2000 database.

Everything works fine using my machine, but when a user running Windows 2000 uses the database, they cannot add a new record to the linked table (either directly using the datasheet or a form). Running a query on the table also did not let me add a new record.

I have searched the MSDN libraries, but I wasn't able to find anything.

Any help much appreciated.

I am able to link to an Oracle table through an ODBC connection and update records (directly in the table).

I gave detailed instructions on how to do so to another user. She tells me she is able to see the table, but she can't enter data--she gets a "ding" sound when she tries. I am unable to replicate this situation--it always works for me.

Does anyone know what might be stopping her from entering data? I looked around the Internet for a while, and could not find anything about this issue.

Thanks in advance!

Hello everyone,

Im having a problem with access 2010. I created a new database and created the relationships.

However when i tried to create a form i noticed im not able to add any new records to my tables. Even if i don't use the form but directly on the tables the option for new record is greyed out.

I uploaded my database so you people could take a look however im not allowed to post links since i don't have 10 posts yet. Is there any other way i could give someone the link if they need it?

Thanks for the help already

Hi All

I have created a query from 3 tables. I have a main table 'Employee Details' (Tbl 1) another table 'Absence Details' (Tbl 2) and a third 'Holiday Entitlement' (Tbl 3)

Employee Details is my main db with a primary key set to Employee Initials. In Relationships I have joined my Employee Details to both Absence Details and Holiday Entitlement as a one to many relationship, ticked enforce referential integrity and also cascade delete related records, using Empoyee Initals as my join. I have no primary key set on these two.

My query once run brings up all the information I need, however I can't enter any data into this.

Is this due to one to many relationship being used more than once from any one table?

If so how can I get around this when I don't have any field in Tbl 2 and 3 which is unique therefore I can't set a primary key.

I'd appreciate anybody's advice.


OK....I am really new to access. This is my first database--that is my own -building from scratch. ((I am all self taught so far (meaning no offical class or training)...just things I've pick-up using access. )))

So my problem is I have made my pretty little form to add data easily. However, I can't add anything...I can't change any field nor can I ad new records. The little arrow with the star at the bottom left of the screen is grayed out. Even when I go into the actual table I can't add anything new. Plus I can't add anything to existing records.

What I have already tried---- I looked at the locked field in properties and no locks....I have the form set for data entry.

I created it with 4 excels files that I imported to access. Then I did a query and brought all the data into the query and based my form off of that. I created with Radial Inner Joins with prop_ID as the common field.

The only thing I didn't do was set a primary key. Could that be the problem? If so, how do I change it now?

Thank you in advance for any help given. Break it down for me, though, because I do not know what I am doing!!!!!!

I have two tables. They are linked by one field. I would like to use a form to enter data records. The problem is that I have fields from both tables, and I can only enter data into the fields that come from one of the tables. For example, there's a job description in both tables. However, I would like to enter data in one of the tables in the job descr. field without entering it in the other table. When I try to do this it tells me, "Can't enter value into blank field on "one" side of outer join. Is there any way to get around this.

I am trying to run an Append Query with Primary Keys (so as to eliminate duplicate records). Problem is that I would like to automate this in a macro but keep getting a "...can't append all records..." warning message, which requires I press a button to continue. BAH!!

I have tried Tools->Options and unchecked "Confirm Action Queries", with no luck.

Any suggestions?

I know what's wrong with my query, but I don't know how to fix it.

Attached is a screen shot of the relationships in my query. You'll notice I reference 5 tables, and 2 queries. The two Queries contain expressions for combining fields in the tables with date of birth and date of death info. For data entry I have separated day of birth (and death), month of birth (and death) and year of birth (and death). These queries combine those fields into date strings month/day/year so that I can use them in a complex calculation. (To find the exact age of a person upon death in years, months, and days.)

The problem I have is that while referencing these queries in the query I use for data entry, I can't add new records. I have discovered that if I remove those queries referencing expressions then I am once again able to add new records. However, if I remove them, then my calculation field on my form doesn't work!

Any ideas are welcome.
Also attached is a picture of my relationships for your reference. (Sorry if it's a little fuzzy, I had to resize it a tad in order to attach it.)

I am trying to figure out how to have a form enter new records only. Currently, my form opens and pulls all the records from the table. I was wondering if it is possible to have the form open with blank fields and enter new records to the table and not display all the records from the table.

I've posted in general because I really don't know what approach is best for this requirement. I can't even come up with a meaningful, yet succinct title.

Here's the problem. I have tables:

tblApplication - defines an application (name)
tblServer - defines a server
tblInstance - defines a partition on a server (defined in above table).
tblApplicationInstance - defines a specific instance of an application on an instance on a server (i.e. ties the above three tables together)

The tblApplicationInstance table has an autogenerated ID field as primary key, and foreign keys to tblApplication and tblInstance (and thus through this to tblServer).

All well and good. Now the next table

tblApplicationUse - defines that a specific project (a foreign key to another table but I don't think it is an important factor here) is using a specific application instance over a date range.

The question. How to allow entry of new tblApplicationUse records without having to find and enter the ID from tblApplicationInstance. Rather, I want the user to specify the Application, Server and Instance, but be limited to only those that are defined.

I tried a simple query, thinking it may allow me entry, but not so. I've been building some simple forms for query parameter prompting lately but am fairly inexperienced with these. The crudest form of prompting I can think of is to simply apply a drop-down to the ID field (in tblApplicationUse) and use a multi-column format here. But it ends up very wide and is less than ideal.

Can anybody give me ideas on the 'proper' way to do this?

I'm not sure where to post this, maybe VBA or Forms would be better, but I think it's fundamentally an index problem, so I'm posting here. I've searched both the net and here, but most of the questions are about preventing duplicate records.

I want to prevent duplicate records but save changes to existing records.

I have a form based on a table that has a 3-field-unique-index (but not the primary index, which is autonumber). I've written a function to check for existing records with the given combination of the three fields, as long as all have data (it bails if any are null). I'm calling the function from BeforeUpdate for each of the textbox controls. I was also calling it from the form's BeforeUpdate, but commented it out as it seemed redundant. I have other code there verifying that the user wants to keep the changes.

I'm trying to save the users from getting all the way to the end before discovering there's a problem. To test it, I added then deleted one character from one of the three relevant fields in an existing record, and bing, up pops my alert that I'm violating the index. Then I can't leave the field, I'm stuck there because I can't save the record.

I want it set up so that other fields in the record can be changed/updated AND so that a new record for the same person can't be entered (a dupe).

Oh, and I'm running into all kinds of problems with acCmdSaveRecord not being available. I feel like an idiot that I haven't been able to make sense of the posts about that. I can just comment it out and let Access save the record when it closes the form. But my bosses really want the users prompted about saving changes.

Any thoughts or suggestions or insights greatly appreciated. I really tried to search this out, so any tips for better searching are also appreciated.

Here's the code for the function:

	Public Function CheckForClientDupes()
    Dim response As Variant
    Dim strFamID, strLName, strFName As String
    Dim strFilter As String
    If IsNull(Me!FamilyIDNo) Or IsNull(Me!strLastName) Or IsNull(Me!strFirstName) Then
        Exit Function
    End If

    strFilter = "[strFamilyIDNo] = """ & Me!FamilyIDNo & """ And " & "[strLastName] = """ & Me!strLastName & """ And " &
"[strFirstName] = """ & Me!strFirstName & """"
    If DCount("*", "tblClients", strFilter) > 0 Then
       response = MsgBox("There is already a client with this combination of FamilyID, Last and First names in this database.
Would you like to Continue Anyway (Yes) or cancel data entry and Erase Your Changes (No)?", vbYesNo, "Duplicate Client
        If response = vbYes Then
'            DoCmd.RunCommand acCmdSaveRecord
            response = MsgBox("You have chosen to cancel your changes.  Your changes will be erased.", vbOKCancel +
vbQuestion, "Data Entry Cancelled")
            If response = vbOK Then
                Exit Function
            End If
        End If
    End If

'    Me.FamilyIDNo.SetFocus

End Function

Hello Access Gurus,

I am really new to Access. Using 2007 version of the application. I created my tables and relationships, and some forms to be able to edit and enter new information in my DB.

I noticed that when I open a form w/ an existing record on it - it's very easy to make a mistake and change a record somewhat. I want to avoid as much as possible this accidental change of the record, but allow creation of a new record AND intentional editing of the existing ones.

Here is my idea:

- When the form is opened, the user has "read-only" access by default to all of the controls on that form;
- then if the user needs to edit the record he/she would press a button that will switch the form for the record to the "read-write" mode;
- then when the user goes to the next existing record the form automatically goes back to the default state ("read-only") and if the user needs to change it too he/she will have to press that "read-write" button again;
- then if the user needs to create a new record he/she presses another button that opens a new record in "read-write" mode.

I guess all of the above will need to be done through a macro, it's just that since I am new to Access I don't know what code I will need...

Could anybody help me, please?

Greatly appreciated!


I have a form which is used to enter car parts, the form has a Save button and the X at the top right hand corner. I have the following code in place to make sure the user enters the required fields.

	Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control, Source As String
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Controls(0).Caption & "' field" & nl & _
"You can't save this record until this data is provided" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
Cancel = True
Exit For
End If
End If
End Sub

I have a number of problems with this though:

1) If the user moves the wheel on the mouse, it tries to move to a new record and gives validation error.

Is there any way to remove the ability of mouse scrolling?

2) If the user presses the X in the top corner, it gives the validation error message and also shows the "You can't save this record at this time" error message.

Is there a way of changing that error message and upon the user clicking "yes" cancelling the data and closing the form?

I have searched for a solution but come up with nothing.

Here goes...

I have a main form for ORDERS and a subform for ORDER DETAILS (based on a query). These forms work harmoniously together...i can enter an order and add and delete detail records at will. New ORDER DETAILS are automatically populated with the corresponding order number from the current record in the main ORDERS form. Everything saves correctly and everything is just peachy.

I discovered i cannot add a new detail record to an order that has no existing order details. No new blank ORDER DETAILS line pops up waiting for me to fill in the missing info... nothing happens. the subform is dead and i can't even click the new record button.

I understand that if there are no corresponding records, the query that my subform is based upon will return nothing...but isn't there some way i can force the ORDER DETAILS subform to allow an initial detail record to be added to a virgin ORDER?

[P.S. this is NOT a problem with saving the ORDER first. This happens with ANY Order that doesnt already have one or more corresponding ORDER DETAILS]

Not finding an answer? Try a Google search.