Microsoft Visual Basic: "3021 No Current Record" on Insert?

Hi,

What is your code that generates this error?
Val Mazur
Microsoft MVP

http://xport.mvps.org

"Darrell Wesley" <DarrellWesley...discussions.microsoft.com> wrote in message
news:8B4F53EC-F728-47A7-A066-5BDE1A5BAF07...microsoft.com...
> How can I get a 3021 error on a Access table when I'm inserting records
> into
> a table that has a autonumber field as it's sole key? Any ideas?
> BTW: I get this error no matter if I use ADO or DAO methods. The strangest
> thing is that it just suddenly started happening with 5 out of 7 machines.
> Reinstalling the software made no difference.

Hi Darrell,

It was a known issue in Access when you could get this error if you try
insert a record into empty table or when you try to delete last record in a
table or when you open Access reports several times. It looks like first two
cases do not belong to you, but could be that you have a third one. Do you
have any service packs installed for Access?
I am also suspecting that you have a memory and connection leaks. First of
all do not instantiate ADODB.Command in a declaration part. It leads to the
memory leaks. Just split it into two following statements

Dim adoCmd As ADODB.Command
Set adoCmd = New ADODB.Command

Also use Set when you point to the opened connection. Otherwise new
connection will be opened and it could lead to the connection leaking. Use
next code

Set .ActiveConnection = adoCnnAK

Also try to use parameterized query instead of concatenating it dynamically.
In this case you could avoid issues with the injection and special
characters.
Val Mazur
Microsoft MVP

http://xport.mvps.org

"Darrell Wesley" <DarrellWesley...discussions.microsoft.com> wrote in message
news:EA1A5718-5318-4081-8F9F-D1CF3C5CCF8B...microsoft.com...
> Val,
> You see that this routine was using the old DAO code when the problem
> first
> showed up and I changed it to ADO to see if I could get some additional
> diagnostics but as it turns out I got nothing better than "No Current
> Record".
> The ACCESS table that it's attempting to append to is located on a network
> share and the table currently has some 23,867 records in it. Last w the
> problem started on 5 out of 7 machines and yesterday the problem extended
> to
> the other 2.
> I have no problem in writing to it from the developement machine.
> Both the er.number and er.NativeError show up as some hugh negative value.
>
> Sub WriteLogFile()
> Dim adoCmd As New ADODB.Command
> Dim dbAKSteel As Database
> Dim rsAK As Recordset
> Dim Sql As String
> Dim x As Long
> On Error GoTo LogError
> If IsNumeric(frmCoreInfo.lblOrdQty.Caption) Then
> '
> 'Create Log File Entry
> '
> Sql = "Insert into SteelLogFile "
> Sql = Sql & "(RunNo, ItemNo, Rev, Crord, OldStack, NewStack, "
> Sql = Sql & " OldWeight, NewWeight, Qty, Material, CoilSpec, "
> Sql = Sql & " Mfg, RollNo, CoreID ) Values ("
> Sql = Sql & "'" & frmCoreInfo.lblRunNo.Caption & "',"
> Sql = Sql & "'" & frmCoreInfo.lblRunItem.Caption & "',"
> Sql = Sql & "'" & frmCoreInfo.lblRunRev.Caption & "',"
> Sql = Sql & CSng(frmCoreInfo.txtOrderNo.Text) & ","
> Sql = Sql & Core.E & ","
> Sql = Sql & CSng(frmCoreInfo.txtStack.Text) & ","
> Sql = Sql & CSng(frmCoreInfo.lblOrgWeight.Caption) & ","
> Sql = Sql & CSng(frmCoreInfo.txtMax.Text) & ","
> Sql = Sql & CLng(frmCoreInfo.txtNumLoops.Text) & "," ' was order
> quantity
> Sql = Sql & "'" & (frmCoreInfo.txtMaterial.Text) & "',"
> Sql = Sql & "'" & Trim$(frmCoreInfo.txtCoilSpec.Text) & " ',"
> Sql = Sql & "'" & frmCoreInfo.txtMFG.Text & "',"
> Sql = Sql & "'" & frmCoreInfo.txtCoilNumber.Text & "',"
> Sql = Sql & "'" & frmCoreInfo.txtCore.Text & "'"
> Sql = Sql & ")"
> With adoCmd
> .CommandText = Sql
> .CommandTimeout = 60
> .CommandType = adCmdText
> .ActiveConnection = adoCnnAK
> .Execute
> End With
> 'Set dbAKSteel =
> OpenDatabase("\\S10AC52B\ENG\AKSteelEpstienValues.mdb")
> 'dbAKSteel.Execute Sql
> 'x = dbAKSteel.RecordsAffected
> 'If x = 0 Then
> ' MsgBox " Error occured on Inserting Log data, " & vbCrLf & "
> Please
> report to Supervisor ", vbCritical
> 'End If
> 'dbAKSteel.Close
> 'Set dbAKSteel = Nothing
> End If
> Exit Sub
> LogError:
> Dim er As ADODB.Error
> Dim strerr As String
> For Each er In adoCnnAK.Errors
> strerr = strerr & "Conn error " & er.Number & " " & er.Description
> &
> vbCrLf & er.NativeError
> Next er
> 'MsgBox "Error Occured in WriteLogFile - Press Ok to continue" & vbCrLf
> & _
> ' strerr
> Exit Sub
> End Sub
>
> "Val Mazur (MVP)" wrote:
>

Hi,

Do you know which service packs you have on a Access?
Val Mazur
Microsoft MVP

http://xport.mvps.org

"Darrell Wesley" <DarrellWesley...discussions.microsoft.com> wrote in message
news:59C07D84-9C46-4F33-AED5-8181E28E8951...microsoft.com...
> Val,
> Thanks for the reply but I don't see where memory leaks would be the cause
> of this error since it occurs on even the first attempt to insert a
> record.
> Just to add to this, the older DAO code shown below has been running with
> no
> problems for over a year and almost 2 years so why would it suddenly start
> generating errors. Sounds like something else altogether.
> "Val Mazur (MVP)" wrote:
>

I've now found out a little bit more about this problem. If I open up ACCESS
and then open the table that's having the problem and attempt to insert a
record I can get the "No Current Record" error.

The first 2 fields are both text fields 50 characters each. The 1st field
ones a value which looks like "05-086-PL" where "05" is the year and "086" i
s
the 86th production date and "PL" is the particular product line. If I enter
a value that looks similar to "05-086-PL" say "05-086-XX" it will allow me t
o
insert a new record but if I try to enter "XXXXXXXX" into that field I get a
"NO CURRENT RECORD", why?

I copied this MDB file to my local drive and had the same type of problem. I
ran the compact and repair utility and now it allows me to add the "XXXXXXX"
record.

Any ideas?

"Darrell Wesley" wrote:

> How can I get a 3021 error on a Access table when I'm inserting records in
to
> a table that has a autonumber field as it's sole key? Any ideas?
> BTW: I get this error no matter if I use ADO or DAO methods. The strangest
> thing is that it just suddenly started happening with 5 out of 7 machines.
> Reinstalling the software made no difference.