Newbie Q: Can't update data in underlying table.

G

Guest

I have been looking everywhere to track down the answer to something that I
suspect is simple but it has eluded me!!!

I am using an Access 2003 ADP project with an MDSE backend. I have just
recently used the wizard to change from and .mdb to an .adp. I have an
unbound form that I use in single form view to display details pieces of
artwork in a collection. I use ADO to bind the recordset after the user
selects a method of filtering the records. Once the filtering method is
selected, a sub is called:

Sub Set_RecordSource(strServerFilter As String)
' Makes sure detail is visible and subform properly linked as connects
RecordSource

If Me.Detail.Visible = False Then
Me.Detail.Visible = True
Me.NavigationButtons = True
blnConnectSub = True
End If

Me.ServerFilter = strServerFilter
Me.RecordSource = "Select * From vwCollectionNoPic"
Me.RecordsetType = adOpenDynamic

If blnConnectSub Then
Me.sub_frm_artist.LinkChildFields = "artist id"
Me.sub_frm_artist.LinkMasterFields = "artist id"
End If

End Sub

vwCollectionNoPic is a view that is based on one table, no joins. Under
form properties, the unique table is set to "Collection" and the resync is
"Select * From vwCollecionNoPic Where artist_id = ?" The form has two
subforms on it. One displays information about the artist from the artist
table. The other uses code to populate a recordset with a server filter.
The recordset simply contains the collection's primary key field,
"catalogue_number" and a picture field. I structured it this way so that
only one picture is loaded at a time.

strSQL = "SELECT [Villanova Catalogue Number],picture FROM vwPicOnly
WHERE " _
& "[villanova catalogue number] = '" & [Villanova Catalogue Number]
& "';"
recPic.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Set frmSub = Forms!frm_main_collection!sub_frm_pic.Form
If Not IsNull(recPic("Picture")) Then
Me.sub_frm_pic.Enabled = True
strServerFilter = "[villanova catalogue number] = '" &
Me.Villanova_Catalogue_Number & "'"
frmSub.ServerFilter = strServerFilter
frmSub.RecordSource = "Select * From vwPicOnly"
Else
frmSub.RecordSource = ""
Me.sub_frm_pic.Enabled = False
End If

If anyone needs any additional information to help me solve this, I'm happy
to provide it.

Thanks in advance!
Andrea
This all seems to work fine except that the data that populates the form is
read only. When you try to edit the data you get "Field 'fieldname' is based
on an expression and can't be edited."
 
S

Sylvain Lafontaine

The most often cause for a read-only query is a missing primary key. Make
sure that the primary key has been set correctly by the upsizing wizard when
you have upsized your database. The second cause are insufficient security
permissions.

You are using Views, I cannot tell you anything about this because I don't
use them myself (I only use parameterized stored procedures) but make sure
that you don't have created them with the option With VIEW_METADATA;
otherwise you have to create an INSTEAD OF trigger to make them updatable.

Also, I'm not sure about the exact meaning of your statement « I use ADO to
bind the recordset » and the fact that you are setting the RecordSource to
"Select * From vwCollectionNoPic" and the RecordsetType to adOpenDynamic
instead of adOpenKeyset. See http://support.microsoft.com/?kbid=281998 to
make sure that you making this thing right.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Andrea M said:
I have been looking everywhere to track down the answer to something that I
suspect is simple but it has eluded me!!!

I am using an Access 2003 ADP project with an MDSE backend. I have just
recently used the wizard to change from and .mdb to an .adp. I have an
unbound form that I use in single form view to display details pieces of
artwork in a collection. I use ADO to bind the recordset after the user
selects a method of filtering the records. Once the filtering method is
selected, a sub is called:

Sub Set_RecordSource(strServerFilter As String)
' Makes sure detail is visible and subform properly linked as connects
RecordSource

If Me.Detail.Visible = False Then
Me.Detail.Visible = True
Me.NavigationButtons = True
blnConnectSub = True
End If

Me.ServerFilter = strServerFilter
Me.RecordSource = "Select * From vwCollectionNoPic"
Me.RecordsetType = adOpenDynamic

If blnConnectSub Then
Me.sub_frm_artist.LinkChildFields = "artist id"
Me.sub_frm_artist.LinkMasterFields = "artist id"
End If

End Sub

vwCollectionNoPic is a view that is based on one table, no joins. Under
form properties, the unique table is set to "Collection" and the resync is
"Select * From vwCollecionNoPic Where artist_id = ?" The form has two
subforms on it. One displays information about the artist from the artist
table. The other uses code to populate a recordset with a server filter.
The recordset simply contains the collection's primary key field,
"catalogue_number" and a picture field. I structured it this way so that
only one picture is loaded at a time.

strSQL = "SELECT [Villanova Catalogue Number],picture FROM vwPicOnly
WHERE " _
& "[villanova catalogue number] = '" & [Villanova Catalogue Number]
& "';"
recPic.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Set frmSub = Forms!frm_main_collection!sub_frm_pic.Form
If Not IsNull(recPic("Picture")) Then
Me.sub_frm_pic.Enabled = True
strServerFilter = "[villanova catalogue number] = '" &
Me.Villanova_Catalogue_Number & "'"
frmSub.ServerFilter = strServerFilter
frmSub.RecordSource = "Select * From vwPicOnly"
Else
frmSub.RecordSource = ""
Me.sub_frm_pic.Enabled = False
End If

If anyone needs any additional information to help me solve this, I'm
happy
to provide it.

Thanks in advance!
Andrea
This all seems to work fine except that the data that populates the form
is
read only. When you try to edit the data you get "Field 'fieldname' is
based
on an expression and can't be edited."
Click to expand...
 
A

aaron.kempf

sylvain

why dont you use views?

i build a lot more views than normal db developers; since im an olap
guy.. and pretty much everything i do in olap uses views
 
G

Guest

Sylvain,

Thanks for your response and all of your ideas! The one that worked was the
code in the link that you included. It had me set a recordset variable to
the data source, customizing some of the properties, and then binding it to
the form.

Thanks so much for sending the link. Funny how you can search and seach
that MSDN and not pull up what you are looking for.

Well I'm off to my next problem,
Andrea
 
Top