ms access update record problem

[skaswani]

Dear Friends,


i am having problem with Private Sub save_Click() module,

error : variable not found


help me please

thanks,

Option Explicit
Public WithEvents Cn As ADODB.Connection
Public WithEvents rsRecordSet As ADODB.Recordset
Public WithEvents rsRecordSet2 As ADODB.Recordset
Public WithEvents rstempcode As ADODB.Recordset

Private Sub cancel_Click()
Go.Enabled = True
save.Enabled = False
cancel.Enabled = False
Go.SetFocus
End Sub

Private Sub Go_Click()
Go.Enabled = False
cancel.Enabled = True
save.Enabled = True

Set Cn = New ADODB.Connection
Cn.CursorLocation = adUseClient
    
  
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= C:\SCN-ACCESS\SCN-2.mdb"

Set rsRecordSet = New ADODB.Recordset
 rsRecordSet.CursorType = adOpenDynamic

rsRecordSet.Open "SELECT * From SCN where  ReferenceNo = " & refno.Text, Cn, adUseClient




If rsRecordSet.RecordCount = 1 Then

ref_long.Text = rsRecordSet!Reference_Long
rem_name.Text = rsRecordSet!rem_name
ben_name.Text = rsRecordSet!ben_name
ben_add.Text = rsRecordSet!Ben_address & ""
rem_date.Text = Format(rsRecordSet!Transactiondate, "dd-mmm-yy")
amt.Text = Format(rsRecordSet!amount, "#,###,###,###,###")
CNIC.Text = rsRecordSet!ben_cnic & ""
reim_date.Text = Format(rsRecordSet!reim_date, "dd-mmm-yy") & ""
pay_date.Text = Format(rsRecordSet!pay_date, "dd-mmm-yy") & ""
bcode.Text = rsRecordSet!branchcode & ""

Else
 
 
 
rem_name.Text = ""
ben_name.Text = ""
ben_add.Text = ""
rem_date.Text = ""
amt.Text = ""
CNIC.Text = ""
reim_date.Text = ""
pay_date.Text = ""
bcode.Text = ""
    MsgBox "No Record Found"


End If

   
End Sub

Private Sub Quit_Click()
End
End Sub

Private Sub save_Click()

rsRecordSet.Update pay_date, pay_date.Text
rsRecordSet.Update Ben_address, ben_add.Text
rsRecordSet.Update ben_cnic, CNIC.Text
rsRecordSet.Update branchcode, bcode.Text
rsRecordSet.Update reim_date, reim_date.Text
If pay_date.Text = "" Then
rsRecordSet.Update Status, "Paid"
Else
rsRecordSet.Update Status, "unPaid"
End If


MsgBox "Saved!"
End Sub

[Hack]

What variable isn't found?

Have you predeclared it?

[skaswani]

Data base variables

like

Ben_address
ben_cnic
pay_date
branchcode
reim_date
Status



but.,. when i Query on GO Button it show me all values extracted by those variables from database

[skaswani]

i am attaching my files, maybe this can help u to understand my prob!

regards,

[chunk]

i tried ur project and made these changes

Private Sub save_Click()

rsRecordSet.Update rsRecordSet.Fields("pay_date"), pay_date.Text
rsRecordSet.Update rsRecordSet.Fields("Ben_address"), ben_add.Text
rsRecordSet.Update rsRecordSet.Fields("ben_cnic"), CNIC.Text
rsRecordSet.Update rsRecordSet.Fields("branchcode"), bcode.Text
rsRecordSet.Update rsRecordSet.Fields("reim_date"), reim_date.Text
If pay_date.Text = "" Then
rsRecordSet.Update rsRecordSet.Fields("Status"), "Paid"
Else
rsRecordSet.Update rsRecordSet.Fields("Status"), "unPaid"
End If


MsgBox "Saved!"
End Sub

now i get a new error

Run Time error 3251

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

[skaswani]

hmmm
now what to do with new error?

[si_the_geek]

To use the .Update method in that way, you should specify the field name (not a variable with the name, or a Field object), eg:

