Access 2010 Can't Add new record

I have a 2010 Access front end with a SQL backend.

I have three views and a table that users can select from to view a list of records - Customers (table), ActiveCustomers (view; default), InactiveCustomers (view), and Prospects (view).  

I have a wizard created "New Record" button that works for adding a new record when the record source is Customers, Inactive, or Prospects but when I try to use it with ActiveCustomers, I get an error message: You can't go to the specified record

I have a combo box that allows me to select the record source.

The code behind that dropdown is as follows (if it helps)
Private Sub RecordSourceCombo_Change()
    'Original Form/Data(Tab)/Order By value is Customer.CREDIT_TERMS DESC
    Select Case RecordSourceCombo.Value
        Case "Active"
            Form.RecordSource = "ActiveCustomers"
        Case "Inactive"
            Form.RecordSource = "InactiveCustomers"
        Case "Prospect"
            Form.RecordSource = "ProspectCustomers"
        Case Else
            Form.RecordSource = "Customer"
    End Select
    Form.Requery
End Sub

Open in new window


For your reference:
The SQL Views were created using the following commands (so if one works, they all should, right? and manually creating a record in the ActiveCustomers view works through SSMS.
CREATE VIEW ActiveCustomers AS SELECT * FROM Customer WHERE Deleted = 0 AND RecordType = 'A'
GO

CREATE VIEW InactiveCustomers AS SELECT * FROM Customer WHERE Deleted = 0 AND RecordType = 'I'
GO

CREATE VIEW ProspectCustomers AS SELECT * FROM Customer WHERE Deleted = 0 AND RecordType = 'P'
GO

Open in new window

I have added pictures of the macro and the Properties Data tab on the form as well for reference.
Form Data Properties New Record Macro
Can someone tell me how I can get this to allow the creation of a new record when "ActiveCustomers" is selected?
LVL 98
Lee W, MVPTechnology and Business Process AdvisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There are several things that can cause Access to be unable to create new records in SQL Server:

-- No Primary Key defined on the table(s)
-- NULL values in Bit fields (commonly used to store Boolean data)
-- Multiple Tables in the View, with improper Joins (Access can't figure out which table to update)
-- Invalid indexing on the linked table (you can view these in Access, and even add Indexes if needed)
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
-- No Primary Key defined on the table(s)
The "CustomerTable" is the source of all records (as you can see that from the statements used to create the views I included with the question).  When I set "All" it's using the customer table which has a primary key defined.  This would seem to mean this cannot be my problem.

-- NULL values in Bit fields (commonly used to store Boolean data)
If the Customer table is the source of all records in the views (as you can see from what I posted in the question) AND I can add a record to it, then by extension, there cannot be any nulls in bit fields.   This would seem to mean this cannot be my problem.

-- Multiple Tables in the View, with improper Joins (Access can't figure out which table to update)
This is not the problem; the views use only one table, as you can see from the information I originally posted.

-- Invalid indexing on the linked table (you can view these in Access, and even add Indexes if needed)
I doubt it, but indexing is my biggest weakness... so if you can elaborate how I might check the indexes/delete them/recreate them (I have not consciously created or modified any indexes).
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When I set "All" it's using the customer table which has a primary key defined.
I'm not sure if you mean the link or the SQL Table, but it's possible for a linked item to NOT include a Primary Key, even though one is defined on the source table. It's happened to me on more than one occasion. To check, open the linked view in design view (Access will complain, but will open it) and check to insure Access is reporting a Primary Key. If not, you can remove and recreate the link.

I can add a record to it, then by extension, there cannot be any nulls in bit fields.
You're provided filtered views of the data in your views, so it's conceivable that ViewA will work (since the filtered data would not have any NULLS) while ViewB would not.

That said - do you have the Customer table linked to Jobboss? If so, and if you can add a record to that table, then I'd assume all is well with that. If your View is linked, then try to add a record to that.

You can open the linked table in "Design" view - Access will complain, but will let you do it - and you can created indexes there just as you would on a local table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
So I consider this VERY VERY weird... but maybe you can explain (and thanks for your patience).

The views were all created using the SQL commands I posted int the question. So why would InactiveCustomers and ProspectCustomers views HAVE a key while ActiveCustomers doesn't?  It's the same fields off the same tables?  

More importantly, how to I create the view and ENSURE the ActiveCustomers (as well as the other views) get primary keys?

(To be clear, when I open the linked tables in Access, there's a "key" next to the Cust_ID column in Customers, InactiveCustomers, and ProspectCustomers, but NO KEY next to the same field in ActiveCustomers and I assume THAT is my problem...)
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
The answer to my followup question was that I had to pay more attention when linking the table.  I deleted it and re-linked it and upon relinking I'm asked for the unique ID field...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think it's a bug in the relinking process Access uses. It's happened to me for years, and it's very sporadic. Some apps I manage never have this problem, where others seem to consistently fail to recreate PKs and/or indexes. What's even more odd to me is that the same tables are not always the ones that fail to create the PK ...

More importantly, how to I create the view and ENSURE the ActiveCustomers (as well as the other views) get primary keys?
If you're creating them manually, then you can just open the table in design view to insure the PK has been created, along with any other important indexes.

If you're doing this through code, you can always check the properties of the Fields via DAO, and make sure your intended field has been setup as the PK. Here's a link to as MSDN article that shows how to use the INdex.Primary property for a TableDef:

https://msdn.microsoft.com/en-us/library/office/Ff197416.aspx?f=255&MSPPError=-2147217396

Essentially, after creating your links you would loop through the TableDef collection. For each TableDef, you'd look through the Indexes and check to be sure your intended Column has been setup with the Primary attribute. In general, if I'm doing this by code, I'd store all the relevant information in a table that sits in the FE - for example, TableName, SourceTable, PKField, etc. I also store an SQL string that can be used to create Indexes on the table, using the CreateIndex method. See this article for more info on working with indexes via SQL in Access:

https://support.office.com/en-za/article/Create-or-modify-tables-or-indexes-by-using-a-data-definition-query-1e8c9219-ba45-4550-9164-f33f795cc140?ui=en-US&rs=en-ZA&ad=ZA
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.