Modules & VBA :: Run-time Error 3021 While Updating Existing Record In DAO Recordset

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 Replies


ADVERTISEMENT

Modules & VBA :: No Current Record (3021) Error

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

Modules & VBA :: No Record Found - How To Bypass Error 3021

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

Run Time Error Updating Record -- HELP ! Please....

May 11, 2005

I have an orders Form with a subform for products. In the subform the user selects each product by first selecting the product category from a combo box, which filters the query for the second combo where they selects the product. Once this is done, I need the record to up date, but Access gives a run time error 3058: Index or primary cannt contain a null value.



In this case I belive the key field is the product_id form my 'order_detail_table' where this is a shared key.



I've been beating this to death (it's killing me!!) for a couple of weeks now and got nowhere. Maybe someone out there can have a look and see what my obvious mistake is ... (I've attached a cut down version of db with just tables, query and subform)

Thx.
Kev

View 2 Replies View Related

Modules & VBA :: Sorting Out Data To Be Used In Final Table - Runtime Error 3021

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

Forms :: How To Set Recordset Or Count To Determine Existing Record In Table

Dec 28, 2014

I am very new to VBA and I have been self-learning VBA from two month and have been assigned to new project Work Authorization & Lock Out Tag Out.I have table called tblWA & tblLOTO.Basically most of the LOTO/s are associated with WA #, following example

WA # in tblWA
Associated LOTO/s in tblLOTOs
25258
123456
123457
123458
123459
25259
123410
123411
123412
123413

However, both tables are in relationship right now..I have form called WA Switch Board where I have Datasheet View form with all WA records, fields are WA#, WAStatus, WAIssuedDate, IssuedBy, CompletionDate and etc but I have dropdown with with WAStatus with (In Field Today, On-Hold, In Approval, Cancelled, Close).Now from the main switch board when authorized people try to change the status of permit to Closed I want recordset or count to loop through tblLOTO and give me a message box if associated LOTO/s status not equal to Close.In other word, if the associated LOTO/s are not close then the selected WA # in WA Switch Board cant close.Here is the code I have been playing with no success

Private Sub PermitStatus_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb

[code]....

View 3 Replies View Related

Modules & VBA :: Appending To Existing Excel File From Recordset

Apr 18, 2014

I'm spinning my wheels on this. Trying to copy a recordset into an existing excel file.

Private Sub cmdExport_Click()

Dim conn As ADODB.Connection
Dim conn2 As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strConnection2 As String
Dim wks As Excel.Worksheet

[code]...

View 2 Replies View Related

Forms :: Updating Existing Record In A Form

Mar 19, 2014

I'm having difficulty updating an existing record in my form. I can enter a docket number on my form and when i hit my button (Find Docket) it brings up all the customers info (name, date, invoice, vat, total, etc.) What i want to do then is be able to amend/update the record. Just wondering do i need to put vba code in my save button and how to code it and is it similar to my find data code below.

Here is my save function code:

Function SaveData() As Boolean
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Skips Delivered")

[Code] .....

View 6 Replies View Related

Error 3021

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

Help With Error 3021

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

Error 3021

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

Error 3021 Is Driving Me Nuts

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

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 3 Replies View Related

Modules & VBA :: Modify Existing Record In DAO Record Set

Jul 18, 2013

I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database.

Code:

Private Sub Form_AfterUpdate()
Dim dbs As DAO.Database
Dim rstTest As DAO.Recordse
Dim strQuery As Strin
strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining

[code]....

View 12 Replies View Related

Modules & VBA :: Error 3061 - Open Recordset Fails

Dec 1, 2014

The code below fails.

Code:
Dim ResultQy As String, qdf As QueryDef
Dim ResultFm As String
ResultQy = "ByFederationQy"
Dim strSQL As String 'sql statement to execute

[Code] ....

When I run the code it fails at the last line giving error message:

Code:
Runtime error 3061 - toofew parameters expected 1

View 6 Replies View Related

Modules & VBA :: Message Box When Editing Existing Record?

Jun 7, 2013

What event would I attach code to, to have a message box pop up warning the user when that he is editing existing records.

I would like the message to appear not as the form loads, or as he tabs to the first control, but right as he makes any changes to the data displayed. Preferably the box should have a "OK" to continue the edit and a "Cancel" to undo it.

View 3 Replies View Related

Modules & VBA :: Editing Existing Record Using Subform

Jan 8, 2015

I'm successful in adding data from the form to the table using VBA. But I couldn't retrieve and edit the data. For retrieving and editing the data I have a different form "[Forms]![Editor]". I have all the fields listed in the image in both the form and table.

1. I want to allow the user to search and retrieve a specific row based on the condition Incident ID and the Package code are same
2. I want the user to edit the fields in the form and update it in the same row again.

View 2 Replies View Related

Modules & VBA :: Passing Recordset To Sub Procedure - Type Mismatch Error

Jul 13, 2015

I am trying to pass a recordset to a sub procedure as follows

Code:
Private Sub AddNewSProdStatus(rsTemp As DAO.Recordset)
rsSProdStatus.AddNew
rsSProdStatus![ProdID] = Me.Recordset![ProdStatusID]
rsSProdStatus![TitleID] = Me.Recordset![Title]
rsSProdStatus![ProdDealType] = 1

[Code] ....

However, whenever I call the procedure as below

Code:
AddNewSProdStatus (rsCSales)

I get a type mismatch error.

All Recordsets are declared as DAO and contain data...

View 1 Replies View Related

Modules & VBA :: Number Rounding Error When Pulling Double From Recordset?

Jan 28, 2015

I have 2 similar numbers in a table with the following parameters:

Double, Fixed, DecPlaces = 2
One number (GLminor) = 0.10
Second Number (GLmaj) = 0.50

When i use the following code to create and pull the numbers from a recordset the (Gmin) is OK and displays as 0.1 but the (Gmaj) has a problem and displays as 0. So when i'm attempting to use it in an equation (OtherNumber) / (Gmaj) i'm getting a divide by zero error.

Code:
sSQL1 = "SELECT * FROM tblRScales WHERE ID = " & RScaleID
Set rs1 = CurrentDb.OpenRecordset(sSQL1, dbOpenDynaset)
rs1.MoveFirst
Gmaj = rs1!GLmaj
Gmin = rs1!GLminor

I've tried using CDec(OtherNumber) / CDec(Gmaj) and still get the same error.

As both numbers have the same properties in the table parameters i'm completely lost as to why one seems OK while the other seems to round down to 0.

View 2 Replies View Related

Updating Record (3251 Error)

Aug 17, 2005

Hi, I've tried everything to get this to work with access. I can get it to work with SQL but unfortunately in this case I can't use SQL. Problem is that when I try and update the record, I get an error saying that the update method is not supported or that the lock is incorrect. It's a 3251 error.

Here's the code I'm using:

Dim dbConn2 As ADODB.Connection
Dim dbRst2 As ADODB.Recordset

Set dbConn2 = New ADODB.Connection
dbConn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= R:myAccessDB.mdb"

Set dbRst2 = New ADODB.Recordset

dbRst2.CursorLocation = adUseClient
dbRst2.CursorType = adOpenDynamic
dbRst2.LockType = adLockOptimistic

Set dbRst2 = dbConn2.Execute("Select ROAD_ID FROM GIS_MSLINK_ROADID WHERE MSLINK" & " = " & UserForm2.slsn_textbox1.Value)

dbRst2.Fields("ROAD_ID").Value = UserForm2.slsn_textbox2.Value

dbRst2.Close
dbConn2.Close

Set dbRst2 = Nothing
Set dbConn2 = Nothing

View 9 Replies View Related

Error In Updating Record In Table

Jan 1, 2015

I have a separate button to update the record, but when I run or click the cmdUpdate button it gives me an error;

Code below is the one I'm working thru.

Private Sub cmdUpdate_Click()
strSQL1 = "UPDATE tblAMHMace " & _
" SET ReferenceNo=" & Me.txtReferenceNo & "'" & _
", DateLog='" & Me.txtDate & "'" & _
", DocType='" & Me.txtDocType & "'" & _

[Code] ......

View 6 Replies View Related

Error Updating Record In Bounded Form

Jun 19, 2005

Hi guys I am creating maintenance form to update and add new records to department table.
But when I try to edit department field in employee table.
(I get the following error.I get same when try to add new record.)

Error:
You cannot add or change a record becasue a related record is required in table department

http://i5.photobucket.com/albums/y1...gdepartment.jpg
==> pic of error errroupdatingdepartment

http://i5.photobucket.com/albums/y1...eletionship.jpg
===> timekeepingreletionship


I be happy if some expert help me how to update and add new record to department table successfully without the above error and without changing the db structure.
Thanks

View 2 Replies View Related

Modules & VBA :: Error After Updating Fields By SQL

Jul 31, 2013

After run this command

DoCmd.RunSQL (" Update [Receipt_TB] SET [Receipt_TB].[Changed_by] = '" & UserIdP & "' WHERE [Receipt_TB].[Receipt_ID] = '" & Me.Receipt_ID & "' ;")

I see the following message:

"Data type mismatch in criteria expression"

Although all fields and parameter are integers...

View 5 Replies View Related

Modules & VBA :: Why RecordSet Not Starting From Record 1

Sep 1, 2014

Why my Recordset start from record # 301 instead of # 1 Here's part of my code:

Set db2 = CurrentDb
Set rst2 = db2.OpenRecordset(strTable2)
If rst2.RecordCount = 0 Then
MsgBox "No records to process."

[Code] ....

Table has 12,000 records and the first record has an ID of 1. So why is it starting from the record 301? What am I doing wrong?

View 3 Replies View Related

Modules & VBA :: Lookup A Record In Recordset

Oct 17, 2014

I am trying to lookup a record in a recordset. If no match is found then run an append query. I am having trouble coding the findfirst syntax.

Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim WO As String
WO = Forms!frmdsh_workorder!txtWO
Set db = CurrentDb

[Code] .....

When I run the Function, it throws a RT error #3077 on the .findfirst line. (Syntax Error (Missing Operator) in expression. )

View 3 Replies View Related

Modules & VBA :: Copy Current Record From Recordset To Csv

Aug 4, 2013

I have a DAO.recordset called "rsSQLIn". This comes from a csv file by:

Code:
strSql = "SELECT * " _
& "FROM [Text;Database=" _
& strFolder _

[Code]....

While the validation runs a boolean keeps track of validated input and errored input.

After validation the validated input is dumped in the table.

Now what I want is de saving the errored record from "rsSQLIn" to be copied to a new .csv file.

The problem I have is that I cant seem to get the current record from the recordset "rsSQLIn". How do I reference this? I need the complete set of 24 fields being the same within "rsSQLIn"

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved