current record set does not support upadating

[mheiy]

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.

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

[danasegarane]

This will solve your problem

tbInquiry.Open "Select * from tbinquiry", cn, [B]adOpenDynamic[/B], adLockOptimistic

[si_the_geek]

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.

[mheiy]

ive already change that to adOpenDynamic but still have the same error.

[si_the_geek]

The problem is that lines like this:

Set tbInquiry = cn.Execute(str)

..need to be replaced with this:

tbInquiry.Open str, cn, adOpenKeyset, adLockOptimistic

[mheiy]

it says that the connection is already open

[mheiy]

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

[danasegarane]

I think you have post the Full Code

[mheiy]

in form 1 this is my 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

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

[mheiy]

yes im done that but still having an error.. sorry im just a beginner

[si_the_geek]

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).

[mheiy]

[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

[danasegarane]

Put this before the opening of the all tbInquiry

set tbInquiry=Nothing

[mheiy]

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.

[si_the_geek]

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.

[mheiy]

where i will put this
[Highlight=VB]
set tbInquiry=Nothing

[si_the_geek]

Ah yes, in that case add these two lines before that .Open line:

tbInquiry.Close
Set tbInquiry=Nothing

ps: you aren't using the tags correctly.
[vbcode] 'code here [/vbcode]

[danasegarane]

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

[mheiy]

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

[danasegarane]

It should be like this

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

[si_the_geek]

What error?

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

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.

[mheiy]

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

[danasegarane]

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

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

[mheiy]

thank you so much guys... you really help me a lot

[mheiy]

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...

[mheiy]

im having an error when closing it

[danasegarane]

What is the Errorr Msg?

[mheiy]

Operartion are not allowed because the object are closed

[mheiy]

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

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

[danasegarane]

Which Line you are getting the error?

[danasegarane]

txtAppID = rs.fields(0)
tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")

[danasegarane]

This one

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

[mheiy]

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

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

[mheiy]

in dois this

txtAppID = rs.fields(0)
tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")

the

tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")

comes in red color whats he error

[danasegarane]

Where you get the Red Colour

[mheiy]

when i just enter it becomes red

[danasegarane]

Try this

tmp = left(tmp,3) & (Format(Cint(right(tmp,4))+1,"00#")[B])[/B]