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)
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.
Can someone tell me how I can get this to allow the creation of a new record when "ActiveCustomers" is selected?
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 SubFor 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'
GOCan someone tell me how I can get this to allow the creation of a new record when "ActiveCustomers" is selected?
ASKER
-- 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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...)
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...)
ASKER
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...
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 ...
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
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
-- 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)