Why is my text field's recordset not updateable? (1 Viewer)

S

Sampoline

Member
Local time
Today, 15:51
Joined
Oct 19, 2020
Messages
161
I have a main form (frmFileMetdata) that uses a query (qryFileMetadata) as it's recordsource. This query is simply two tables with a foreign key that matches the tables together.

In my form, I have a button (that is supposed to open a file using the UNC path of that file (from one of the two tables from that query above). My form is a split form so that I can click on any line (which will have the file and image path) and the button ('Open File') should open that file up. The button will also populate the time started text box field from when the user clicks the button.. the code reads as below:

Code: 
Private Sub btn_OpenImage()


    Dim errorMSG, errorTITLE As String

    If Dir(Forms!frmFileMetadata!txtUNCPath) = "" Then

        WarningImageNotFoundMsg = "The File was not found. Please check this file exists in the location provided manually:" & Chr(13) & Chr(13)

        WarningImageNotFoundMsg = WarningImageNotFoundMsg & Forms!frmFileMetadata!txtUNCPath
   
        MsgBox WarningImageNotFoundMsg

    Else
            If IsNull(Forms!frmFileMetadata!datFileDateTimeStarted) Then

            Forms!frmFileMetadata!datFileDateTimeStarted = Now()

Now if I didn't have the text field, the button works perfectly.. it opens up the selected file. But with the text field there, I receive a run-time error 3326: "The Recordset is not updateable"

What are some potential reason why I could be receiving this error? My PK's are on both tables in my query, FK's are fine, they query itself works when I run it. Still not able to understand why the recordset is not updating. Any suggestions?
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:51
Joined
May 21, 2018
Messages
8,527
they query itself works when I run it.
Click to expand...
You say it runs, but is it updateable? Can you post the sql anyways even though you already said it is a simple parent to child query on 2 tables. I assume the txtbox is bound to a field and not calculated?
 
S

Sampoline

Member
Local time
Today, 15:51
Joined
Oct 19, 2020
Messages
161
MajP said:
You say it runs, but is it updateable? Can you post the sql anyways even though you already said it is a simple parent to child query on 2 tables. I assume the txtbox is bound to a field and not calculated?
Click to expand...
That's a good question. I hadn't really considered that. Sorry I'm an inexperienced user to Access.

I tried to recreate everything, so I've changed my table and text field names btw.

Here is the SQL:

Code: 
SELECT tblCallNoFormula.CallID, tblCallNoFormula.FName, tblCallNoFormula.FPath, tblCallNoFormula.FDateCreated, tblCallNoFormula.FDateLastModified, tblContentListing.ContentID, tblContentListing.[PM NAME], tblContentListing.[CM NAME], tblContentListing.txt_Comments, tblContentListing.txt_Audit_User_Name, tblContentListing.txt_Audit_Computer_Name, tblContentListing.dat_Audit_Date_Time_Started, tblContentListing.dat_Audit_Date_Time_Finished, tblContentListing.b_Audit_Completed, tblContentListing.txt_Audit_Outcomes, tblContentListing.txt_Audit_Comments

FROM tblCallNoFormula INNER JOIN tblContentListing ON tblCallNoFormula.CallNoWithSequence = tblContentListing.[CALL NO];

I can see there is no updating going, how would I write that in? Thanks.
 
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:51
Joined
May 21, 2018
Messages
8,527
So both tables have a PK which is good, but you join from two keys that are not PKs. You can set Inconsistent updates and I believe you can also index a field in the join and that will also work. In the below image I did a join without a PK. I set Recordset type to inconsistent and I could update.

RecordsetType Property - Microsoft Support

support.microsoft.com support.microsoft.com
inconsistent.jpg

I went back and applying an index was not sufficient. I need to add an index and make it unique as well but jus on one side.
 
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:51
Joined
May 21, 2018
Messages
8,527
FYI, there are a few free sites that can format your SQL strings when posting
Code: 
SELECT tblcallnoformula.callid,
       tblcallnoformula.fname,
       tblcallnoformula.fpath,
       tblcallnoformula.fdatecreated,
       tblcallnoformula.fdatelastmodified,
       tblcontentlisting.contentid,
       tblcontentlisting.[pm name],
       tblcontentlisting.[cm name],
       tblcontentlisting.txt_comments,
       tblcontentlisting.txt_audit_user_name,
       tblcontentlisting.txt_audit_computer_name,
       tblcontentlisting.dat_audit_date_time_started,
       tblcontentlisting.dat_audit_date_time_finished,
       tblcontentlisting.b_audit_completed,
       tblcontentlisting.txt_audit_outcomes,
       tblcontentlisting.txt_audit_comments
FROM   tblcallnoformula
       INNER JOIN tblcontentlisting
               ON tblcallnoformula.callnowithsequence =
                  tblcontentlisting.[call no];
I use this

Instant SQL Formatter & Beautifier Online

Free online sql formatting tool, beautify sql code instantly for SQL Server, Oracle, DB2, MySQL, Sybase, Access and MDX
www.dpriver.com
 
S

Sampoline

Member
Local time
Today, 15:51
Joined
Oct 19, 2020
Messages
161
MajP said:
So both tables have a PK which is good, but you join from two keys that are not PKs. You can set Inconsistent updates and I believe you can also index a field in the join and that will also work. In the below image I did a join without a PK. I set Recordset type to inconsistent and I could update.

RecordsetType Property - Microsoft Support

support.microsoft.com support.microsoft.com
View attachment 88876
I went back and applying an index was not sufficient. I need to add an index and make it unique as well but jus on one side.
Click to expand...
MajP said:
FYI, there are a few free sites that can format your SQL strings when posting
Code: 
SELECT tblcallnoformula.callid,
       tblcallnoformula.fname,
       tblcallnoformula.fpath,
       tblcallnoformula.fdatecreated,
       tblcallnoformula.fdatelastmodified,
       tblcontentlisting.contentid,
       tblcontentlisting.[pm name],
       tblcontentlisting.[cm name],
       tblcontentlisting.txt_comments,
       tblcontentlisting.txt_audit_user_name,
       tblcontentlisting.txt_audit_computer_name,
       tblcontentlisting.dat_audit_date_time_started,
       tblcontentlisting.dat_audit_date_time_finished,
       tblcontentlisting.b_audit_completed,
       tblcontentlisting.txt_audit_outcomes,
       tblcontentlisting.txt_audit_comments
FROM   tblcallnoformula
       INNER JOIN tblcontentlisting
               ON tblcallnoformula.callnowithsequence =
                  tblcontentlisting.[call no];
I use this

Instant SQL Formatter & Beautifier Online

Free online sql formatting tool, beautify sql code instantly for SQL Server, Oracle, DB2, MySQL, Sybase, Access and MDX
www.dpriver.com
Click to expand...
Hi Maj,

Thanks for the SQL format strings tip! I'll use that in the future.

And in regards to your solution, so I tried using Inconsistent Updates but all that did was remove some fields from my query when I ran it and even then it was still giving me a recordset is not updateable error. And sorry I didn't pick up on the unique index you made? Are mine not correct or were you suggesting something else? Thanks.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:51
Joined
May 21, 2018
Messages
8,527
You are joining on these two fields
callnowithsequence
tblcontentlisting.[call no];

Can you go into the table view for these tables and index these two fields? In either table is the number not repeated (unique)? If you can make that a unique index and should be updateable. It may not be so.

In my example RangeStart is not a PK but I indexed it and it is unique. This allowed the join to be updateable
index2.jpg
 
S

Sampoline

Member
Local time
Today, 15:51
Joined
Oct 19, 2020
Messages
161
MajP said:
You are joining on these two fields
callnowithsequence
tblcontentlisting.[call no];

Can you go into the table view for these tables and index these two fields? In either table is the number not repeated (unique)? If you can make that a unique index and should be updateable. It may not be so.

In my example RangeStart is not a PK but I indexed it and it is unique. This allowed the join to be updateable
View attachment 88882
Click to expand...
So in saying this, if I have data that are duplicates in the fields:
callnowithsequence
tblcontentlisting.[call no];

Then I will receive an error that recordset is not updateable?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:51
Joined
May 7, 2009
Messages
19,233
use Left Join instead of Inner Join.
 
S

Sampoline

Member
Local time
Today, 15:51
Joined
Oct 19, 2020
Messages
161
arnelgp said:
use Left Join instead of Inner Join.
Click to expand...
Hi Arnell, I tried this but I still receive the recordset not updateable error. Like I mentioned above, if there are duplicates in my data for either FK fields then will this cause this issue?
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:51
Joined
Feb 28, 2001
Messages
27,172
Here is a list of reasons why a given recordset would not be updatable:

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.
allenbrowne.com

Allen Browne's articles are usually pure gold and this one is no exception.
 
You must log in or register to reply here.

Similar threads

raziel3
Solved How to Export Current Recordset and remove certain fields to Excel (Field/Column Filtering)
2
Replies
35
Views
1,473
theDBguy
theDBguy
K
Saveas method with workbook
Replies
4
Views
141
Gasman
Gasman
M
Solved Database not updating from Citrix ...
Replies
18
Views
552
Marshall Brooks
M
S
VBA Code for Bold Text & Hyperlink
Replies
19
Views
1,190
cheekybuddha
cheekybuddha
T
Using multiple criteria in DCOUNT function using a comparison
Replies
1
Views
140
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom