current record set does not support upadating

Printable View

can anyone help me with this i'v been trying to fix this but i can't. ive already done the solutions posted in forums but still having error...
the error is the .fields(0) = txtappid down... please help me... thank you in advance.

VB Code:

Private Sub Form_Load()
tbInquiry.Open "Select * from tbinquiry", cn, adOpenKeyset, adLockOptimistic
CallFields
End Sub
Private Sub LV1_Click()
str = "Select * from tbinquiry where ApplicantID like '" & LV1.SelectedItem.Text & "'"
Set tbInquiry = cn.Execute(str)
 
With tbInquiry
    If .BOF = False And .EOF = False Then
        txtFind = .Fields(3) & " " & .Fields(1) & ", " & .Fields(2)
        
    End If
End With
End Sub 
 
Private Sub cmdSave_Click()
Frame5.Visible = True
Frame4.Visible = False
txtSummary.Text = "(New Entry Saved. CLick Next To Proceed."
 
With tbInquiry
            .Fields(0) = txtAppID
    .Fields(1) = txtAppInfo(0)
    .Fields(2) = txtAppInfo(1)
    .Fields(3) = txtAppInfo(2)
    .Fields(4) = txtAppInfo(3)
    .Fields(5) = txtAppInfo(4)
    .Fields(6) = cmbTFA.Text
    '.Fields(7) =
        'AddFields
        .Update
         MsgBox "Edit successfully saved to the record.", vbInformation
End With
End Sub
 
Private Sub Form_Load()
str = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
Set tbInquiry = cn.Execute(str)
 
With tbInquiry
    If .BOF = False And .EOF = False Then
        txtAppID = .Fields(0)
        txtAppInfo(0) = .Fields(1)
        txtAppInfo(1) = .Fields(2)
        txtAppInfo(2) = .Fields(3)
        txtAppInfo(3) = .Fields(4)
        txtAppInfo(4) = .Fields(5)
        cmbTFA.Text = .Fields(6)
        
    End If
End With

Feb 6th, 2007, 11:40 AM
danasegarane

Re: current record set does not support upadating
This will solve your problem
VB Code:
```
tbInquiry.Open "Select * from tbinquiry", cn, [B]adOpenDynamic[/B], adLockOptimistic
```
Feb 6th, 2007, 11:42 AM
si_the_geek

Re: current record set does not support upadating

Don't use = cn.Execute(str) to open a recordset, the correct method is to use the .Open method as at the top of your code... the error will then magically disappear.
Feb 6th, 2007, 11:47 AM
mheiy

Re: current record set does not support upadating

ive already change that to adOpenDynamic but still have the same error.
Feb 6th, 2007, 11:51 AM
si_the_geek

Re: current record set does not support upadating
The problem is that lines like this:
VB Code:
```
Set tbInquiry = cn.Execute(str)
```
..need to be replaced with this:
VB Code:
```
tbInquiry.Open str, cn, adOpenKeyset, adLockOptimistic
```
Feb 6th, 2007, 11:58 AM
mheiy

Re: current record set does not support upadating

it says that the connection is already open
Feb 6th, 2007, 12:02 PM
mheiy

Re: current record set does not support upadating

if i remove the connection to the form load and replace the tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic im having a error of command text was not set for the command object
Feb 6th, 2007, 12:05 PM
danasegarane

Re: current record set does not support upadating

I think you have post the Full Code

Re: current record set does not support upadating

in form 1 this is my code

VB Code:

Dim ls As ListItem
Dim inq As String ' for inquiry
 
 
Private Sub CallFields()
With tbInquiry
    LV1.ListItems.Clear
    While .EOF = False
        Set ls = LV1.ListItems.Add(, , .Fields(0))
        ls.SubItems(1) = .Fields(3) & " " & .Fields(1) & "," & .Fields(2)
        .MoveNext
    Wend
End With
End Sub
 
Private Sub cmdCancel_Click()
ans = MsgBox("Are you sure you want to cancel this?", vbYesNo + vbQuestion, " Titus School of Multimedia")
    If ans = vbYes Then
      Unload Me
    End If
End Sub
 
Private Sub cmdSelect_Click()
'If LV1.SelectedItem = False Then
  ' MsgBox "Select first before you can move to another.", vbInformation, "Titus School of Multimedia"
'Else
Unload Me
frmEditApplicant.Show
'End If
 
End Sub
 
Private Sub Form_Load()
'tbInquiry.Open "Select * from tbinquiry", cn, adOpenDynamic, adLockOptimistic
CallFields
End Sub
Private Sub LV1_Click()
 
inq = "Select * from tbinquiry where ApplicantID like '" & LV1.SelectedItem.Text & "'"
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic
 
With tbInquiry
    If .BOF = False And .EOF = False Then
        txtFind = .Fields(3) & " " & .Fields(1) & ", " & .Fields(2)
        
    End If
End With
End Sub
 
'Private Sub Form_Activate()
'CallFields
'End Sub
Private Sub txtFind_Change()
inq = "Select * from tbinquiry Where LastName like '" & txtFind & "%'"
Set tbInquiry = cn.Execute(inq)
 
If tbInquiry.BOF = True And tbInquiry.EOF = True Then
    txtFind = Left(txtFind, Len(txtFind) - 0)
    txtFind.SelStart = Len(txtFind)
Else
    CallFields
End If
End Sub

and in form 2

VB Code:

Option Explicit
Public State                As FormState 'Variable used to determine on how the form used
 
Dim inq As String
 
Private Sub cmdCancel_Click()
ans = MsgBox("Are you sure you want to cancel this?", vbYesNo + vbQuestion, " Titus School of Multimedia")
    If ans = vbYes Then
      Unload Me
    End If
End Sub
 
Private Sub cmdnext_Click()
If CheckTextBox(txtAppID, "You cannot move to this part unless you have created New Applicant ID.") = False Then
    StartTxt txtAppID
ElseIf CheckTextBox(txtAppInfo(0), "Invalid 'FIRST NAME' value. This field is required, please enter a value.") = False Then
    StartTxt txtAppInfo(0)
ElseIf CheckTextBox(txtAppInfo(1), "Invalid 'Middle Name' value. This field is required, please enter a value.") = False Then
    StartTxt txtAppInfo(1)
ElseIf CheckTextBox(txtAppInfo(2), "Invalid 'LAST NAME' value. This field is required, please enter a value.") = False Then
    StartTxt txtAppInfo(2)
ElseIf CheckTextBox(txtAppInfo(3), "Invalid 'ADDRESS' value. This field is required, please enter a value.") = False Then
    StartTxt txtAppInfo(3)
ElseIf CheckTextBox(txtAppInfo(4), "Invalid 'CONTACT NO' value. This field is required, please enter a value.") = False Then
    StartTxt txtAppInfo(4)
Else
    Frame1.Visible = False
    Frame4.Visible = True
    
    'tbInquiry.MoveFirst
    'tbInquiry.Find "ApplicantID = '" & txtAppID & "'"
    'show summary
       txtSummary.Text = "Applicant ID: " & txtAppID
       txtSummary.Text = txtSummary.Text & vbNewLine & "First Name: " & txtAppInfo(0)
       txtSummary.Text = txtSummary.Text & vbNewLine & "Middle Name: " & txtAppInfo(1)
       txtSummary.Text = txtSummary.Text & vbNewLine & "Last Name: " & txtAppInfo(2)
       txtSummary.Text = txtSummary.Text & vbNewLine & "Home Address: " & txtAppInfo(3)
       txtSummary.Text = txtSummary.Text & vbNewLine & "Contact Number: " & txtAppInfo(4)
       txtSummary.Text = txtSummary.Text & vbNewLine & "Tuition Fee Assistance: " & cmbTFA.Text
       txtSummary.Text = txtSummary.Text & vbNewLine & "Creation Date: " & FormatDateTime(Now, vbGeneralDate)
End If
End Sub
 
Private Sub cmdSave_Click()
Frame5.Visible = True
Frame4.Visible = False
txtSummary.Text = "(New Entry Saved. CLick Next To Proceed."
 
With tbInquiry
            .Fields(0) = txtAppID
    .Fields(1) = txtAppInfo(0)
    .Fields(2) = txtAppInfo(1)
    .Fields(3) = txtAppInfo(2)
    .Fields(4) = txtAppInfo(3)
    .Fields(5) = txtAppInfo(4)
    .Fields(6) = cmbTFA.Text
    '.Fields(7) =
        'AddFields
        .Update
         MsgBox "Edit successfully saved to the record.", vbInformation
End With
End Sub
 
Private Sub Form_Load()
inq = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic
 
With tbInquiry
    If .BOF = False And .EOF = False Then
        txtAppID = .Fields(0)
        txtAppInfo(0) = .Fields(1)
        txtAppInfo(1) = .Fields(2)
        txtAppInfo(2) = .Fields(3)
        txtAppInfo(3) = .Fields(4)
        txtAppInfo(4) = .Fields(5)
        cmbTFA.Text = .Fields(6)
        
    End If
End With
 