rsRecordSet.Update "pay_date", pay_date.Text

..but I don't see why you would choose to do it that way, rather than the standard (faster, and more atomic) method of setting the fields, followed by a simple call to .Update, eg:

rsRecordSet.Fields("pay_date").Value = pay_date.Text
.. (same for other fields)
rsRecordSet.Update

I also don't see why you have declared the variables using "WithEvents", as you aren't making use of that.


The recordset won't support updating - because it was opened as read-only. For more information, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[chunk]

hehehe

rsRecordSet.Open "SELECT * From SCN where  ReferenceNo = " & refno.Text, Cn, adOpenKeyset, adLockOptimistic

made this change

and got new error.

Run time 3265

[chunk]

read it
http://support.microsoft.com/kb/q197528/

[skaswani]

error 3251 Current Recordset doesnot support updating, this may be limitation of provider, or of the selected locktype

[si_the_geek]

What does your rsRecordSet.Open line look like?


chunk, that article is not relevant (it is explicitly for SQL Server, and this is Access - the issue that the article describes cannot occur with Access).

[chunk]

i just wrote the error number and description and post the article, sorry for that geek

[skaswani]

here is my code again.

Option Explicit
Public Cn As ADODB.Connection
Public rsRecordSet As ADODB.Recordset
Public rsRecordSet2 As ADODB.Recordset
Public rstempcode As ADODB.Recordset

Private Sub cancel_Click()
Go.Enabled = True
save.Enabled = False
cancel.Enabled = False
Go.SetFocus
End Sub

Private Sub Exit_Click(Index As Integer)
End
End Sub

Private Sub Go_Click()
Go.Enabled = False
cancel.Enabled = True
save.Enabled = True

Set Cn = New ADODB.Connection
Cn.CursorLocation = adUseClient
    
  
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= " & App.Path & "\SCN-2.mdb"

Set rsRecordSet = New ADODB.Recordset
 rsRecordSet.CursorType = adOpenDynamic

rsRecordSet.Open "SELECT * From SCN where  ReferenceNo = " & refno.Text, Cn, adUseClient




If rsRecordSet.RecordCount = 1 Then

ref_long.Text = rsRecordSet!Reference_Long
rem_name.Text = rsRecordSet!rem_name
ben_name.Text = rsRecordSet!ben_name
ben_add.Text = rsRecordSet!Ben_address & ""
rem_date.Text = Format(rsRecordSet!Transactiondate, "dd-mmm-yy")
amt.Text = Format(rsRecordSet!amount, "#,###,###,###,###")
CNIC.Text = rsRecordSet!ben_cnic & ""
reim_date.Text = Format(rsRecordSet!reim_date, "dd-mmm-yy") & ""
pay_date.Text = Format(rsRecordSet!pay_date, "dd-mmm-yy") & ""
bcode.Text = rsRecordSet!branchcode & ""

Else
 
 
 
rem_name.Text = ""
ben_name.Text = ""
ben_add.Text = ""
rem_date.Text = ""
amt.Text = ""
CNIC.Text = ""
reim_date.Text = ""
pay_date.Text = ""
bcode.Text = ""
    MsgBox "No Record Found"


End If

   
End Sub

Private Sub Quit_Click()
End
End Sub

Private Sub save_Click()

'rsRecordSet.Update rsRecordSet.Fields("pay_date").Value, pay_date.Text


rsRecordSet.Fields("pay_date").Value = pay_date.Text
rsRecordSet.Fields("Ben_address").Value = ben_add.Text
rsRecordSet.Fields("ben_cnic").Value = CNIC.Text
rsRecordSet.Fields("branchcode").Value = bcode.Text
rsRecordSet.Fields("reim_date").Value = reim_date.Text
If pay_date.Text = "" Then
rsRecordSet.Fields("Status").Value = "Paid"
Else
rsRecordSet.Fields("Status").Value = "unPaid"


rsRecordSet.Update

End If




MsgBox "Saved!"
End Sub


