Hello Wendell. Thank you for your response.
Sorry in advance for the lengthy reply. I don't even know that there's a clue in here...
You are correct that the field in question is not a bit field. The column in the base table is an integer with declarative referential integrity (in SQL Server) to the look-table.
You are correct that by "updateable query” I do mean a SQL server view. There is one detail about primary key index to clarify though. MS Access doesn't recognize it automatically from the view (don't know why that is). In order for the linked table in MS Access to be updateable I needed to define a primary key index in MS Access. Here's my index definition.
CurrentDb.Execute "CREATE UNIQUE INDEX AppTracker_Milestone_Dates_Edit_UIX " _
& "ON AppTracker_Milestone_Dates_Edit (DB_Key) " _
& "WITH DISALLOW NULL;"
This is the same index as is defined on the base table in SQL Server.
Here is the underlying view;
/****** Object: View [dbo].[AppTracker_Milestone_Dates_Edit] Script Date: 4/19/2019 12:47:37 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
This view was created to support the functionality of the AppTracker solution.
This view takes no parameters and can be executed as follows;
SELECT <columns> FROM [dbo].[AppTracker_Milestone_Dates_Edit] WHERE <Conditions>
The intent of this view is to provide a editable list of Milestone_Dates records
(Applications) for use in the AppTRacker solution. The functionality of the AppTracker
is such that it will retrieve only one record at a time if the user is desiring
to update it or to display a blank data entry form if the user is desiring to add
a new record.
Note that although [RowVersion] and [Corporate_GUID] are not required for the user interface
they must be included in the view in order for the view to be updateable in
Commented fields from the underlying table are not presently used but are left
in the code in case they are needed in the future.
Columns additional to the application details are included to enable
visual cues in the AppTracker in the case of editing records e.g.
- Expired look-up values
- record counts for "child records" in the subforms
Rev 0 Jan 09 2019 Michael Lock Initial build
ALTER VIEW [dbo].[AppTracker_Milestone_Dates_Edit] AS
,PJ.[Obsolete_Date] AS [Project_Name_Obsolete_Date]
,RS.[Obsolete_Date] AS [Sector_Obsolete_Date]
,DI.[Obsolete_Date] AS [Directorate_Obsolete_Date]
,TM.[Obsolete_Date] AS [Team_Obsolete_Date]
,GR.[Obsolete_Date] AS [Group_Obsolete_Date]
,CA.[Obsolete_Date] AS [Category_Obsolete_Date]
,TY.[Obsolete_Date] AS [Type_Obsolete_Date]
,PU.[Obsolete_Date] AS [Purpose_Obsolete_Date]
,RD.[Obsolete_Date] AS [Application_Decision_Obsolete_Date]
,ST.[Obsolete_Date] AS [Audit_State_Obsolete_Date]
,AR.[Obsolete_Date] AS [Audit_Result_Obsolete_Date]
,OS.[Obsolete_Date] AS [OS_Area_Obsolete_Date]
,CS.[Obsolete_Date] AS [Conf_State_Obsolete_Date]
,RC.[Obsolete_Date] AS [Rate_Control_Type_Obsolete_Date]
,WW.[Obsolete_Date] AS [Well_Type_Waiver_Obsolete_Date]
,(SELECT COUNT(*) FROM [dbo].[Application_Location] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [Location_Count]
,(SELECT COUNT(*) FROM [dbo].[Information_Request] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [Information_Request_Count]
,(SELECT COUNT(*) FROM [dbo].[Comment] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [Comment_Count]
,(SELECT COUNT(*) FROM [dbo].[SOC_Link] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [SOC_Count]
FROM [dbo].[Milestone_Dates] MD
LEFT JOIN [dbo].[R_Project_Name] PJ
ON PJ.[DB_Key] = MD.[Project_Name_DB_Key]
JOIN [dbo].[R_Regulatory_Sector] RS
ON RS.[DB_Key] = MD.[Regulatory_Sector_DB_Key]
JOIN [dbo].[R_Application_Directorate] DI
ON DI.[DB_Key] = MD.[Directorate_DB_Key]
JOIN [dbo].[R_Application_Team] TM
ON TM.[DB_Key] = MD.[Team_DB_Key]
JOIN [dbo].[R_Application_Group] GR
ON GR.[DB_Key] = MD.[Group_DB_Key]
JOIN [dbo].[R_Application_Category] CA
ON CA.[DB_Key] = MD.[Category_DB_Key]
JOIN [dbo].[R_Application_Type] TY
ON TY.[DB_Key] = MD.[Type_DB_Key]
JOIN [dbo].[R_Application_Purpose] PU
ON PU.[DB_Key] = MD.[Purpose_DB_Key]
LEFT JOIN [dbo].[R_Application_Decision] RD
ON RD.[DB_Key] = MD.[Application_Decision_DB_Key]
LEFT JOIN [dbo].[R_Audit_State] ST
ON ST.[DB_Key] = MD.[Audit_State_DB_Key]
LEFT JOIN [dbo].[R_Audit_Result] AR
ON AR.[DB_Key] = MD.[Audit_Result_DB_Key]
LEFT JOIN [dbo].[R_OS_Area] OS
ON OS.[DB_Key] = MD.[OS_Area_DB_Key]
LEFT JOIN [dbo].[R_Conf_State] CS
ON CS.[DB_Key] = MD.[Conf_State_DB_Key]
LEFT JOIN [dbo].[R_Max_Prod_Rate_Kind] RC
ON RC.[DB_Key] = MD.[Max_Prod_Rate_Kind_DB_Key]
LEFT JOIN [dbo].[R_Well_Type_Waiver] WW
ON WW.[DB_Key] = MD.[Well_Type_Waiver_DB_Key]
Here's a screenshot of the data entry form (fields bordered in red are required fields)
For the sake of completeness here's a screenshot of the view/search form
The view form is designed so that by default, when it opens, it retrieves undecided records that the user has worked on. The User Name and and State fields enable them to view records others have worked on and also decided records.
A user can either edit an existing record or add a new one. With either choice, the same edit form is displayed, either in edit or add mode. Here are the edit and add subroutines. Here they are
Private Sub btnEdit_Click()
Dim RecordNumber As Long
Dim DBKey As Long
On Error GoTo btnEdit_Click_Err
'Remember the DB_Key of the record we're working on
'So we can return to it after calling the Refresh method
DBKey = Me.txtDBKey
'open the data entry form in edit mode, showing the currently selected record from the browse form
DoCmd.OpenForm "Application Edit - Template", acNormal, "", "[DB_Key]=" & txtDBKey, acEdit, acDialog
'Set the boolBypassFormCurrent variable so we can bypass the redundant
'calls to Form_Current() which occur when the Refesh method is
'called since we're going to immediately return to the record
'the user was editing, which will also call Form_Current()
boolBypassFormCurrent = True
'Requery the databaset to get the changes that were made by the
'user and then return to the record that the user was looking at.
boolBypassFormCurrent = False
Me.Recordset.FindFirst "[DB_Key] = " & DBKey
'If the sort order chosen by the user results in the edited record
'being row 1 then the Me.Recordset.FindFirst call does not trigger
'the final Form_Current event so we need to call the SetForm subroutine
If Me.CurrentRecord = 1 Then SetForm 'Note: SetForm is a subroutine that sets various visual
'cues on the form e.g. record counts in subforms
As a side note, in the btnEdit_Click() routine I've had to resort to saving the DB_Key of the records being edited and then using Me.Requery and then navigating back to the record being edited because, for reasons I do not understand, Me.Refresh does not actually refresh the record with the edits the user made on the edit form.
Private Sub btnAdd_Click()
On Error GoTo btnAdd_Click_Err
'Open the data entry form in add record mode
DoCmd.OpenForm "Application Edit - Template", acNormal, "", "", acAdd, acDialog
When a user is adding a new record I use the Form_Update subroutine to save the record and update the view form. It looks like this;
Private Sub Form_AfterUpdate()
Dim rst As DAO.Recordset
'Refresh the record to get the latest data that has been saved
'note that this is primarily to refresh the record details
'i.e. last updated by and Last update date time.
'If it's a new record...
If boolNewRecord Then
'Refresh the browse form so that the newly saved record is in the recordset
boolBypassFormCurrent = True
[Forms]![Application Browse - Template].Requery
'Navigate the browse form to the newly saved record
Set rst = Forms("Application Browse - Template").RecordsetClone
.FindFirst "[DB_Key] = " & Me.txtDBKey
If Not .NoMatch Then
Forms("Application Browse - Template").Bookmark = .Bookmark
Set rst = Nothing
boolNewRecord = False
boolBypassFormCurrent = False
btnSaveClose.Enabled = False
btnCancel.Caption = "Close"
So, that's pretty much it. I'm sure all of this would be much clearer if I could package up the SQL DB and the Access DB but the SQL DB has dependencies on other DB's that I'd have to resolve... It would be a lot of work.
As far as the user's requirements It's really only to either choose a record from the view form, edit it in the edit dialog, close the dialog and return to the edited record in the view form OR add a new record, close the dialog and return to the newly added record in the view form.
Again, thanks in advance for any assistance you can provide.
p.s. My fallback solution would be to prevent a user from clearing the project name field once it had been set when adding a new record and displaying a message indicating that it can be cleared after the record is saved. A bit of a hack but it would work.