'txtAppInfo(0).SetFocus
Frame4.Visible = False
Frame5.Visible = False
txtAppID.Enabled = False
cmdprevious.Enabled = False
End Sub

Feb 6th, 2007, 12:10 PM
mheiy

Re: current record set does not support upadating

yes im done that but still having an error.. sorry im just a beginner
Feb 6th, 2007, 12:12 PM
si_the_geek

Re: current record set does not support upadating

When you post code please put it inside VBCode tags so it is displayed in a more readable way - either using the button in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [vbcode] 'code here [/vbcode]

(I have edited your posts above)

The problem seems to be that you have commented out the line in Form1's Form_Load that actually loads the data - which then makes CallFields fail (as the recordset is not set to anything).
Feb 6th, 2007, 12:15 PM
mheiy

Re: current record set does not support upadating

[Highlight=VB]Option Explicit
Public State As FormState 'Variable used to determine on how the form used

Dim inq As String

Private Sub cmdCancel_Click()
ans = MsgBox("Are you sure you want to cancel this?", vbYesNo + vbQuestion, " Titus School of Multimedia")
If ans = vbYes Then
Unload Me
End If
End Sub

Private Sub cmdnext_Click()
If CheckTextBox(txtAppID, "You cannot move to this part unless you have created New Applicant ID.") = False Then
StartTxt txtAppID
ElseIf CheckTextBox(txtAppInfo(0), "Invalid 'FIRST NAME' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(0)
ElseIf CheckTextBox(txtAppInfo(1), "Invalid 'Middle Name' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(1)
ElseIf CheckTextBox(txtAppInfo(2), "Invalid 'LAST NAME' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(2)
ElseIf CheckTextBox(txtAppInfo(3), "Invalid 'ADDRESS' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(3)
ElseIf CheckTextBox(txtAppInfo(4), "Invalid 'CONTACT NO' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(4)
Else
Frame1.Visible = False
Frame4.Visible = True

'tbInquiry.MoveFirst
'tbInquiry.Find "ApplicantID = '" & txtAppID & "'"
'show summary
txtSummary.Text = "Applicant ID: " & txtAppID
txtSummary.Text = txtSummary.Text & vbNewLine & "First Name: " & txtAppInfo(0)
txtSummary.Text = txtSummary.Text & vbNewLine & "Middle Name: " & txtAppInfo(1)
txtSummary.Text = txtSummary.Text & vbNewLine & "Last Name: " & txtAppInfo(2)
txtSummary.Text = txtSummary.Text & vbNewLine & "Home Address: " & txtAppInfo(3)
txtSummary.Text = txtSummary.Text & vbNewLine & "Contact Number: " & txtAppInfo(4)
txtSummary.Text = txtSummary.Text & vbNewLine & "Tuition Fee Assistance: " & cmbTFA.Text
txtSummary.Text = txtSummary.Text & vbNewLine & "Creation Date: " & FormatDateTime(Now, vbGeneralDate)
End If
End Sub

Private Sub cmdSave_Click()
Frame5.Visible = True
Frame4.Visible = False
txtSummary.Text = "(New Entry Saved. CLick Next To Proceed."

With tbInquiry
.Fields(0) = txtAppID
.Fields(1) = txtAppInfo(0)
.Fields(2) = txtAppInfo(1)
.Fields(3) = txtAppInfo(2)
.Fields(4) = txtAppInfo(3)
.Fields(5) = txtAppInfo(4)
.Fields(6) = cmbTFA.Text
'.Fields(7) =
'AddFields
.Update
MsgBox "Edit successfully saved to the record.", vbInformation
End With
End Sub

Private Sub Form_Load()
inq = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic

With tbInquiry
If .BOF = False And .EOF = False Then
txtAppID = .Fields(0)
txtAppInfo(0) = .Fields(1)
txtAppInfo(1) = .Fields(2)
txtAppInfo(2) = .Fields(3)
txtAppInfo(3) = .Fields(4)
txtAppInfo(4) = .Fields(5)
cmbTFA.Text = .Fields(6)

End If
End With

'txtAppInfo(0).SetFocus
Frame4.Visible = False
Frame5.Visible = False
txtAppID.Enabled = False
cmdprevious.Enabled = False
End Sub
Feb 6th, 2007, 12:18 PM
danasegarane

Re: current record set does not support upadating
Put this before the opening of the all tbInquiry
VB Code:
```
set tbInquiry=Nothing
```
Feb 6th, 2007, 12:19 PM
mheiy

Re: current record set does not support upadating

if i commented it out it will have an error point to the tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic of lV1_Click saying that Operation is not allowed when the object is open.
Feb 6th, 2007, 12:20 PM
si_the_geek

Re: current record set does not support upadating

That is terrible advice I'm afraid - you should never set an object variable to Nothing before it has been closed properly. It can cause memory issues, and can cause damage - in this case database locking and/or corruption.
Feb 6th, 2007, 12:20 PM
mheiy

Re: current record set does not support upadating

where i will put this
[Highlight=VB]
set tbInquiry=Nothing
Feb 6th, 2007, 12:21 PM
si_the_geek

Re: current record set does not support upadating
Ah yes, in that case add these two lines before that .Open line:
VB Code:
```
tbInquiry.Close
Set tbInquiry=Nothing
```
ps: you aren't using the tags correctly. ;)
[vbcode] 'code here [/vbcode]

Re: current record set does not support upadating

VB Code:

Option Explicit
Public State As FormState 'Variable used to determine on how the form used
 
Dim inq As String
 
Private Sub cmdCancel_Click()
ans = MsgBox("Are you sure you want to cancel this?", vbYesNo + vbQuestion, " Titus School of Multimedia")
If ans = vbYes Then
Unload Me
End If
End Sub
 
Private Sub cmdnext_Click()
If CheckTextBox(txtAppID, "You cannot move to this part unless you have created New Applicant ID.") = False Then
StartTxt txtAppID
ElseIf CheckTextBox(txtAppInfo(0), "Invalid 'FIRST NAME' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(0)
ElseIf CheckTextBox(txtAppInfo(1), "Invalid 'Middle Name' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(1)
ElseIf CheckTextBox(txtAppInfo(2), "Invalid 'LAST NAME' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(2)
ElseIf CheckTextBox(txtAppInfo(3), "Invalid 'ADDRESS' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(3)
ElseIf CheckTextBox(txtAppInfo(4), "Invalid 'CONTACT NO' value. This field is required, please enter a value.") = False Then
StartTxt txtAppInfo(4)
Else
Frame1.Visible = False
Frame4.Visible = True
 
'tbInquiry.MoveFirst
'tbInquiry.Find "ApplicantID = '" & txtAppID & "'"
'show summary
txtSummary.Text = "Applicant ID: " & txtAppID
txtSummary.Text = txtSummary.Text & vbNewLine & "First Name: " & txtAppInfo(0)
txtSummary.Text = txtSummary.Text & vbNewLine & "Middle Name: " & txtAppInfo(1)
txtSummary.Text = txtSummary.Text & vbNewLine & "Last Name: " & txtAppInfo(2)
txtSummary.Text = txtSummary.Text & vbNewLine & "Home Address: " & txtAppInfo(3)
txtSummary.Text = txtSummary.Text & vbNewLine & "Contact Number: " & txtAppInfo(4)
txtSummary.Text = txtSummary.Text & vbNewLine & "Tuition Fee Assistance: " & cmbTFA.Text
txtSummary.Text = txtSummary.Text & vbNewLine & "Creation Date: " & FormatDateTime(Now, vbGeneralDate)
End If
End Sub
 
Private Sub cmdSave_Click()
Frame5.Visible = True
Frame4.Visible = False
txtSummary.Text = "(New Entry Saved. CLick Next To Proceed."
 
With tbInquiry
.Fields(0) = txtAppID
.Fields(1) = txtAppInfo(0)
.Fields(2) = txtAppInfo(1)
.Fields(3) = txtAppInfo(2)
.Fields(4) = txtAppInfo(3)
.Fields(5) = txtAppInfo(4)
.Fields(6) = cmbTFA.Text
'.Fields(7) =
'AddFields
.Update
MsgBox "Edit successfully saved to the record.", vbInformation
End With
End Sub
 
Private Sub Form_Load()
inq = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
[B]Set tbInquiry = Nothing 'This will close the previously opend Connection[/B]
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic
 
With tbInquiry
If .BOF = False And .EOF = False Then
txtAppID = .Fields(0)
txtAppInfo(0) = .Fields(1)
txtAppInfo(1) = .Fields(2)
txtAppInfo(2) = .Fields(3)
txtAppInfo(3) = .Fields(4)
txtAppInfo(4) = .Fields(5)
cmbTFA.Text = .Fields(6)
 
End If
End With
 
'txtAppInfo(0).SetFocus
Frame4.Visible = False
Frame5.Visible = False
txtAppID.Enabled = False
cmdprevious.Enabled = False
End Sub

Feb 6th, 2007, 12:25 PM
mheiy

Re: current record set does not support upadating

in the second form in having on error [CODE]

Private Sub Form_Load()

tbInquiry.Close
Set tbInquiry = Nothing

inq = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic

Re: current record set does not support upadating

It should be like this

VB Code:

Private Sub Form_Load()
[B]Set tbInquiry = Nothing[/B]
inq = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic

Feb 6th, 2007, 12:28 PM
si_the_geek

Re: current record set does not support upadating
What error?

By the way - you need to put tags before and after the code, eg:
[vbcode] 'code here [/vbcode]
Quote:
Originally Posted by danasegarane

VB Code:

Private Sub Form_Load()
inq = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
[B]Set tbInquiry = Nothing 'This will close the previously opend Connection[/B]
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic
Please read my post above - your advice is not only bad, but causes errors.

That line does not do ANYTHING with the connection. It doesn't do anything useful with the recordset either - the recordset still exists, it is just that the variable is no longer linked to it.
Feb 6th, 2007, 12:31 PM
mheiy

Re: current record set does not support upadating

wow.... thank you so much... thank ou for the patience... its running now... does it same with deleting

Re: current record set does not support upadating

Dear SI,
IF the Recordset is not Open and If we tries the
.Close it will Give Error.Instead we can use this method

VB Code:

inq = "Select * from tbinquiry where ApplicantID like '" & frmApplicantDetails.lvList.SelectedItem.Text & "' "
If tbInquiry.State = 1 Then tblInquire.Close
tbInquiry.Open inq, cn, adOpenKeyset, adLockOptimistic
Set tbInquiry = Nothing 'This will close the previously opend Connection

Feb 6th, 2007, 12:32 PM
mheiy

Re: current record set does not support upadating

thank you so much guys... you really help me a lot
Feb 6th, 2007, 12:34 PM
mheiy

Re: current record set does not support upadating

wait im having the problem again when im editing it always the first record is shown even if i click the last record the first record will be show...
Feb 6th, 2007, 12:53 PM
mheiy

Re: current record set does not support upadating

im having an error when closing it
Feb 6th, 2007, 12:56 PM
danasegarane

Re: current record set does not support upadating

What is the Errorr Msg?
Feb 6th, 2007, 01:22 PM
mheiy

Re: current record set does not support upadating

Operartion are not allowed because the object are closed

Re: current record set does not support upadating

how can i add an applicant id number with a letter like this one APP-0001

VB Code:

With tbInquiry
    If .RecordCount = 0 Then
        txtAppID = "00000001"
    Else
        .MoveLast
        txtAppID = .Fields(0) + 1
'        txtAppInfo(0).SetFocus
    End If
End With

Feb 6th, 2007, 01:25 PM
danasegarane

Re: current record set does not support upadating

Which Line you are getting the error?
Feb 6th, 2007, 01:26 PM
danasegarane

Re: current record set does not support upadating
VB Code:
```
txtAppID = rs.fields(0)
tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")
```

Re: current record set does not support upadating

This one

VB Code:

If tblinquiry.state=0 then tblInquiry.open(your connection string)
 
With tbInquiry
.Fields(0) = txtAppID
.Fields(1) = txtAppInfo(0)
.Fields(2) = txtAppInfo(1)
.Fields(3) = txtAppInfo(2)
.Fields(4) = txtAppInfo(3)
.Fields(5) = txtAppInfo(4)
.Fields(6) = cmbTFA.Text
'.Fields(7) =
'AddFields
.Update
MsgBox "Edit successfully saved to the record.", vbInformation
End With

Re: current record set does not support upadating

in my list view i have a checkbox how can i put the one that the user check in the list view to the database

VB Code:

If LV1.ListItems.Count - 1 > -1 Then
                
                ItemCount = -1
                For Each ls In LV1.ListItems
                    If ls.Checked = True Then
                        ItemCount = ItemCount + 1
                    End If
                Next
                
                ReDim AccessTitle(ItemCount)
                
               i = 0
               For Each ls In LV1.ListItems
                
                    If ls.Checked = True Then
                    
                        AccessTitle(i) = ls.Text
                        i = i + 1
                    End If
                Next
    End If
    
End Sub

Feb 6th, 2007, 11:46 PM
mheiy

Re: current record set does not support upadating
in dois this
VB Code:
```
txtAppID = rs.fields(0)
tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")
```
the
VB Code:
```
tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")
```
comes in red color whats he error
Feb 7th, 2007, 03:58 AM
danasegarane

Re: current record set does not support upadating

Where you get the Red Colour
Feb 7th, 2007, 04:06 AM
mheiy

Re: current record set does not support upadating

when i just enter it becomes red
Feb 7th, 2007, 05:19 AM
danasegarane

Re: current record set does not support upadating
Try this
VB Code:
```
tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")[B])[/B]
```