Visual Basic :: Current Record Set Does Not Support Upadating

Rs.Update Record Updates Next Record Not Current Record Access DB
For some reason I can't find (lack of VB knowledge obviously) the record that I want to update is updated in the following record and not in the current record.... I'm using Access 2002 database and VB 6. Here is the code: (the DB is opened with "methodaccessdatabase" in a module)

Code:
Option Explicit
Private Sub Form_Load()
    Skin1.ApplySkin Management.hwnd
    methodaccessdatabase
    Set rs = db.OpenRecordset("Responsibility")
End Sub
Private Sub Command1_Click() 'Next record button
    If Not rs.EOF Then
        Text1 = rs(0): Text2 = rs(1): Text3 = rs(2)
        rs.MoveNext
      Else
        rs.MoveFirst
    End If
End Sub
Private Sub Command2_Click() 'update record in database
    rs.Edit
    rs(2) = Text3.Text
    rs.Update
    rs.MoveNext
End Sub

The database has only three fields, the one I want to update is field (2 = responsible) jpeg of DB attached...it seems soo simple but I'm stuck!

Thanks
vbprog1144

Edited by - vbprog1144 on 2/10/2004 6:03:37 PM

Current Recordset Does Not Support Updating
hello everyone! can u help me with this?

Code:
Option Explicit
Dim MyConn As ADODB.Connection
Dim MyRs As ADODB.Recordset

Private Sub Command1_Click()
MyRs.Fields("Fname") = Text1.Text
Set MSHFlexGrid1.DataSource = MyRs
MSHFlexGrid1.Refresh
End Sub

Private Sub Form_Load()
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data Source=" & App.Path & "DatLibrary.mdb"
MyConn.Open

Set MyRs = MyConn.Execute("SELECT * from books")

'Set MSHFlexGrid1.DataSource = MyRs
'MSHFlexGrid1.Refresh

'MyRs.MoveNext

End Sub

Current Recordset Does Not Support Updating
When trying to insert data into a field of my Access table i get the error:

"Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."

I have tried changing the locktype to optimistic..etc but it didnt make a difference. Any ideas?

Here is my code for inserting a value into the database:

sqlString = "select Score from tblHard where User = '" & playername & "'"

Set rstRecordSet = conConnection.Execute(sqlString)

If ((rstRecordSet.EOF <> True) And (rstRecordSet.BOF <> True)) Then

rstRecordSet.Update ("Score"), counter
End If

conConnection.Close

Current Recordset Does Not Support Updating
Below is my code:

Code:
Public Sub cmdbtt_Add_Click()
Dim con As New ADODB.Connection
Dim lsDSN As String
Dim objRS As New ADODB.Recordset
lsDSN = "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=127.0.0.1,1433;" & _
"Initial Catalog=abc;" & _
"User ID=sa;" & _
"Password=aC852088"
Dim numberid As Integer
Dim sex As String

con.Open lsDSN
SQL = "SELECT * FROM id ORDER BY id"
objRS.Open SQL, con, , 3
objRS.AddNew
objRS("IC") = txtb_Ic.Text
objRS.Update
numberid = objRS("id")
objRS.Close
Set objRS = Nothing
con.Close
Set con = Nothing

con.Open lsDSN
SQL = "SELECT * FROM Profile ORDER BY IC"
objRS.Open SQL, con, , 3
objRS.AddNew
objRS("id") = numberid
objRS("Name") = txtb_Name.Text
objRS("IC") = txtb_Ic.Text
objRS("Birthdate") = txtb_Birthdate.Text

If op_SexM = True Then
sex = "Male"
Else
sex = "Famale"
End If

objRS("Sex") = sex
objRS("Address") = txtb_Address.Text
objRS("HomePhone") = txtb_HomePhone.Text
objRS("MobilePhone") = txtb_MobilePhone.Text
objRS("ComAdd") = txtb_ComAdd.Text
objRS("OfficePhone") = txtb_OfficePhone.Text

objRS("PatPhoto").AppendChunk (Pat_PhotoAdd.Picture)

objRS.Update
objRS.Close
Set objRS = Nothing
con.Close
Set con = Nothing
MsgBox "Add", vbInformation, "Detail Add"

End Sub

We run the application, and add data but cause the exclamation below:

"Current Recordset does not support updating. This may be a limitation of provider, or of the selected locktype."

How to fix it?

Thanks.

Current Recordset Does Not Support Updating
Hi everyone.
I'm using SQL Server 2000, and I'm having a slight problem with a recordset from a stored procedure. When the query is first opened all settings indicate that the recordset is editable. As soon as I try to add/Modify a record I get the following error:
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

If I check the LockType right after executing a addnew to the recordset the property changes to adLockReadOnly. This is a simple query too.
There is one other thing that I thought it could be. I have one field in the table set to uniqueidentifier and I have newid() in the Default value field in Enterprise manager. If this is the cause, I can change that easy. Please let me know what you all think.
Here is an example of the code thats loading the recordset:
   If rsInsurance.State <> adStateClosed Then
      rsInsurance.Close
   End If
   Set mCommand = New ADODB.Command
   mCommand.CommandType = adCmdStoredProc
   mCommand.CommandText = "nb_GetInsurance"
   mCommand.ActiveConnection = conn
   mCommand.Parameters.Refresh
   mCommand.Parameters("@PatientID").Value = lPatientId
   mCommand.Parameters("@AdmissionDate").Value = AdmissionDate
   rsInsurance.CursorLocation = adUseClient
   rsInsurance.CursorType = adOpenStatic
   rsInsurance.LockType = adLockOptimistic
   Set rsInsurance = mCommand.Execute
Here is the Stored procedure:

CREATE PROCEDURE [dbo].[nb_GetInsurance]
@PatientID nvarchar(12),
@AdmissionDate smalldatetime
AS
Select     KeyId, PatientID, AdmissionDate, Insurance_Name, Insurance_Number
From Insurance
Where PatientId = @PatientId and AdmissionDate = @AdmissionDate
GO

I do have the CursorLocation set for the connection object. I added the above CursorLocation to see if problem went away, but it didn't. Thanks again for any help.
Michael Lee

Current Recordset Does Not Support Updating Problem
Heya all,

I have looked through the forums, and have not been able to find an answer to my problemn, so thought I would let the experts have a look at the code, to see where the problem is coming from.

Code:
Option Explicit

Dim cnEbility As New ADODB.Connection
Dim rsEBMaster As New ADODB.Recordset
Dim cnMSAccess As New ADODB.Connection
Dim rsMSsb As New ADODB.Recordset

Private Sub Form_Activate()

cnEbility.Open "Provider=vfpoledb.1;Data Source=C:ebilitydata;Mode=ReadWrite|Share Deny None"
cnMSAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and Settingspeterc.UBSMy Documentsdb1.mdb;User Id=;Password=;Mode=ReadWrite|Share Deny None"

'Unload Me

End Sub

Private Sub Command1_Click()

rsEBMaster.Open "master", cnEbility, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic
'rsEBMaster.Open "master", cnEbility, adOpenDynamic, adLockOptimistic
rsMSsb.Open "select * from completewarehouse", cnMSAccess

rsMSsb.MoveFirst

Do While rsMSsb.EOF = False

If rsEBMaster.State = 1 Then rsEBMaster.Close

rsEBMaster.Open "select master.isbn,master.location from master where isbn = '" & rsMSsb("ISBN").Value & "'", cnEbility

'rsEBMaster.AddNew

rsEBMaster("Location").Value = Trim(rsMSsb("S+B").Value)

rsEBMaster.Update

rsMSsb.MoveNext

Loop

rsEBMaster.Close
rsMSsb.Close
cnMSAccess.Close
cnEbility.Close

Unload Me

End Sub

When the program gets to the .Update line I get the error "Recordset does not support updating".

I have setpped through the code, and the open works, and everything looks ok, but......

Thanks in advance.

Current Recordset Does Not Support Scrolling Backward
im going to delete a record but i encounter an error of current recordset does not support scrolling backward..
here's my code
Code:
Private Sub cmdDelete_Click()
With tbDepartment
ans = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Wait")
If ans = vbYes Then
.Find "DepartmentName = '" & txtDepTitle.Text & "'"
If .EOF = True Then
MsgBox "Huh?"
Else
.Delete
.Requery
txtDepTitle = ""
End If
Else
Refresh
End If
End With
End Sub

Private Sub cmdGetDepartment_Click()
PickDepartment.Show
PickDepartment.cmdSelect.Visible = False
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub Form_Load()
tbdepartment.open "Select * from tbdepartment", cn. adopendynamic, adloackoptimistic
end sub

Current Recordset Does Not Support Updating. This May Be A Limitation Of The
I've spent the last three days trying to find an answer I can understand. I'm new to this, so please accept my aplogies if this one's been asked and answered a gazillion times.

