Looping DAO Recordset

[Martyh]

What have I forgotten ?

I have created a button on screen which goes thru all of my email names and gets the domain name from the Email. This is the code that I've used, except that the sub stops after going thru the first one. I am at a loss !! This should work -->> BUT !!

Whaat am I doing wrong ?

Private Sub DomainNamePlace_Click()

Dim dbs As Database
Dim rst As Recordset
Dim intRecordCount As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblContacts;")
rst.MoveFirst

If Not (rst.EOF) Then
    With rst
       .Edit
       ![Domain Name] = Right(!EmailName, Len(!EmailName) - InStr(!EmailName, "@"))
       .Update
       .MoveNext
    End With
Else
    MsgBox ("No more values!")
End If
rst.Close
Set dbs = Nothing

Exit_DomainNamePlace_Click:
    Exit Sub

Err_DomainNamePlace_Click:
    MsgBox Err.Description
    Resume Exit_DomainNamePlace_Click

End Sub

 

[Mile-O]

Private Sub DomainNamePlace_Click()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim intRecordCount As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblContacts;")
    rst.MoveFirst

    Do While Not rst.EOF
        With rst
           .Edit
           .Fields("Domain Name") = Right(.Fields("EmailName"), Len(.Fields("EmailName") - InStr(.Fields("EmailName", "@"))
           .Update
           .MoveNext
        End With
    Loop
    MsgBox "No more values!", vbExclamation
    rst.Close 
    dbs.Close

Exit_DomainNamePlace_Click:
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

Err_DomainNamePlace_Click:
    MsgBox Err.Description
    Resume Exit_DomainNamePlace_Click

End Sub

Why are you putting a calculated value into a field?

 

[Martyh]

Private Sub DomainNamePlace_Click()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim intRecordCount As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblContacts;")
    rst.MoveFirst

    Do While Not rst.EOF
        With rst
           .Edit
           .Fields("Domain Name") = Right(.Fields("EmailName"), Len(.Fields("EmailName") - InStr(.Fields("EmailName", "@"))
           .Update
           .MoveNext
        End With
    Loop
    MsgBox "No more values!", vbExclamation
    rst.Close 
    dbs.Close

Exit_DomainNamePlace_Click:
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

Err_DomainNamePlace_Click:
    MsgBox Err.Description
    Resume Exit_DomainNamePlace_Click

End Sub

Why are you putting a calculated value into a field?

Although it will be redundant at first, its rather the reverse: I'd like keep the domain name and the user name seperately, rather than the whole email name.

I intend to eliminate the whole email name in the future.

Marty

 

[billyr]

Although it will be redundant at first, its rather the reverse: I'd like keep the domain name and the user name seperately, rather than the whole email name.

I intend to eliminate the whole email name in the future.

Marty

I have a question if I may. Somewhere, likely here or Access VB Help, I gained the impression that to populate a recordset you should:
rst.movelast
rst.movefirst.
Does that only apply to table recordsets, none, all? Thanks for this chance to learn too.

 

[WayneRyan]

Marty,

Private Sub DomainNamePlace_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intRecordCount As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblContacts;")

While Not (rst.EOF) Then
   rst.Edit
   rst![Domain Name] = Right(!EmailName, Len(!EmailName) - InStr(!EmailName, "@"))
   rst.Update
   rst.MoveNext
   Wend
rst.Close
Set dbs = Nothing

Exit_DomainNamePlace_Click:
    Exit Sub

Err_DomainNamePlace_Click:
    MsgBox Err.Description
    Resume Exit_DomainNamePlace_Click

End Sub

Wayne