[RESOLVED] Help Plz

[Jamie_Garland]

Can anyone help me with this minor problem please.

I have deleted all my accounts from my database ok but when i type in a new username and password and click on save i get an error saying the following.

Run-time error '3021':

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

and it then highlights the following code.

.Fields("uname") = txtUser.Text

Can anyone help my whole code is below.

Public CN As New ADODB.Connection
Public RS As New ADODB.Recordset
 
 
Private Sub cmdClose_Click()
CN.Close
Unload Me
End Sub
 
Private Sub cmdDelete_Click()
    With RS
        CN.Execute "DELETE * FROM users WHERE uname ='" & .Fields("uname") & "'"
        .Requery ' update our datagrid
    End With
End Sub
 
Private Sub cmdNew_Click()
    txtUser.Text = ""
    txtPass.Text = ""
    RS.AddNew
End Sub
 
Private Sub cmdSave_Click()
    With RS
        If txtUser.Text <> "" And txtPass.Text <> "" Then
        .Fields("uname") = txtUser.Text
        .Fields("pass") = txtPass.Text
        .Update
    End If
    End With
End Sub
 
Private Sub Form_Load()
 
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb"
    RS.CursorLocation = adUseClient
    RS.Open "SELECT * FROM users ORDER BY uname ASC", CN, adOpenStatic, adLockOptimistic
    Set DataGrid1.DataSource = RS
 
 
 
    DataGrid1.Columns(0).Caption = "Username"
    DataGrid1.Columns(1).Caption = "Password"
 
End Sub

[MartinLiss]

At EOF shouldn't you be using AddNew and not Update?

[Jamie_Garland]

Nope still get the same error

[MartinLiss]

Private Sub cmdSave_Click()
    With RS
        If RS.EOF Then
            .AddNew
        End If
        If txtUser.Text <> "" And txtPass.Text <> "" Then
        .Fields("uname") = txtUser.Text
        .Fields("pass") = txtPass.Text
        .Update
    End If
    End With
End Sub

[Jamie_Garland]

What would i do to do a search for a username in the program?

[MartinLiss]

CN.Execute "Select * FROM users WHERE uname ='" & txtUser.Text & "'"

[Jamie_Garland]

i done the following code but it dosent show the records in the textboxes when found.

Private Sub Command2_Click()
With RS
CN.Execute "Select * FROM users WHERE uname ='" & Text1.Text & "'"
        .Fields("uname") = txtUser.Text
        .Fields("pass") = txtPass.Text
        .Requery
        
    End With
End Sub

[MartinLiss]

Private Sub Command2_Click()
With RS
CN.Execute "Select * FROM users WHERE uname ='" & Text1.Text & "'"
[HL="#FFFF80"]        txtUser.Text = .Fields("uname")
        txtPass.Text = .Fields("pass")[/HL]
        '.Requery ?
        
    End With
End Sub

[Jamie_Garland]

When i type that code in nothing happens.

With RS
CN.Execute "Select * FROM users WHERE uname ='" & Text1.Text & "'"
        txtUser.Text = .Fields("uname")
        txtPass.Text = .Fields("pass")
End W

[MartinLiss]

It works for me. Do you have a field called Text1?

[MartinLiss]

Could the case of the name be the problem? For example are you looking for "smith" when the name is "Smith"?

[Jamie_Garland]

nope i tried both ways. Or is there a better like using an inputbox or something.

[MartinLiss]

Please zip up your project with your database and attach it.

[Jamie_Garland]

program attached.

[MartinLiss]

I used "Mark" as the name and "brodie" as the password and I logged in successfully. Is there something else I should be doing?

[Jamie_Garland]

click on the command1 command button it should being up another form i want to have a search feature on that form so people can search the users and also look at the data grid.

[MartinLiss]

In the project you sent me there is no reference to Form1.

[Jamie_Garland]

what do u mean no referance

[MartinLiss]

There is no "Load Form1" anyplace so the form is not shown. But I just now added that code after the login and I'll take a look at the problem you mentioned.

[Jamie_Garland]

oh im sorry must have forgot to add it.

[MartinLiss]

Do I have the correct and or up to date project. In the one you sent me Command1 has a cation of "Update" and the following code.

Private Sub Command1_Click()
On Error Resume Next
With RS
    If MsgBox("Update The Record?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
    .Fields("uname") = txtUser.Text
    .Fields("pass") = txtPass.Text
    .Update
    .Requery
MsgBox "The current record has been updated", vbInformation, "Update Successful"
End If
End With
End Sub

[Jamie_Garland]

yes thats correct

[MartinLiss]

Well then I'm totally confused. In posts #7 through #12 we were talking about data showing up in textboxes but it does not seem that Command1 has anything to do with that.

[Jamie_Garland]

its ment to have a search feature on Form1 but it dosent seem to be one on it can you help me add it please.

[MartinLiss]

The code in post #8 does that.

[Jamie_Garland]

This code dosent work

Private Sub Command2_Click()
With RS
CN.Execute "Select * FROM users WHERE uname ='" & Text1.Text & "'"
txtUser.Text = .Fields("uname")
txtPass.Text = .Fields("pass")
'.Requery ?

End With
End Sub

[MartinLiss]

ADO isn't something I use very often so there may be a better way of doing this but...

Private Sub Command2_Click()
With RS
    .Close
    
    RS.Open "Select * FROM users WHERE uname ='" & Text1.Text & "'"
    
    txtUser.Text = .Fields("uname")
    txtPass.Text = .Fields("pass")
End With
 
End Sub

[Jamie_Garland]

Code works now but how do i get it to show the record in the DataGrid aswell. As it removes them from the datagrid and just shows up blank with the record showing in the text boxes.

With RS
    .Close
    
    RS.Open "Select * FROM users WHERE uname ='" & Text1.Text & "'"
    
    txtUser.Text = .Fields("uname")
    txtPass.Text = .Fields("pass")
End With

[MartinLiss]

Put the Requery back in.

Private Sub Command2_Click()
With RS
    .Close
    
    RS.Open "Select * FROM users WHERE uname ='" & Text1.Text & "'"
    
    txtUser.Text = .Fields("uname")
    txtPass.Text = .Fields("pass")
    .Requery
End With
 
End Sub

[Jamie_Garland]

Thank you very much.

[MartinLiss]

You're welcome. Did you know that you can more easily mark the thread resolved by pulling down the Thread Tools menu and clicking the Mark Thread Resolved button?

[Jamie_Garland]

No i didnt know that lol can i ask yopu one more question. See once the search has been done how do i get it to go back and show all the other records again. Using a timer or something like that.?

[MartinLiss]

I guess the best thing would be to have a "Refresh" button that would do a .Close and a Select *, followed by another .Requery.

[Jamie_Garland]

How would i go about getting the refresh button to work.

[Chathura]

As I read your program, I didn't see any need of text boxes. You can just type on the grid cells to add a new record. The reason is you have bound the recordset to the data grid.
You only have to do these steps
1. Press "new" button
2. Type your new values on Data Grid
3. Press Update button

Private Sub Command1_Click()
On Error Resume Next
With RS
    If MsgBox("Update The Record?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
'    .Fields("uname") = txtUser.Text
'    .Fields("pass") = txtPass.Text
    .Update
    .Requery
MsgBox "The current record has been updated", vbInformation, "Update Successful"
End If
End With
End Sub

[MartinLiss]

How would i go about getting the refresh button to work.

Well, just what I said in post #33...

Private Sub cmdRefresh_Click()
With RS
    .Close
    
    RS.Open "Select * FROM users"
    
    .Requery
End With
 
End Sub