I'm using a form to add records to an Access 2002 database using an ADODB connection. I was able to add records to the database until I added a second table to my database (linked to main which uses an autonumber as the primary key). I realize that I need to get the primary key from table one and (somehow) insert it into table2. That's where I get lost. :-(

I'm hoping someone can tell me (in plain English) just how I go about getting the autonumber from my justadded record and then how I use it to add records to my second table.

Relevant code:

Dim Conn

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("/mydatabase connection.mdb")

Conn.Open

Set RS = Server.CreateObject("ADODB.RecordSet")

Conn.BeginTrans

RS.LockType = adLockOptimistic
RS.CursorType = adOpenDynamic
RS.CursorType = adOpenKeyset

'Open Client Table.

RS.Open "SELECT * FROM Client", Conn

RS.AddNew

RS("Title") = Request.Form("title")
RS("FirstName") = Request.Form("fname")
RS("Initial") = Request.Form("initial")
RS("LastName") = Request.Form("lname")
RS("Address1") = Request.Form("address1")
RS("Address2") = Request.Form("address2")
RS("City") = Request.Form("city")
RS("Prov") = Request.Form("prov")
RS("PCode") = Request.Form("pcode")
RS("CountryName") = Request.Form("country")
RS("E-mail") = Request.Form("email")
RS("Phone") = Request.Form("phone")
RS("Format") = Request.Form("format")
RS("EnrollDate") = Date()
RS("ExpiryDate") = DateAdd("m", 6, Date)

RS.Update
RS.MoveLast

RS.Close 'This worked to here as a single table

'Open Business Table.

RS.Open "SELECT * FROM Business", Conn

RS.AddNew

RS("BusName") = Request.Form("busname")
RS("BusPhone") = Request.Form("busphone")
RS("Ext") = Request.Form("ext")
RS("Industry") = Request.Form("industry")
RS("EmpNum") = Request.Form("empnum")
RS("Email") = Request.Form("email")
RS("Agency") = Request.Form("agency")
RS("WebPage") = Request.Form("webpage")
RS("Comments") = Request.Form("notes")

RS.Update 'This is where I get the error: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

RS.MoveLast

RS.Close

Conn.CommitTrans

At this point I'm about ready to stick it all into one table (not a great idea but would work) and call it quits.
Any help would be gratefully appreciated.

Kasperkatz

Edited by - Shandy on 3/21/2004 5:47:14 PM

Error 3251: Current Recordset Does Not Support Updating...
In my following code following error is displayed at line 4,
whenever I execute the query strSQL. But if I directly
update the table at the current postion, then it works.
Also, in the query it is at the right position, means
uid is right, and exists in the table.

ERROR:
Run-time error '3251':
Current Recordset does not support updating. This may be a
limitation of the provider, or of the selected locktype.

-------------------------------------------------------------------
Private Sub cmdOK_Click()
1 Dim strSQL As String
2 strSQL = "Select address_r from persons where userid " & uid
3 Set rs_persons = cn.Execute(strSQL)
4 rs_persons!address_R = txtHaddress.Text
5 rs_persons.Update
End Sub

Private Sub Form_Load()
6 Set cn = New ADODB.Connection
7 'Set rs_emails = New ADODB.Recordset
8 Set rs_persons = New ADODB.Recordset

9 cn.Open "DSN=RPIM"
10 rs_persons.Open "Select * from persons", cn, adOpenDynamic, adLockOptimistic

End Sub
-------------------------------------------------------------------

What is wrong ?

Rohit.

Current Provider Does Not Support Transactions Error 3251
Hi all, I have made an application that connects to an MS Access 2000 database which sits on an IIS server on a Windows XP machine. I am using a MS Remote provider and configured the server for read/write permissions. Everythings seems to go alright.. but when I try adding data into the database, it crashes on a .CommitTrans and gives me the error message Error 3251: Current Provider does not support Transactions. Are there any solutions to this problem ? I tested putting data into the database with a .execute command and it works with no problems. That proves my write permissions are working and the data can being written. What other settings do I need to check for ? Cheers

A VB turned PHP geek

Run-time Error 3251 Current Recordset Does Not Support Updating?
Hello guys;

I get this error after i click print button in my project, although it print perfectly this Runtime error pops up.

I tried to look for some similar thread but no luck,

I am using access for my database, i check my connection and test it ok.
i change my Dataenvironment1 connection where the database are kept after installation using Package and Deployment wizard "C:ProgramFilesautoshopautoshop.mdb"

[VBCODE]Private Sub Form_Load()
DBPATH = App.Path & "autoshop.mdb"
connAuto.CursorLocation = adUseClient
connAuto.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source = " & DBPATH & ";Persist Security Info =False"
frmLogin.Show vbModal

rsMain.Open "Select * From Main", connAuto, adOpenStatic, adLockOptimistic
rsOrder.Open "Select * From [Order]", connAuto, adOpenStatic, adLockOptimistic
add_field Combo3
rec = 0
recorder = 0
locker
ClearText
Text14.Locked = True
Option1.Value = True
DTPicker3.Enabled = False
Command5.Caption = "&Add"
Command3.Caption = "Add Order"
Command10.Enabled = False

End Sub[/highlight]

I did check the Data link Properties before i debug it

What else do i missed here.?

Thanks for any reply guys.

naz

Run Time Error '3251': Current Recordset Doesnot Support........
I m doin a project, which have listview and button...i want to save listview items in access database,the part of code is given below...when i run the program the following error occurred, the codes below i have written was suggested in forum, but still getting error.
I m using MDAC version 2.8,Visual Basic with SP6

Run time error '3251':
"Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."

Private Sub cmdSave_Click()
Dim conS As New ADODB.Connection, recSet As New ADODB.Recordset, connString As String, strDatabase As String, invNo As Long, strDate As String, invCusName As String, invCustAdd As String, invCustNo As String
invNo = lblTaxinvoiceno.Caption
strDate = lblinvoiceDate.Text
invCusName = txtCustomerName.Text
invCustAdd = txtCustAddress.Text
invCustNo = txtCustTelephone.Text
strDatabase = App.Path & "Data" & "data.mdb"
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase
conS.Open connString
recSet.Open "datas", conS, adOpenForwardOnly
Dim itm As ListItem
For Each itm In listInvoice.ListItems
    recSet.AddNew
    recSet.Fields("SerialNumber").value = itm.Text
    recSet.Fields("Products").value = itm.SubItems(1)
    recSet.Fields("Description").value = itm.SubItems(3)
    recSet.Fields("Quantity").value = itm.SubItems(4)
    recSet.Fields("Unit_Price").value = itm.SubItems(5)
    recSet.Fields("TotalAmount").value = itm.SubItems(6)
    recSet.Fields("CustomerName").value = invCusName
    recSet.Fields("InvoiceNubmer").value = invNo
    recSet.Fields("Address").value = invCustAdd
    recSet.Fields("Period").value = strDate
    recSet.Fields("Telephone").value = invCustNo
    recSet.Update
Next
End Sub

recSet.Addnew is highlighted

Please someone help me out with this.
Thank you,
Sabin.

How To Record The Current Record Number In A Recordset??
glbRecSet.MoveFirst
Do While Not glbRecSet.EOF
  MsgBox "Account Number = " & glbRecSet("AccountNumber"), vbOKOnly, "Search Results"
  glbRecSet.MoveNext
Loop

This works fine, but what I want to do is have the it also tell me that this is, for example, record 1 of a total of 5 records in the recordset.  I know how to use ".RecordCount" to return the total number of records, but how do I return the number of the current record in the recordset?

Thank you
Kevin

Current Provider Does Not Support ..... Index Functionality - Run-time Error 3251
Hi,
I am using Dataenvironment in VB6, When I tried to use the seek method, VB shows error i.e.

++++++++++++++++++++++++++++++++++++++++++++++++++
Run time Error 3251
Current provided does not support the necessary interface for index functionality
++++++++++++++++++++++++++++++++++++++++++++++++++

The code is as follows:

With DE.rsParty
.Seek Trim(CmbParty) <------- Error Statement
If Not .EOF Then
TxtAddress1 = ""
TxtAddress2 = ""
TxtPhone = ""
TxtMobile = ""
End If
End With

Your earliest reply will be appreciable.

Thanks in advance.

Nishal

Current Record
Hello

i have this form on access 97 that i use to edit data in one table......in this form i have combo boxes, txtboxes etc.. that i use to show and edit the information of the table ....... my question is the following...i want to edit data that i'm not display in my form such as the creation date of the record...this information is stored in the same table...but i don't need to show it ...so how can i call to the current recordset to save this information in a specific field

Table name tblaltas
field name creadate

i hope someone can help
Thanks in advance

No Current Record
Hi,

I have the following code:

Code:
Private Sub Submit_Forms()

Dim i As Long
Dim MtstN, FtstN
Dim MyDB, RS, lngRSCount, lngStartValue
Dim MHP, FHP, MCP, FCP

'This code will set the timer defined in Sub sSleep
Const cTIME = 3000 'in MilliSeconds
Const clessTIME = 500

'This is the beginning
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set RS = MyDB.OpenRecordset("2007 Football Registration",
dbOpenTable, dbReadOnly)
lngRSCount = RS.RecordCount
RS.MoveLast
RS.MoveFirst
'Decide where you want to start
lngStartValue = InputBox("On which record would you like to
begin?", "Starting Participant", 1)
With RS
If .BOF = True And .EOF = True Then
MsgBox "This recordset is empty"
Else
.MoveLast
End If
End With
try = MsgBox("Number of Records = " & RS.RecordCount, vbOKOnly, "MSG")

RS.Move (lngStartValue - 1)
'cTIME = 5000
'Call sSleep(cTIME)

ForC = RS.Fields("Football/Cheer")
FstN = RS.Fields("First Name").Value
LstN = RS.Fields("Last Name").Value
DOB = RS.Fields("Date of Birth").Value
If Mid(Mid(DOB, 1, 2), 2, 1) = "/" Then DOB = "0" & DOB
If Mid(DOB, 5, 1) = "/" Then DOB = Left(DOB, 3) & "0" & Right(DOB, 6)
MD = Left(DOB, 2)
DD = Mid(DOB, 4, 2)
YD = Right(DOB, 4)
WT = RS.Fields("Weight")
GPA = RS.Fields("GPA").Value
GPA = Left(GPA, 2)

'too much more to show, but problem is above.
Now, I get the 3021 Error every time, unless lngStartValue = 1. Highlighted at the line that begins ForC =... the first variable. My problem is that the BOF/EOF check shows records, and the msgBox shows records (199). Get to the first variable, "No Current Record". I feel like I have tried everything, to no avail.

Any help would be appreciated.

Thank You,

TB

Edit by Moderator:
Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

No Current Record
Hi,

I have what I think should be a rather simple problem, but I can't find it. I have added a debug code and I'm not getting anything so I'm assuming that the loop code isn't working because it can't find any records to search. The correct form is coming up, but all fields are blank and when I click either next or previous buttons it says "No Current Record." I kept getting this error before, but after I added the code to check for BOF and EOF it allows me to execute this script, but not for it to work. I was orginally using the Find commands, but I was getting the same error so I tried using the loop. If I run formEditClass itself it works fine displaying all data and allowing me to freely move between records.

VB Code:
formEditClass.Show If formEditClass.dbClassInfo.Recordset.BOF = True ThenformEditClass.dbClassInfo.Recordset.MoveNextElseIf formEditClass.dbClassInfo.Recordset.EOF = True ThenformEditClass.dbClassInfo.Recordset.MovePreviousEnd If Do Until formEditClass.dbClassInfo.Recordset.Fields("ClassName").Value = CSformEditClass.dbClassInfo.Recordset.MoveNextLoop

No Current Record
Hi all

When I open the form that has information in the database , there is no problems at all, but when I open the form with empty database this is the message I get. no current record.

Any one can help me to handl this problem please.

I will be highly appreciated.

Thank you

Mohammed

Getting The Current Record # Using ADO
I'm using Ado to access my database on an MS SQL 7.0 server. I want to know
as I am going through the recordset of a table what Record number I'm at.

Joel Foudy

Current Record
hello.

i just wanna ask is it possible to insert new record to a field and
yet that inserted record will be considered as ist record.

ex. if field 1 has 5 records. and field2 has 0 record.
then i will try to addnew or insert a record to field2.
normally , the inserted record will be considered as record6 of field2.
leaving field2 record 1- 5 as blank.

can somebody please tell me how to do this?
thanks,

anna

Current Record In ADO
Is there a way to find out which record I'm currently looking at in an ADO recordset?

For example, after a series of .MOVENEXT commands in a recordset, I want to know which record out of .RECORDCOUNT I'm looking at.

Thanks,
Yazster

Current Record Row
Hello!

Is there a function (such as recno() in other languages)to get the current record row of a table? I need to save to a variable this current record row so that I can use it later. Thanks in advance!

"current Recordset Does Not Support Updating" Using DSN-less Connection
What am I missing here?

Code:
Private Sub Command1_Click()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
Dim x As String

sConnString = "Provider=SQLOLEDB.1;Initial Catalog=DCGSRV;Data Source = SRSQL2KDEV;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;integrated security=sspi"

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "SELECT * FROM msdb"
cmd.CommandType = adCmdText

Set rs = cmd.Execute

With rs
.AddNew
![Test] = "Testing"
.Update
end with

end sub

"current Recordset Does Not Support Updating".
Hi folks,

I'm just starting out teaching myself VB, and so far have got on quite nicely with it. I'm coding a fairly simply database system; something to keep track of patients through a hospital unit.

Originally I used the ADODC to manage the data, and that worked fine. However, I wanted to understand *how* the program worked, so I've stripped the ADODC out of the code, and am trying to write the logic by hand. This is perhaps a mistake ;-)

The relevant bit of code is below:

***********

Dim conPatient As Adodb.Connection
Dim dtaPatient As Adodb.Recordset

Set conPatient = New Adodb.Connection
Set dtaPatient = New Adodb.Recordset

conPatient.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsRikDesktopMy VBNHSpatient.mdb;"
conPatient.Open
Set dtaPatient = conPatient.Execute("SELECT * FROM Patient")
dtaPatient.MoveFirst
dtaPatient.MoveNext

dtaPatient.AddNew
<snip> most of updates
dtaPatient.Fields("Postcode") = txtPostCode
dtaPatient.Update

*********

This looks ok to me; but the program doesn't like it. When it gets tot he dtaPatient.AddNew line, it falls over, complaining:

*********

Run time error '3251'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected recordtype.

*********

I'm trying to get it to write to an access database as specified in my code. The database is one I created in access, it's indexed by an automatically incredmenting number.

I'm guessing I'm opening this wrongly, and as a result updates are forbidden (as is reading backwards, as I've tried doing a MoveLast, which also causes problems). It seems perfectly happy with the file *until* it reaches the AddNew; the MoveFirst and MoveNext both show the correct records in the "watch" screen.

All help is very gratefully received; this has been doing my head in all day!

Many thanks,

Rocko.

"Current Recordset Does Not Support Bookmarks ....".
I am trying to use the book mark property of a record set and I receive the error "Current Recordset does not support bookmarks ....". I am using SQl Server 7 tables, opening the recordset dynamically
If I am reading everything correctly a dynamic curser supports bookmarks.

The code I use to create the recordset is:

Public Function fOpenDynamicRS(ByVal strS As String, Optional con As ADODB.Connection, Optional ByVal locking As ADODB.LockTypeEnum = adLockOptimistic) As ADODB.Recordset
'opens dynamic recordset
' first check to see if we have passed a different connection string other
'than Boss default if not then use BossCN
  'If con.ConnectionString = "" Then
  If con Is Nothing Then
     Set con = BossCN
  End If
  'now be certain that nothing has happened to the connection
  If con.State = adStateOpen Then
    Set fOpenDynamicRS = New ADODB.Recordset
    fOpenDynamicRS.Open strS, con, adOpenDynamic, locking

  Else
      ERR.Raise 911, , "Database Connection not open " & Chr$(10) + Chr$(13) & _
                    con & Chr$(10) & Chr$(13) & _
                    strS
  End If
End Function

BossCN is the open ADO connection and strS is the SQL statement.

Obviously, I am missing something, but what?

I am also cross posting this to the VB database forum and the SQL forum.

Thanks

Terry (cyberbiker)

Get The Record Of Current Date!!!
Table Name = Calendar

CalendarDate Year Workweek Workday
............... ....... ...... ......
30/12/2004 2004 12 4
31/12/2004 2004 12 5
01/01/2005 2005 12 6
02/01/2005 2005 1 0
03/01/2005 2005 1 1
04/01/2005 2005 1 2
05/01/2005 2005 1 3
06/01/2005 2005 1 4
07/01/2005 2005 1 5
08/01/2005 2005 1 6
09/01/2005 2005 2 0
10/01/2005 2005 2 1
11/01/2005 2005 2 2
12/01/2005 2005 2 3
13/01/2005 2005 2 4
14/01/2005 2005 2 5
and so on......
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''
Public Function myFunction ()
Dim rsCalendar As Recordset

Set rsCalendar = CurrentDb.OpenRecordset ("Calendar" , db_open_dynaset)

With rsCalendar

End With

rsCalendar.Close

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''

I want to get the record of Date() which point to Today date . I want to get the row of record of the Maximum value in "Workweek" which is the last Workweek value. I want to move to the next 20 record. If EOF then take the last record based on the Workweek. If not EOF then get the current record. What should i do?
Thanks in advance

Regards,
bananatree

Current Record Lock
hi hope all is well

(i am using microsoft access 2000)
I have a form and also navigation buttons at the bottom of the form.. i would like to lock a current record after it is updated... so the user can not go and edit what has been inputted in the previous row..

can someone help me out please....

thanks in advance - sonia...

No Current Record Error
I have a Access 97 database, and am making a VB interface to add/delete/edit records. I have this one table (called TblResults) that has no records in it to begin with, but I want to add one through VB.

Before I add any records, the table is empty, but some of the fields have a 0 in them, because that was the default entry for the Default Value property. One such field is called MatchID.

I want VB to check to see if MatchID is 0, and if it is, add a new record which has Match ID 1, and then delete Match ID 0. If VB checks Match ID and it isnt 0, then I want to add a new record which has a Match ID of 1 more than the last record.

I entered in the following code:

Code:
With DatResults.Recordset
If !MatchID = "0" Then
.AddNew
!MatchID = "1"
.Update
.FindFirst "MatchID = 0"
.Delete
Else
.MoveLast
Dim LastID as integer
LastID = !MatchID
.AddNew
!MatchID = LastID + 1
.Update
End If
End With

However, I get a error that says No Current Record, and the If !MatchID = "0" Then line is selected as the error line. I think this is because since the table is empty, it cant check to see what is in the field MatchID.

How can I check to see if the table is empty (or MatchID = 0) without encountering an error?

No Current Record Error
I have a program job quoting program that is giving me some problems. I use ADO to create new records in the database... every new record requires a new entry in 3 tables... here is the code that I use to add records:

Code:
Private Sub addrecord_Click()
Dim count As Integer

'Synchronize database and initiate new quote command
Set addcn = New ADODB.Connection
Set addrs = New ADODB.Recordset

addcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + Form4.Text2
addrs.ActiveConnection = addcn

addrs.Open "JobOrderData", , adOpenKeyset, adLockOptimistic
addrs.MoveLast
count = Val(addrs.Fields("QuoteNo"))
'MsgBox count
addrs.AddNew

'increment quote number
addrs.Fields("QuoteNo") = count + 1
addrs.Fields("date") = Date
addrs.Update
'MsgBox addrs.Fields("QuoteNo")
addrs.Close

Set addrs = New ADODB.Recordset
addrs.ActiveConnection = addcn

addrs.Open "BillofMatl", , adOpenKeyset, adLockOptimistic
addrs.AddNew
addrs.Fields("QuoteBM") = count + 1
addrs.Update

addrs.Close

Set addrs = New ADODB.Recordset
addrs.ActiveConnection = addcn

addrs.Open "QuoteSheetInfo", , adOpenKeyset, adLockOptimistic
addrs.AddNew
addrs.Fields("QuoteNO") = count + 1
addrs.Update

addrs.Close
addcn.Close

'MsgBox addrs.RecordCount

Form1.Text63 = count + 1

Unload Form4

'Goto the new quote created in form1
Form1.getrecord = True
Form1.Command11.Value = True

Form1.Show

To access this new record I use the following DAO commands:

Code:

Private Sub getrecord_Click()

'Locate quote number (not active command)
Data1.Recordset.Index = "QuoteNo"
'MsgBox Text63

Data1.Recordset.Seek "=", Form1.Text63
If Data1.Recordset.NoMatch Then
MsgBox ("Check to be sure you have selected a valid quote no, or the quote number range")
End
'Data1.Recordset.MoveFirst
End If

End Sub

This getrecord sub will work sometimes and other times I will receive NO CURRENT RECORD. I cannot figure out why it works sometimes and not others.

No Current Record Message
I have a delete procedure which I have included a findfirst method in. Now an error comes up in this procedure stating

"No Current Record"

Have I missed something from the findfirst?? or is the loop incorrect?

The last line in the code is where the error occurs:

Dim matflag, matchdel, srchdel As String
'search for match
matflag = ""
matchdel = cbodeletepart.Text
srchdel = "Ryco_part = '" & matchdel & "'"
With fordpal
.FindFirst srchdel
If .NoMatch = True Then
matflag = ""
Else
matflag = "valid"
End If
End With
If matflag = "" Then
MsgBox "Please select a part from the list"
cbodeletepart.SetFocus
Else
parttodel = cbodeletepart.Text
ques = MsgBox("Are you sure you want to delete " & parttodel, vbYesNo)
If ques = vbYes Then
fordpal.MoveFirst
Do Until founddel = "yes"
If parttodel = fordpal![ryco_part] Then

Use Current Record In A New Form
Is there a way to select a record with one form then open another form with the selected record from the previous form set as the current record in the second form.
The problem is when opening a second form the current record is always set to the first record in the table.

Delete Current Record
Hello I would like to know how I can delete the current record in a recordset. I am using Access as database.

Dim RSone As Recordset

Set RSone = New ADODB.Recordset

With RSone
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With

strSQL = "SELECT * FROM HoraireBak ORDER BY DateVente"

Call CopyTable

Set RSone = Cn.Execute(strSQL)

'Some operations on the record

RSone.Delete <----------- Causes Error

I get the error '3251': Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Anybody has an idea to help me out please.

Thank you.

Sincerely,

Mark

Recordset Current Record?
I am using Access 2000. I am dealing with a recordset and want to know what the current record is, so that the navigation buttons can function properly. Either that or I need to know one record before BOF or EOF occurs.

I know there is a way to use the recordsetclone property of a form to do this, and possibly a way to clone the entire record set and accomplish the same thing, but I am using an unbound form, so the first option won't work, and I will be dealing with large numbers of records (up to 1 million), so I don't want to just make a clone of the whole recordset because I am worried about memory.

Basically, to sum it up, is there any way to know which record I'm at using just the 1 recordset that I am working with, sort of like a CurrentRecord property? I know this may seem like a newbie question but I haven't touched vb in about 6 months, so I apologize in advance.

ADO Update Current Record
Hello,

I am changing an old app from dao to ado. MS Access and using

Code:
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0 "
conn.Open (App.Path & "work.mdb")
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "PartsInventory", conn, adOpenDynamic, adLockOptimistic, adCmdTable

I have a previous, next, new and update button. the update may have a logic issue cause I use it to save a new record and update a current record depending what the command buttons caption. see update button code

Code:
Private Sub Update_Click()

If Text2 = "" Then
MsgBox "The Inventory Item Name field is blank. If you don't want to create a new record click Cancel.", vbInformation, "Empty Field"
Text2.SetFocus
Exit Sub
End If

If Text1 = "" Then
MsgBox "The Quantity field is blank. If you don't want to create a new record click Cancel.", vbInformation, "Empty Field"
Text1.SetFocus
Exit Sub
End If

rs.Requery

If Command1.Caption = "Cancel" Then
With rs
.AddNew 'adding new record
.Fields("PartName") = Text2.Text
.Fields("Quantity") = Text1.Text

.Update 'this updates the recordset etc.
End With

Command1.Caption = "Add New"
Command2.Enabled = True ' disable delete button

ElseIf Command1.Caption = "Add New" Then
Command2.Enabled = True ' enable delete button

conn.Execute "UPDATE PartsInventory SET PartName ='" & Text2 & "' , Quantity='" & Text1 & "'" 'since quantity is a numberic field then there isnt the extra "' around it

End If

Text3.SetFocus

End Sub

the new record button turns to cancel when clicked for making new records. That button if not making new stays as "add New" so this means you are just updating a current record. I need my execute statement to update the current record. the table only has two fields part name and quantity. Current statement makes all records the same as whatever was in the current record.

Locate The Current Record
Hi, I have coding like this, and it updates the all rows in the table , but i wanted to restrict it to only the current record, please help on how to locate the current record and do the update . Thanks.

Set db = CurrentDb()

Set rst = db.OpenRecordset("Table1", dbOpenDynaset)

Do While Not rst.EOF

stScode = rst![SCode]
stLcode = rst![LCode]

If Not IsNumeric(stLcode) Then

stsql2 = "update lTable1 set errormsg='Numeric Value error in Lcode' "

DoCmd.RunSQL (stsql2)

Exit Sub

End If

rst.MoveNext
Loop

Problem With Current Record
Hi all..may i know how to avoid this error during add new data into Database.

"Either BOF or EOF is True,or the current record has been deleted.
Requested operation requires a current record."

I'm so wonder..sometimes it will give me this error and sometimes not..

Thanks

Update Record And Add Value To Current Value
hi,
i want to update a record like:

"update produtos set stock='10' Where ID=( " 1 )

but i want add the current value to the new value for example
the current record have 2 now i want add 10 them the record will have 12
i try this
"update produtos set stock+'10' Where ID=( " 1 )
but this dont work, i already see this right function in other place but i dont remember where i know that is possible any one know the right sintax?
thanks for the help
bruno

How To Set Current Record In A Datagrid.
Greeting All,
I am stuck and need your help.
I have a datagrid that retrieve data from a db thru an ADO recordset.
The problem is that after I change a value in a cell and click the Save button then the change get save in the db. Right after I click the Save button, if I change my mind and go back to that cell right away to change to a different value then click Save, the change does not get saved.
But if I click other row then come back to the row I changed before, then I can change the value and save sucessfully.

Please let me know if I miss something.

Thanks,
ConKi.

Update Current Record
Hi

I have a label which contains an ID now what I want to do is
update the current record if the label contains a value and if not
insert a row....

The code related to the condition is below

VB Code:
If (lblScaleEventID > 0) Or (ObjRSprodidvalues.EOF) Then ObjRSprodidvalues.MovePrevious ObjRSprodidvalues.Update ElseIf (ObjRSprodidvalues.BOF) Then Load frmvoidopid frmvoidopid.Show vbModal Else ObjRSprodidvalues.MoveLast End If lblScaleEventID = ObjRSprodidvalues.Fields(0).Value

I receive an error ' EOF or BOF is true' or record is deleted...

Thanks for your help

Print Current Record
I finally got something workin in crystal report version 9. So my report has been created etc etc. But heres the problem Im having...it displays all the records in the database. I just want to run a print out of the report based on a user entering an ID number...is there a way to do this.

For instance a user is looking at ID # 200..and he wants to print out this record. When I run the report currently it shows all the records in the database...but all I want the printout to do is print ID #200.

Thanks,
Jon

Display Current Record??
I am using the ADO Data Control with access, i have a label where i want it to when the user goes from record 1 to record 2. Basically i wana be able to show the record that the user is on. Is there a way i can do it. I tried with alot of the commands but nothing seems to work

so something like this

You are on record [3] from [5]

Thx for you help.

Showing No Current Record ?
To,

I hope you will clear this doubt.

I want to enter names in the database using textbox.
if it is a first name it should update.if it is a second name which is not
similar to the first that record should be updated otherwise it should be
deleted.

The problem iam getting is not updating new record showing error has"No
current record"

my code:

Private Sub Cmd_updateClick()
Dim WS As Workspace
Dim DB As Database
Dim RS, RSL As Recordset
Dim E As String
Dim I As Integer
Set WS = DBEngine.Workspaces(0)
Set DB = WS.OpenDatabase("C:WINDOWSPATS.MDB")
Set RS = DB.OpenRecordset("EMP", dbOpenDynaset)

E = "select ENAME FROM EMP WHERE ENAME='" & Text1.Text & "'"
Set RSL = DB.OpenRecordset(E, dbOpenDynaset)
RSL.MoveLast
If I = 1 Then
Data1.Recordset.Update
Else
Data1.Recordset.Delete
End If
End Sub

Private Sub Cmd_addnewClick()
Data1.Recordset.AddNew
Text1.Text = " "
Text1.SetFocus
End Sub

Thanks.
brushindia@yahoo.com

Help!!! How Do You Find Current Record Value?
Newbie Here)I have an access database that I am using in VB. I need to return the value of the access database ID# to the program. In other words... I have 30 records, which are actually questions. The program needs to know which question or (Record) is selected. In access, the ID field is set to autonumber and tells which record is being viewed.

