Can't Edit Data In A Table In ADP
Aug 2, 2006
I've created an ADP project containingnumerous tables. One (and only one!) table in the set will not open in edit mode while in Access. Here are the particulars:
1) The table was created from scratch in SQL Enterprise Manager,
2) The table is editable in SQL Enterprise Manager,
3) The table permissions appear to be set correctly (and are the same as all the other tables which all allow editting in Access)
4) I open the Access project and double-click the table. The "add record" navigation button is disabled, and any attempt to edit a field elicits a "This recordset is not updatalbe" error.
5) However, I can open the table in Design mode in Access, make changes, and save them.
Any ideas? This is very frustrating!
Mark
View Replies
ADVERTISEMENT
Jan 24, 2008
hi, im amaturish at access, but im wondering what would be the easiest way to create a edit page, via any means. eg. in a form?
cheers
View 6 Replies
View Related
Aug 29, 2006
Hi
I have a form, which displays data from a query recordsource.
One of the textboxes is a "Comments" textbox, which I would like the user to write in directly, which in turn is a direct link to the data in the table so it is updated immediately.
At the moment, the textbox does not seem to be editable. Is there a standard way to do something like this ?
View 10 Replies
View Related
Feb 21, 2013
I have a commitment table in my database, and normally people will input commitments through a form.
However each month I will have a flat file upload from excel directly into the Commitment Table, these will be up to 1500 lines. (they are downloads from other systems, that we want to have in our database and i can't link directly to these systems).
I created a macro to do this, and attached it to a button and tested it and it works fine.
My issue is this, once I've uploaded it once, next month what I would like to happen is when I upload it again that it changes the original commitment should any of the details be different or it inserts a new commitment if it didn't already exist. (After I have uploaded the original data, I can download it to get the commitment ID to attach to the excel files if necessary.)
View 7 Replies
View Related
Aug 14, 2012
I have a Union query that is linked into about 10 different excel spreadsheets on our drive and it pulls back only 3 columns of data (Document Number, Date Registered and Date Implemented). I then have another report which refers to the results of this query and does its own thing from thereon.
Here is a sample of the SQL:
SELECT CINT(RIGHT(RT_01000.[Rev-Trac Number],5)) as [Document Number], RT_01000.[CC_RT Issue Date] as [Date Registered], RT_01000.[Actual Delivery Date] as [Date Implemented] FROM RT_01000;
UNION SELECT CINT(RIGHT(CC_RT_01000.[Rev-Trac Number],5)) as [Document Number], CC_RT_01000.[CC_RT Issue Date] as [Date Registered], CC_RT_01000.[Actual Delivery Date] as [Date Implemented] FROM CC_RT_01000;
My Issue:
As it is above, the query will run, but we end up with duplicate numbers in the "Document Number". It only brings back the final 5 digits from each of these files and ignores the differenciating part of the number, the "CC_RT_" or the "RT_". This causes huge issues in the file which then goes onto use these results afterwards.
My Plan: (failed)
I thought I could simply update the "5" to an "11" to catch anything named "RT_00001" and also "CC_RT_00001" serperately so I can clearly see which is which without any duplicates. However, I recieve the below error when doing so:
"Data type mismatch in criteria expression".
I've been playing with this for hours and unfortunately many many reports reply on these results being correct. It is also not possible to change the names of the RT and CC_RT files that it links into.
View 2 Replies
View Related
Mar 28, 2006
To make a long story short I have a command button that runs a query and opens a subform based on that query. I want to be able to update the records that are returned (via the subform) but I am getting an error - "This Recordset is not updateable". I've tried changing the recordset property to Dynaset (Inconsistent Updates) but that didn't seem to change anything.
Anyone point me in the right direction?
View 1 Replies
View Related
Aug 7, 2006
I've created a form (callled Contact) that contains a subform (called Sales). The Sales subform is a datasheet that displays sales for each contact and changes when the record in the Contact form changes. All works fine.
My problem is that I want to display some big text fields from the Sales subform in the Contact form so that I can view them more easily. So I've cut and pasted the fields from the Sales form onto the Conact form and tried to change their Control Source. But nothing I do works.
What should happen is that when a record in the Sales subform changes, the fields that have been cut & pasted onto the Contact form should also change. They do change, but I can't edit the text in them. What have I done wrong?
View 2 Replies
View Related
Jun 29, 2006
Hi guys,
I've got a table (called table1), which a select query (called qryTable1) read it and is referenced in a form (called frmTable1).
So now my form display fields of the query of the table.
On the form, below the text boxs for data entry, there is a subform that displays the entire table - table1.
I've done this so I can see some fields of the old data, that helps me to enter new ones. Hope u follow so far...
---
This where I'm stuck. When I was half way entering data in the top section of the form (ie using text boxes), if I go to the subform below to copy some values from a field, ACCESS then either adds a new or edit the record. I'm wondering if there is a way to stop ACCESS automatically doing this? I want to be able to add a record when THE add record button is depressed, and that's the only way to add record.
Please do help..
View 1 Replies
View Related
May 30, 2013
I have a table structure that I guess could be called a dual one-to-many-to-one, as can be seen in the attached Relationships screen print. Customer loan files are maintained in an AllFiles table, tied to the Customers and Loans tables through the CustomerFiles and LoanFiles tables. Users access the data from an unbound form called FileProcessing, where they can select a customer, and a loan for that customer. The files linked to that customer and loan then appear on a form called Files. However, I need the data on Files to be updateable, and I'm having a hard time coming up with a Files datasource that can be updated. The SQL that retrieves the needed data is:
Code:
SELECT AllFiles.*
FROM (AllFiles INNER JOIN CustomerFiles ON AllFiles.FileNum = CustomerFiles.FileNum)
INNER JOIN LoanFiles ON AllFiles.FileNum = LoanFiles.FileNum
WHERE LoanFiles.LoanNum=[Forms]![FileProcessing]![tbLoanNum]
AND CustomerFiles.CIFKey=[Forms]![FileProcessing]![tbLoanCIFKey];
I tried creating another query that just had the AllFiles table linked to the above query, but the data still couldn't be updated.
View 2 Replies
View Related
Apr 23, 2013
I have a simple data base with 2 tables. One table is the pertinent Employee data. That includes a EmployeeID that I contrived myself with the first 4 letters of the FIRST NAME and the first 3 letters of the LAST NAME.
how to make the program do that automatically when I enter the employee's first and last name.I have a form that sits overtop this table to populate the data. It's got some test date in it now c/w hand and manually calculated EmployeeID.
So now I figured out how to make the program do that calculation automatically. So why when I cruse over the records and tab through the fields and the form shows the new EmployeeID, it doesn't update the table for that field?
View 6 Replies
View Related
Jun 21, 2005
Hi all
I am putting together a quick and dirty db to hold just a few thousand bits of data for some tests I’m about to start.
For various reasons it is better to hold some of these in Excel, and to link Access to Excel.
The problem I have is that when I use Access to edit a “record” in Excel, it will work for the first one or two records, but then Access crashes and I get the very familiar “Sorry for the inconvenience, please send this error file to uSoft blah blah blah” (which I always do, but wonder if anyone ever takes any notice of them!).
The problem occurs whether the spreadsheet is open in the background, or is just sitting closed in its directory with Excel not running.
At the moment, I only have a tiny amount of data as I’m still putting it together, so it’s not a “size” issue; I first noticed the problem with only about ten records!
I expect I’ll move everything to Access (not ideal but hopefully it wont crash quite so often), but I’m curious to know if any of you have actually made a successful link between Access and Excel (and used it for editing data, not just looking, which is fine).
My system: Office 2003 pro/developer, Win XP pro.
Thanks for any thoughts
Skeletal
View 4 Replies
View Related
Jul 14, 2005
Still not sure how to do this, after I convert my database and create an mde file few changes might accrue in the future, I have tried to make some changes for testing purpose and then create a new mde file and saved it with replacing the older one but I had lost my new data which was entered in the old mde file?
Q: how can I make the new change on my original file and not lose my new data in my mde file.
View 3 Replies
View Related
Apr 27, 2005
I have a subform where records can be added or edited. There are two fields: firstname and lastname. I want a message box to appear when a first or last name is edited.
I do not want that message box to appear when a new name is being added. I use this code:
[CODE]....
Now I want to also prevent the user from leaving a record before both fields have data (first and last name are required). My efforts to do this seem to conflict with my message box issues.
Can anyone see what I'm doing wrong or how to do this?
View 14 Replies
View Related
Aug 5, 2007
I had Access '97 and was able to edit my table and make changes, etc.
I upgraded to Access 2000 and now I cannot edit my table and make changes, etc. The table is not locked.
Why?:confused:
View 1 Replies
View Related
Jan 12, 2006
Hello,
I have a form that utilizes a combo box called cboProgAddr (for Program Address). The cbox has 5 columns each of which fills in an adjacent text box. Since I needed the option of making corrections to these text boxes once they were filled in I need to set them up as unbound via the following code in the After Update event of cboProgAddr:
Private Sub cboProgAddr_AfterUpdate()
Me!txtProgStreet.Value = Me!cboProgAddr.Column(1)
Me!txtLocStreet.Value = Me!cboProgAddr.Column(1)
Me!txtProgCitySt.Value = Me!cboProgAddr.Column(2)
Me!txtLocCitySt.Value = Me!cboProgAddr.Column(2)
Me!txtProgZip.Value = Me!cboProgAddr.Column(3)
Me!txtLocZip.Value = Me!cboProgAddr.Column(3)
End Sub
As you can see it puts each field of data into two separate text boxes. Here's the issue: If I have to go back to the form after the original data entry the following boxes are blank...
txtProgStreet
txtProgCitySt
txtProgZip
while the following retain the orginal data...
txtLocStreet
txtLocCitySt
txtLocProgZip
The original cboProgAddr maintains its original data. All six fields above are stored in the same table as text field. I'd like all the boxes to maintain the data from the original input.
I'm totally baffled why this is so. I've attached images of the combo box control functions if that helps.
Thanks,
David
View 1 Replies
View Related
Mar 14, 2013
I have a form which saves to a database correctly. It has a key field name called "code".
I can recall the data into a copy of the first form to edit by using the key field "code".
All works well, however, when I click save data from the edit form it does not alter the original data but creates another form but with the same key field called code.
View 1 Replies
View Related
Apr 26, 2013
When editing a record in Access 2003, the status bar would show the value of each field's description from the table design grid. Is there a way to get Access 2010 to do the same?
View 1 Replies
View Related
Mar 12, 2007
Hi, I have a form called Edit Column, which contains two text boxes: ID and Description. The user types something in these two boxes and presses a button, and this button should take the value of ID and edit description for that particular ID in the table "Map". Here is the code I have written for it:
Dim d As Database
Set d = CurrentDb
Dim r As Recordset
Set r = d.OpenRecordset("Map")
r.MoveFirst
If r! = Me.Text1 Then [I]('Text1 is the text box where the user enters an ID)
Do While Not r.EOF
r.Edit
r![Description] = Me.Text3
r.Update
r.MoveNext
Loop
MsgBox "Successful", vbInformation
Else
MsgBox "This column does not exist in the Store", vbCritical
End If
Thanx in Advance
View 1 Replies
View Related
Sep 26, 2005
Hi all,
I am trying to learn Access form building by creating a training database. I created a form that has all of my class information and the students who are attending the class as show in the attached jpg.
If a student has already attended a previous class I don't want to enter his information again. I want to do a lookup for the student and if the student does not exist then create a new student. Is there a way of doing this without coding?
Thanks Steve
View 2 Replies
View Related
Nov 9, 2004
I am trying to edit a specific record in my table. I want to know how to go about referencing it. I'm dealing with a table of books each having its own Book # assigned to it.
Example:
The table has Book#(primary key), title, author, quantity.
How would i go about increasing/decreasing the the quantity by a value(i.e. 1, 2, 3, etc.) in a form that i'm in and then saving it to that specific books quantity?
I'm attempting to keep track of our inventory.
View 12 Replies
View Related
Mar 28, 2015
i've got a Form that contains Subform with an embedded Query that contains 2 tables only (Payments & Invoices) the Join properties between them show all records from payments and what matches it from Invoices where the joined fields are equal (Invoice no).. So, when i enter certain data in the main form the Subform show the results for it from Payments table and only one field needed from the Invoices table !!
The problem is.. i can't edit or add any data in the Subform results with the previous setting, but when i completely remove the Invoices table from the embedded Query then swift to the Form and it's Subform.. i become able to edit and add data in the Subform easily... !!
So, how to enable the edit/add in the Subform with the 2 tables in the embedded Query ?! Cause it's really needed to show that field from the Invoices table.
View 3 Replies
View Related
Mar 19, 2013
I have the main form open up where the Data entry is set to YES so it opens in New Field. I created a button to change the value of form's Data Entry to NO. It works. Changed the form so I can navigate through entered fields. I have created a drop down to filter a specific field "Claim#" and it will open that entry.
The drop down only works if I set the default form's Data Entry to NO. Dropdown works and I can select Claim#. But if I open the form with DataEntry set to Yes, and I click on the button to change the value to NO, the form changes but the drop down field does not pull up the proper information. The button code is.
Private Sub EditButton_Click()
Form_MainForm.Form.DataEntry = False
End Sub
The Claim drop down has this event after update
Private Sub ClaimSearch_AfterUpdate()
Me.Requery
End Sub
I'm missing something. Like I said, it works without having the button change the value.
Access 2010
View 1 Replies
View Related
Dec 19, 2006
Short version of question:
My research has shown that Access will not allow edits through a form that 1) has subqueries in its SELECT clause, or 2) uses aggregation such as First(). Is there any tricky way around this?
Long version of question:
I have a database form that shows an overview of orders for products (it's based on a query that pulls all current orders from the big table). I would like to handle both of these on each row:
Show where we built it last time, and
Allow the user to select where it will be built this time.
The problem is that I cannot figure a way to change the form/query such that it doesn't aggregate to find out where we built it last time, or doesn't rely on subqueries that do just that.
But I feel like there should be some way to do it--since each row displayed does rely on one and only one record in the complete table of orders. Which obviously is the record I want to update.
Is there something I can do that will accomplish this? I really do not want to have to make the user open up another form to see last time or choose this time.
Thanks in advance!!
View 3 Replies
View Related
Sep 13, 2007
We are using access 97 and I have a linked table to sql 2000. A couple of weeks ago, our sql server crashed and we had to completely rebuild it / restore all of the data. Ever since then, only one of our linked sql tables in access is acting up. Whenever we go to edit a line (sometimes, not always), we get an error which states the following:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
Options are Save Record (greyed out), Copy to Clipboard, and Drop Changes.
About all I have tried is re-linking the table...any other ideas?
View 2 Replies
View Related
Jun 30, 2015
I have been developing a form that will allow me to input, edit and delete data in a table.I have the input, delete, clear, and close command buttons working ok, but I am having trouble getting the edit function to work.
What happens is that I highlight the line in the table (shown on the form as a sub form) click the edit command button, and the field are populated with the current data. I then edit the field that I want, and the add, button, changes to Update. When I go to update I keep getting an error message "Cannot update 'ID'; field not updatable.Below is the code I am using, when I go to the debug the arrow points to the "WHERE ID=" line.
Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtID.Tag & "" = "" Then
[code]...
View 14 Replies
View Related
Dec 30, 2013
I have a report that I run which has PT_Ins_ID in it, I am trying to edit that row with the following code.
Code:
Private Sub cmd_Deact_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("dbo_Patient_Insurance")
If rs.RecordCount <> 0 Then
With rs
[code]....
I've also tried this.
Code:
Dim rs_PI As DAO.Recordset
Set rs_PI = CurrentDb.OpenRecordset("dbo_Patient_Insurance")
[code]...
View 6 Replies
View Related