Private Sub Form_QueryUnload(cancel As Integer, UnloadMode As Integer)
  cancel = 1
  MsgBox "Press Exit Menu to Exit!"
End Sub

[chunk]

error 3251 Current Recordset doesnot support updating, this may be limitation of provider, or of the selected locktype

read post #8 and also read whole thread calmly

[skaswani]

oh, thank you,,

actully i have read that post, did'nt change in my code

thank alot,

i'll be back if i have any other problem!

Regards,

[chunk]

i think it is done, test urself

Option Explicit
Public Cn As ADODB.Connection
Public rsRecordSet As ADODB.Recordset
Public rsRecordSet2 As ADODB.Recordset
Public rstempcode As ADODB.Recordset

Private Sub cancel_Click()
Go.Enabled = True
save.Enabled = False
cancel.Enabled = False
Go.SetFocus
End Sub

Private Sub Exit_Click(Index As Integer)
End
End Sub

Private Sub Go_Click()
Go.Enabled = False
cancel.Enabled = True
save.Enabled = True

Set Cn = New ADODB.Connection
Cn.CursorLocation = adUseClient
    
  
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= " & App.Path & "\SCN-2.mdb"

Set rsRecordSet = New ADODB.Recordset
 rsRecordSet.CursorType = adOpenDynamic

rsRecordSet.Open "SELECT * From SCN where  ReferenceNo = " & refno.Text, Cn, adOpenKeyset, adLockOptimistic




If rsRecordSet.RecordCount = 1 Then

Call ShowRecord

Else
 
rem_name.Text = ""
ben_name.Text = ""
ben_add.Text = ""
rem_date.Text = ""
amt.Text = ""
CNIC.Text = ""
reim_date.Text = ""
pay_date.Text = ""
bcode.Text = ""
    MsgBox "No Record Found"

End If

   
End Sub

Private Sub Quit_Click()
End
End Sub

Private Sub UpdateRecord()

'rsRecordSet.Update rsRecordSet.Fields("pay_date").Value, pay_date.Text


rsRecordSet!pay_date = pay_date.Text
rsRecordSet!Ben_address = ben_add.Text
rsRecordSet!ben_cnic = CNIC.Text
rsRecordSet!branchcode = bcode.Text
rsRecordSet!reim_date = reim_date.Text
If pay_date.Text = "" Then
rsRecordSet!Status = "Paid"
Else
rsRecordSet!Status.Value = "unPaid"
End If





MsgBox "Saved!"
End Sub


Private Sub Form_QueryUnload(cancel As Integer, UnloadMode As Integer)
  cancel = 1
  MsgBox "Press Exit Menu to Exit!"
End Sub

   
   
Private Sub ShowRecord()
ref_long.Text = rsRecordSet!Reference_Long
rem_name.Text = rsRecordSet!rem_name
ben_name.Text = rsRecordSet!ben_name
ben_add.Text = rsRecordSet!Ben_address & ""
rem_date.Text = Format(rsRecordSet!Transactiondate, "dd-mmm-yy")
amt.Text = Format(rsRecordSet!amount, "#,###,###,###,###")
CNIC.Text = rsRecordSet!ben_cnic & ""
reim_date.Text = Format(rsRecordSet!reim_date, "dd-mmm-yy") & ""
pay_date.Text = Format(rsRecordSet!pay_date, "dd-mmm-yy") & ""
bcode.Text = rsRecordSet!branchcode & ""
End Sub

Private Sub save_Click()
Call UpdateRecord
rsRecordSet.Update
End Sub

[chunk]

in above work you just keep this line

MsgBox "Saved!"

after this

rsRecordSet.Update

similar to

Private Sub save_Click()
Call UpdateRecord
rsRecordSet.Update
MsgBox "Saved!"
End Sub

[anhn]

One offline small and basic issue with Format() :

You don't need to give that many #'s in this line:
amt.Text = Format(rsRecordSet!amount, "#,###,###,###,###")

You can simply have:
amt.Text = Format(rsRecordSet!amount, "#,###")