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
(view; default), InactiveCustomers
(view), and Prospects
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
Form.RecordSource = "ActiveCustomers"
Form.RecordSource = "InactiveCustomers"
Form.RecordSource = "ProspectCustomers"
Form.RecordSource = "Customer"
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'
CREATE VIEW InactiveCustomers AS SELECT * FROM Customer WHERE Deleted = 0 AND RecordType = 'I'
CREATE VIEW ProspectCustomers AS SELECT * FROM Customer WHERE Deleted = 0 AND RecordType = 'P'
I have added pictures of the macro and the Properties Data tab on the form as well for reference.
Can someone tell me how I can get this to allow the creation of a new record when "ActiveCustomers" is selected?