Tables :: Edit Values In Linked Table Error 3021
Jul 8, 2014
I have ODBC linked tables to a DB2 database.When I try to edit the values in some of the tables (open the table in dataview and edit one record) I get a 3021 Error -No current record.On other tables (linked the same way) I don't get this error
If I try the update by running a query on the same table, I get no errors.
The system I have is a german Access 2010 on german WinXP and a connection to DB2 LUW v9.7
Is there any set of settings necessary for ODBC to work with access and vice versa?
View Replies
ADVERTISEMENT
Nov 25, 2014
I have managed to sort out the data to be used in the final table. However, I am having trouble transferring the data from each of their own tables into the final table.
Each time I run my code I receive "run-time error 3021: No current record."
It seems that only my timestamp is being added properly, but the error pops up and highlights the first "rstInsert.Edit" of my code. I'm suspecting that my function is running too fast, such that it did not have time to read that the table has already been populated by the timestamp in the AddNew code
Code:
Private Sub Command9_Click()
Dim dbs As DAO.Database
Dim rstTimestamp As DAO.Recordset
Dim rstAcknowledgement As DAO.Recordset
Dim rstAgent As DAO.Recordset
[Code] ....
View 1 Replies
View Related
May 13, 2015
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
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
May 5, 2005
Hello,
I have search previous threads on this problem and not been able to solve it....so if there are any new ideas on this I would appreciate your help.
Firstly I open a new form "Document Details" from a subform which filters out a single record thus
Private Sub Command116_Click()
On Error GoTo Err_Command116_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Document Details"
stLinkCriteria = "[Fileandrev]=" & "'" & Me![Filename] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command116_Click:
Exit Sub
Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click
End Sub
If I then try and delete the record using the following
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
It of course...as many others have discovered goes into the error 3021
because there is no current record for the form to move to because it
is filtered from the above statement
DoCmd.OpenForm stDocName, , , stLinkCriteria
All I need to do is close the form after the error message and refresh the subform so it removes the deleted record which I know I can do by placing the commands after the error description....but it would be nice to know how
to do it properly rather than bodge it ..!!
Any ideas please
Thanks in anticipation :)
View 2 Replies
View Related
Oct 11, 2006
Hi, I'm learning Access on the fly for a temp job, and it's going pretty well, but I can't get past this problem:
I have a form for deleting a record, and it has a delete button with this code:
Private Sub Delete_Click()
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "frmDelete"
End Sub
The record IS deleted, but the form is not closed. Instead, I get this message:
Run-time error 3021
No current record
I think I understand, logically, why this is happening - the counter at the bottom of the form is still set on record #x after x has been deleted, and it stops the code. But I can't for the life of me figure out how to fix it.
Thanks for your help!
View 7 Replies
View Related
Apr 8, 2006
I have a code which now works perfectly on my subsubform. My navigation buttons are fine until I put code for showing the record number and total number of records on the form, subform and subsubform. When scrolling through records I get error 3021 suggesting no record found. There may not be a record on the subform yet but there are always records on the form I am trying to navigate.
Any Ideas how I can avoid this error or even get access to ignore it!!!
View 1 Replies
View Related
Jan 14, 2006
Hi
I am having a real hard time with access weared errors. I am using following code:
Me.HistorySymptoms.Form.Refresh 'Just to make sure. It was a try because of error
If Me.HistorySymptomTab.Visible = True Then 'It is a subform in a tab page
For counter = 0 To 29 'Number of fields in the recordset
If IsNull(rs.Fields(1 + counter).value) = False Then ' Just to check Null values
MsgBox rs.Fields(counter).Name 'For debugging
Old_History(counter) = rs.Fields(1 + counter).value 'This line has problem
End If
MsgBox rs.Fields(counter).Name & " Done.||" & counter
Next counter
End If
The error comes in the maked line on accessing the field value. Previously I had another error but after going through the process of "Corrupt Access file correction", I am getting this one. My form has subforms in tabpages. I want to save all the field values to the array on pressing a button. Button is part of the main form and code copies values from a subform. I don't think my file is corrupt as I went through the process twice :-(
Looking for help
RMA
View 2 Replies
View Related
Sep 18, 2014
I get this error when I delete more than one record consecutively.
Here is my delete code;
Private Sub Komut98_Click()
Dim blnLast As Boolean
'MsgBox call must return the value in order to be checked.
'If user says no then cancelling is not required. It is only required
' that the deletion command is not invoked.
[Code] ....
View 5 Replies
View Related
Jan 3, 2014
How to by pass error 3021 No current record
I know I can put the code below just don't know where to put it.
If Err.Description = "No current record" Then Resume
I attached a pic of my code that is causing the error .. and its okay I just want to by pass the message box.
View 4 Replies
View Related
Jun 16, 2014
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset
Then it uses the rs.Findfirst method
It checks if rs.Nomatch is not true (so the records exists!)
Then it starts updating the record with
rs.edit
rs!value1 = myvalue1,
rs!value2 = myvalue 2
rs.Update
There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
I have checked for typos.
View 5 Replies
View Related
Jul 24, 2014
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
View 3 Replies
View Related
Mar 27, 2008
I dont use linked tables very often, and when I always seem to run into the same problem.
I have two tables. The pri table has an index key defined as an autoindex, with no duplicates. The sub-table is linked to the pri
table key in a many-to-one relationship. The linked field in the subtable is defined as a long integer non-keyed field.
All is okay, until I attemp to add a SECOND record. At that point I get a 3022 error, stating I cant do something because I am attempting to create a duplicate key.
I would love to fix it, nut I am not sure exactly what the problem is that needs to be fixed. The pri key would not SEEM to be getting duplicated, and the subtable linked field is not keyed. Hmmm, I guess I need a steer here please.
eatc7402
View 1 Replies
View Related
Jan 17, 2006
My tables are sybase linked tables to my Access front end. I get this error whenever i click away from my mainform while my not-null fields (textboxes) have not been entered. So, i would like to put an error message "somewhere" that tells the user to fill in the necessary fields and highlight the necessary textboxes in red, instead of this scary ODBC message
http://img82.imageshack.us/img82/6829/error0el.png :eek: :eek:
When i put my error handling in the afterUpdate or afterInsert Event. I still get the above error msg instead of my msgbox.
Where do i put this message? or should be question be, "what the proper way to handle this intimidating error? :)
Here's my error handling:
On Error GoTo Err_Form_AfterInsert
'do something
Exit_Form_AfterInsert:
Exit Sub
Err_Form_AfterInsert:
MsgBox Err.Description, vbExclamation, "Please enter the Project Name, Project Leader Initials, Main User and Status Code" & Err.Number
Me!Combo67.BackColor = "red"
Me!Combo65.BackColor = "red"
Me![Project Name].BackColor = "red"
Me![Project Leader Initials Combo].BackColor = "red"
Resume Exit_Form_AfterInsert
Thanks in advance for your help :D
View 14 Replies
View Related
Sep 18, 2014
I have made a program in Access with linked tables from oracle (ODBC).
In access when I'm going to use that tables or when I see the records on the table all the numbers with decimals shows without separators (,).
99,99 looks like 9999.
This is a big problem because I'm working with prices.
I tried to change the NSL_Language in Regedit to:
AMERICAN_AMERICA.WE8ISO8859P1
from:
SPANISH_SPAIN.WE8MSWIN1252
AND it works ONLY in my computer, when I try to fix it and change the language doesn't work, and i don't know why.
PD: In addition, in the other PC's, some characters seems wrong like "�" that looks like a square.
View 2 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
Apr 7, 2014
I have two tables, submit and imgdest. Submit is edited by front-end users to load pictures for back-end users who then delete the images when they're done with them. Submit is edited by a form, in the form I've placed a button (Command37) that has code:
Code:
Private Sub Command37_Click()
Call InsertData
MsgBox "Completed", "0", "Completed Backup"
Exit Sub
End Sub
Private Sub InsertData()
[code]....
This code was working for a short while, now anytime it's run I get error 3027 - Cannot update. Database or object is read-only. However, I can open the linked table and manually change information in it with no problems.
View 14 Replies
View Related
May 23, 2005
Hi, I have a form that consists several buttons. One is to delete a table A, then add records to several tables and eventually it will do a join and insert records to table A, and display a report. It always works while all tables reside in the same Access database. Now we are trying to migrate to SQL server but not ready to get rid of Access yet. So we exported all tables to SQL server and created linked tables. We can open tables directly from Access without any problem. It shows the contents as the SQL database But when we tried to open the form and run the same button as before, we got an error:
Microsoft Access can't delete 0 record(s) in the delete query due to key violations and n record(s) due to lock violations.
We have no idea why this happens. Most of the time, it worked the first time when we clicked on the button. But we got the message when we clicked on the same button the second time. If we clicked "Yes" then it will append new records. There will be duplicate records since it doesn't delete the existing ones.
Did any of you encounter a similar problem before? Thanks in advance!
Jenny
View 10 Replies
View Related
Aug 29, 2014
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
View 5 Replies
View Related
Mar 8, 2006
I have just finished my first access database, well almost, one problem- I can't edit or type anything on my database when I am using a link to an xml workbook. If I just import the xml file to use on my database I can edit and save it but not when I used a linked xml worksheet. If I change the xml file entries in Excel, the changes appear on the linked database but it won't work the other way around - I can't even type anything in any of the fields. What have I done wrong and how can I remedy this problem? Any help would be much appreciated.
View 2 Replies
View Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
Aug 14, 2013
How to control the below message to something like this through VBA:
"The Job: " & JobNumber & " already exists in the table." or to open an alternate form where they can make the change. Allowing duplicates is not appropriate for what I am trying to accomplish. I just want a friendly way of rerouting a user to understandable answer.
instead of this:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
View 2 Replies
View Related
Nov 11, 2013
I have two table
1 is name master containing EMPID(Primary Key)
2 is Saving which also contain EMPID
i want to entered data in Saving with each and every EMPID with Master
View 1 Replies
View Related
Mar 17, 2006
Hi, All,
I have so far only worked with single tables and queries but not with linked tables. Now the following problem has arisen.
We have a database all of whose fields except two are of type = Text. Two fields are type = Memo.
We have to submit this database, i.e. its main table, to an external company for automatic 'cleansing' (UK: Telephone preference service). The cleansing program converts the cleansed database into a comma-delimited text file and returns it to us as such, and we then have to convert it back into an Access table.
(That's the way our supplier works, it is not under our control, and we have to learn to cope with it.)
Now the cleansing program is being screwed up by our Memo fields, because they contain commas. So when the cleansing program sees a comma, it interprets this as the start of a new field, and the same happens when we convert the text file back into a table.
Two solutions were suggested by the cleansing company, but only the following seems suffiently automatic and therefore acceptable to us.
I NEED HELP IN IMPLEMENTING THAT SOLUTION.
-----------------------------
I want to move the two memo columns into a separate table (memo table), linked to the original table (main table). Have the ID column (unique identifier) in both tables to keep the records together. Then I send only the main table for cleansing and the commas in the memo can no longer work havoc.
I have an idea of how to create the memo table: make a copy of the main table and then delete all columns except ID and the two memo columns.
But then I have to link the two tables (I do not know how to do that); where do I start.
I must also ensure that when new records are created (now usually through a form), both tables are expanded. And I must get information from both tables into one form.
At present I do not even know where to start, except for creating the Memo table by copying it and deleting certain columns.
I have a good book (John Viescas: Running MS Access 2000), but it is huge, and the problem is urgent (it stops our tiny company from marketing). I have no time to study the whole book to find what I need in this case. A chapter or page reference would be very useful.
Or some Tutorial on the web that deals with my particular task.
Thanks for your help.
Adrian
View 4 Replies
View Related
Sep 2, 2007
Dear All
Since last week I am working on a Database which I will use for my company. To make a long story short, I didn't find any appropiate softwares nor examples on the internet, hence, with my some knowledge in Access, I am trying to make this "to be" useful tool for my self.
Short brief on the project:
This is going to be used for a trading company to register:
- Suppliers
-- Products
- Customers
-- Inquiries
-- Orders
-- Offers
*Relations:
- Suppliers can have one or many products
- Customers can have one or many Orders / Inquiries / Offers
Problems:
(I have tried many different ways, but going nowhere)
1) The "Customers" table have 3 tables (Orders/Inquiries/Offers) linked to it - and all are based on the primary key "CustomerId". --> How can I make a form to enter these data linked?
2) Under "Orders" I want to select supplier from the Supplier table and Product from the Product table -- How?
I know this is a lot of info and probably too vague, but if someone could assist me with a few problems - then I would really really appreciate it and compensate somehow.
Looking forward for your help.
Best regards
View 1 Replies
View Related
Dec 3, 2013
I am looking to copy one exceptionally large table to another. I can use VBA to copy the entire Report2013.accdb to Report.accdb while compacting at the same time. This is very fast and works well. However once it's copied I need to rename the table tblReport2013 to tblReport. None of these table are in the accdb that I am running the scripts from FrontEnd.accdb. I am working very hard to keep the data tables under the 2 GB limit, which is why linking and keeping the data separate is necessary. There is the chance that Report2013 will be on the network while tblReport will be local to the user, and may or may not be a subset of the Report2013.
I have considered a number of options:
DoCmd.Rename - I can't seem to tell it a database location
I could try
docmd.copyobject - and see if it keeps the file size the same or smaller then the original.
I could just write the sql statement and copy the data that way, and compact the table afterwards. The trouble with doing the transfer that way is hitting the 2GB limit, before I can compact/compress the newly imported data.It just seems odd to have to find another solution when all I really want to to rename a table and ensure the new new is still linked as a separate table.
View 1 Replies
View Related