How do I do this in VB. I have exhausted my books. They all say how to move to previous, last, next, first, etc., but do not discuss how to show the current record ID.

Is it something like...

dim CurrentRecord as variant
CurrentRecord = data1.recordset.fields("Id")

Trying to figure out which record the database is looking at. Is it record 1 of 30 or record 26 of 30. How do I return this value to my program????

Please offer help.

Thanks,
Sal

View Current Record..
Does anyone know if there is a way to write a record to a table, through a form then hit a command button that writes to the table and have it automatically pop a message box or another form or something that would show me the record number (created automatically) of the entry?

Currently, I fill in the form and hit the "update" command with the following code. It doesn't "do" anything per say, except write to the table.... my problem is that I need to know the number it assigns to the record instantaneously. Any ideas anyone?

Private Sub cmdAdd_Click()
On Error GoTo AddErr
datPrimaryRS.Recordset.AddNew
frmSort.Show
Exit Sub
AddErr:
MsgBox Err.Description
End Sub

Kristin

BACKCOLOR OF CURRENT RECORD
Hi All,

How do I change the back color of the current record in a datagrid as I move from one record to the other.

Any help/code would be appreciated.

Thanks.

Emmanuel.

Print Current Record???
Hi everyone, any help much appreciated.
I have a form in VB6, all items in the form are linked to an ADO, i want to insert a print function to the form which will print the current record displayed, the record itself is saved an an Access DB.
At current i have no code, i have created a report in Access which will display the records, how ever it displays all the records and each record is on a seperate page, im afraid that adding a print function with this will have it print out 298 pages when i only want the one im viewing in my VB program...
Any help with this will be very much appreciated

