Recordset transactions not completing, no error message! (1 Viewer)

[raggajunglist]

Afternoon all ,

Im running 2003 with a BE/FE setup (all in Access)
I have a piece of code which doesn't seem to work and wont give me any error messages.

I believe it is something to do with there being no records in this recordset but no matter how hard i try to work around it, i cant (especially with no error messages!!!!)

Here is the offending bit of code:

Sub rstAdds()
Dim objDb
Dim fld
Dim repName

repName = GetAbvName()
Const dbOpenDynaset% = 2
Set objDb = CurrentDb.OpenRecordset("tblAddresses" & repName, dbOpenDynaset)

For Each fld In objDb.Fields
If fld.Name = "Street" And Nz(fld.Value, "") = "" Then
GoTo endMe:
Else
If fld.Name = "AccNum" And Nz(fld.Value, "") = "" Then
objDb.Edit
objDb.Fields("AccNum").Value = Forms!frmMainEmpty!txtAccNum.Value
objDb.Fields("RepID").Value = GetCurrentUserName()
objDb.update
objDb.Close
Set objDb = Nothing
End If
End If
Next fld
endMe:

End Sub

Any help is greatly appreciated, im getting no end of problems because of this.

Thanks in advance.

Mike

 

[mearle]

It really depends on what you are trying to do - at the moment what it is actually doing seems very bizarre, and I'm not surprised it doesn't do anything at all.
First of all, it seems you are trying to edit the 1st record in a recordset - is this the intention ? Or do you really mean to add a new one, which the routine name implies.
Then there is an unnecessary for-next loop which may or may not quit before doing anything at all, depending on which field it happens to find first.
What are the if statments trying to test for ?
Baffling !

 

[DCrake]

Simple Software Solutions

Hi

although the method you are using is syntactically correct I would suggest you change your tack on how to handle the process. Try this.

repName = GetAbvName()

Dim Rs As DAO.Recordset
Dim RepId As String
Dim AccNo As Long


      RepID = GetCurrentUserName()
      AccNo = Forms("FrmMainEmpty")("TxtAccNum")

Set Rs = CurrentDb.OpenRecordset("Select * From tblAddresses Where Street Is Not Null And AccNum Is Null")

If Not Rs.EOF And Not Rs.BOF Then
    Do Until Rs.EOF
    
           Rs.Edit
           Rs("AccNum") = AccNo
           Rs("RepId") = RepID
           Rs.Update

        Rs.MoveNext
    Loop
    Rs.Close
Endif

Set Rs = Nothing

Summary:

1st. pass the currentusername() to a vaiable so it is only called once
2nd. Open the recordset for items that have a street entry but do not have
a AccNum entry.

3rd. Edit each record and pass the defined variables (AccNo & RepID) to the
coressponding fields.

4.th Close the recordset and release from cache

 

[raggajunglist]

DCrake:


Ahhh, opening a recordset like that is something i haven't seen yet!!
That makes everything much easier, thanks for showing me that!

All replys much appreciated, thanks guys.

M