Field cannot be updated

[BillA]

Using existing data derived from excel, I've built an access db with the
following tables:
client
projects
comments
Each table is linked with an "intermediary" table, with the Project table in
the middle. This has given me a ‘many-to-many’ relationship to each folder.
The ‘link’ tables contain a PK [auto#], and a field for each of the linked
tables primary-keys (which are auto# fields within their tables).

For example the table linking the Client and Projects table contain the
following fields:

LinkClientProjectID [ Auto number – PK ]
ClientID [from Client table]
ProjectID [from Project table]

From the very beginning of the venture I’ve had difficulty building forms
that allow updating to the underlying tables. I’ve tried sub forms and
although the data will display as designed… I have yet to get it to allow me
to ADD new data to any table using the form. My first form using the Client
table [master form], and Project(s) table [sub-form] resulted in an error:
“Field cannot be updated.†I have checked the data locks; no locks.

I have spent a lot of time trying different approaches to get data from all
3 tables on a single form, which will allow data updating - without any
success. I understand Sub-Forms work only when the relationship is
one-to-many, which may be part of my problem using the intermediary table.

Any advice would be appreciated.

Bill

 

[Ken Sheridan]

Bill:

You are trying to squeeze too much into one form. The usual arrangement for
this sort of thing would be to have one form with a subform in it, plus
another independent form.

The form with the subform might be based on the Clients table and its
subform on the 'linking' table, i.e. the one which models the many-to-many
relationship between Clients and Projects. The subform would have just one
bound control, a combo box bound to the ProjectID column and a RowSource
which looks up a text column such as ProjectName from the Projects table. It
can also look up other fields, so lets say you want to see ProjectName and
ProjectDate on the subform the RowSource would be:

SELECT ProjectID, ProjectName, ProjectDate
FROM Projects
ORDER BY ProjectName;

The combo box's properties would be as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;4cm;2cm
ListWidth 6cm

Experiment with the dimensions for the ColumnWidths to get the best fit, but
the first one must be zero to hide the bound ProjectID column. The ListWidth
property should be the sum of the ColumnWidths. The values can be in inches
of course.

When a project is selected form the combo box'slist the ProjectName will
show in the control. To show the Project date add an unbound text box to the
control with a ControlSource of:

=cboProjectID.Colun(2)

where cboProjectID is the Name property of the combo box. The Column
property is zero-based so Column(2) is the third column, i.e. the
ProjectDate. You can have more columns in the combo box's RowSource and more
unbound controls in the subform of course, each referencing a different
column of the combo box.

To add a new project you can use the combo box's NotInList event procedure,
so that when a new name is typed into the combo box the code in the event
procedure opens a form to add the new project. Here's an example for a combo
box bound to a CityID column:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

You'll see that this passes the new City name to the frmCities form as its
OpenArgs property, so in the frmCities form's Open event procedure the
following code assigns the new city name as the default value of the City
control on the form with:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Ken Sheridan
Stafford, England

Using existing data derived from excel, I've built an access db with the
following tables:
client
projects
comments
Each table is linked with an "intermediary" table, with the Project table in
the middle. This has given me a ‘many-to-many’ relationship to each folder.
The ‘link’ tables contain a PK [auto#], and a field for each of the linked
tables primary-keys (which are auto# fields within their tables).

For example the table linking the Client and Projects table contain the
following fields:

LinkClientProjectID [ Auto number – PK ]
ClientID [from Client table]
ProjectID [from Project table]

From the very beginning of the venture I’ve had difficulty building forms
that allow updating to the underlying tables. I’ve tried sub forms and
although the data will display as designed… I have yet to get it to allow me
to ADD new data to any table using the form. My first form using the Client
table [master form], and Project(s) table [sub-form] resulted in an error:
“Field cannot be updated.†I have checked the data locks; no locks.

I have spent a lot of time trying different approaches to get data from all
3 tables on a single form, which will allow data updating - without any
success. I understand Sub-Forms work only when the relationship is
one-to-many, which may be part of my problem using the intermediary table.

Any advice would be appreciated.

Bill

 

[BillA]

Ken:
Thank you for your advice and time. I will give it a try.

Thanks again,
Bill

Bill:

You are trying to squeeze too much into one form. The usual arrangement for
this sort of thing would be to have one form with a subform in it, plus
another independent form.

The form with the subform might be based on the Clients table and its
subform on the 'linking' table, i.e. the one which models the many-to-many
relationship between Clients and Projects. The subform would have just one
bound control, a combo box bound to the ProjectID column and a RowSource
which looks up a text column such as ProjectName from the Projects table. It
can also look up other fields, so lets say you want to see ProjectName and
ProjectDate on the subform the RowSource would be:

SELECT ProjectID, ProjectName, ProjectDate
FROM Projects
ORDER BY ProjectName;

The combo box's properties would be as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;4cm;2cm
ListWidth 6cm

Experiment with the dimensions for the ColumnWidths to get the best fit, but
the first one must be zero to hide the bound ProjectID column. The ListWidth
property should be the sum of the ColumnWidths. The values can be in inches
of course.

When a project is selected form the combo box'slist the ProjectName will
show in the control. To show the Project date add an unbound text box to the
control with a ControlSource of:

=cboProjectID.Colun(2)

where cboProjectID is the Name property of the combo box. The Column
property is zero-based so Column(2) is the third column, i.e. the
ProjectDate. You can have more columns in the combo box's RowSource and more
unbound controls in the subform of course, each referencing a different
column of the combo box.

To add a new project you can use the combo box's NotInList event procedure,
so that when a new name is typed into the combo box the code in the event
procedure opens a form to add the new project. Here's an example for a combo
box bound to a CityID column:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

You'll see that this passes the new City name to the frmCities form as its
OpenArgs property, so in the frmCities form's Open event procedure the
following code assigns the new city name as the default value of the City
control on the form with:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Ken Sheridan
Stafford, England

Using existing data derived from excel, I've built an access db with the
following tables:
client
projects
comments
Each table is linked with an "intermediary" table, with the Project table in
the middle. This has given me a ‘many-to-many’ relationship to each folder.
The ‘link’ tables contain a PK [auto#], and a field for each of the linked
tables primary-keys (which are auto# fields within their tables).

For example the table linking the Client and Projects table contain the
following fields:

LinkClientProjectID [ Auto number – PK ]
ClientID [from Client table]
ProjectID [from Project table]

From the very beginning of the venture I’ve had difficulty building forms
that allow updating to the underlying tables. I’ve tried sub forms and
although the data will display as designed… I have yet to get it to allow me
to ADD new data to any table using the form. My first form using the Client
table [master form], and Project(s) table [sub-form] resulted in an error:
“Field cannot be updated.†I have checked the data locks; no locks.

I have spent a lot of time trying different approaches to get data from all
3 tables on a single form, which will allow data updating - without any
success. I understand Sub-Forms work only when the relationship is
one-to-many, which may be part of my problem using the intermediary table.

Any advice would be appreciated.

Bill