ms access update record problem

Printable View

Mar 9th, 2008, 11:18 AM
skaswani

ms access update record problem

Dear Friends,

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

error : variable not found

help me please

thanks,

Code:

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
Mar 9th, 2008, 12:16 PM
Hack

Re: ms access update record problem

What variable isn't found?

Have you predeclared it?
Mar 9th, 2008, 12:43 PM
skaswani

Re: ms access update record problem

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
Mar 9th, 2008, 01:10 PM
skaswani

1 Attachment(s)

Re: ms access update record problem

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

regards,
Mar 9th, 2008, 01:45 PM
chunk

Re: ms access update record problem

i tried ur project and made these changes

Code:

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

Quote:

Run Time error 3251

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype
Mar 9th, 2008, 01:49 PM
skaswani

Re: ms access update record problem

hmmm
now what to do with new error?
Mar 9th, 2008, 01:55 PM
si_the_geek

Re: ms access update record problem

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:

Code:

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:

Code:

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)
Mar 9th, 2008, 01:56 PM
chunk

Re: ms access update record problem

hehehe

Code:

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

made this change

and got new error.

Quote:

Run time 3265
Mar 9th, 2008, 02:02 PM
chunk

Re: ms access update record problem

read it
http://support.microsoft.com/kb/q197528/
Mar 9th, 2008, 02:05 PM
skaswani

Re: ms access update record problem

error 3251 Current Recordset doesnot support updating, this may be limitation of provider, or of the selected locktype
Mar 9th, 2008, 02:09 PM
si_the_geek

Re: ms access update record problem

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).
Mar 9th, 2008, 02:11 PM
chunk

Re: ms access update record problem

i just wrote the error number and description and post the article, sorry for that geek
Mar 9th, 2008, 02:14 PM
skaswani

Re: ms access update record problem

here is my code again.

Code:

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
Mar 9th, 2008, 02:22 PM
chunk

Re: ms access update record problem

Quote:

Originally Posted by skaswani

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
Mar 9th, 2008, 02:28 PM
skaswani

Re: ms access update record problem

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,
Mar 9th, 2008, 02:28 PM
chunk

1 Attachment(s)

Re: ms access update record problem

i think it is done, test urself

Code:

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
Mar 9th, 2008, 02:36 PM
chunk

Re: ms access update record problem

in above work you just keep this line

Code:

MsgBox "Saved!"

after this

Code:

rsRecordSet.Update

similar to

Code:

Private Sub save_Click() Call UpdateRecord rsRecordSet.Update MsgBox "Saved!" End Sub
Mar 9th, 2008, 05:14 PM
anhn

Re: ms access update record problem

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, "#,###")