To Know The Current Record Number
i want a label to write the current record number on my application. is there any function for that? thanks

How To Printout Current Record.
Hi friend,

I have a question about how to print out current record? I used Ms access to create a table which is bugsheet and I use VB to create the interface. after I enter the record. I want to print out only the current record that include the bugID. programmer, describtion..... Also, I hope the printout looks like .doc file.(see attachment)
how can I do it?

thank you

How Can I Update The Current Record ADO
Hello
dear
i am working on the Database but now these day i am facing probelm of Updating the Current record through ADO
i enter the fellowing code but when i exectue i recive an error
i am using UDL and jet 4.0 provider
how can i solve this problem i shall be very thankful to all of you

dim cn as Adodb.connection
Dim rs as ADODB.Recordset
dim cmdup as adodb.commad
dim updateP as adodb.parameter
dim Sqlup as string
set cn = new adobe.connection
cn.connectionstring = "file name = d:myudl.udl"
cn.open

set cmdup = new adodb.command
cmduup.activeconnection = cn
sql = "select * from Emp"
sql = sql &"where empno = ?"
set updateP = cmdup.parameter(,adinteger,input,3)
cmdup.parameter.append updateP
set rs = new adodb.recordset
rs = cmdup.execute

rs!name = txtname.text
rs.update
there are some mistakes in code because at that time i am working on the Computer in which there is no VB
ok sorry for that mistakes

Tamoor Ahmad
OCP DBA