What am I doing Wrong in ADO?

[musleh]

I am trying to copy a field value to another field in the same record if the Rank = 1
The Table name is : tbl_SSP

Fields: Rank "if rank = 1" then move
Red_Level_Support "This amount" to
Current_Level_of_Funding "this field"

What happen is if I have my cursor on the rank 1, then i is ok, but if my cursor is on some other field, it copies that value. I tried to set the focus and lots of other things, but no luck.

'**************** THE CODE **************
On Error Resume Next

Dim dbsSSP As Database
Dim rst As Recordset
Dim strMessage As String

Set dbsSSP = CurrentDb()
Set rst = dbsSSP.OpenRecordset("tbl_SSP", dbOpenSnapshot)

With rst
.MoveLast
.MoveFirst
' !Rank.SetFocus
Do While Not .EOF

If !Rank = "1" Then

Me.Current_Level_of_Funding = ""
Me.Current_Level_of_Funding = Red_Level_Support
End If
dbsSSP.Close
Exit Do
End If

rst.MoveNext
Loop

End With

 

[kjv1611]

What event is that code located within?

Also, do you have any other code running on mouse-move or anything else that could possibly be run just by moving your mouse. For instance, if there is any code that runs on "got ocus" of the form or any controls...

 

[kjv1611]

Here's what I'd suggest doing for starters:

[GREEN]'On Error Resume Next[/GREEN]
[GREEN]'Using this statement for a whole procedure is generally considered very bad programming.  If it's erroring, there's a reason and you need to fix the problem.[/GREEN]

   Dim dbsSSP As DAO.Database
   Dim rst As DAO.Recordset
   Dim strMessage As String

   Set dbsSSP = CurrentDb
   Set rst = dbsSSP.OpenRecordset("tbl_SSP")

   With rst
      Do While Not .EOF
        If .Fields("Rank") = 1 Then 
        [GREEN]'Assuming this would be a numeric field... if not, then put the quotes back on the 1..[/GREEN]
           [GREEN]'Current_Level_of_Funding = ""  
'Why do you set this to Null Length string, and immediately to a value?  Should only need to set it once..[/GREEN]
           Current_Level_of_Funding = Red_Level_Support
[GREEN]'        End If  'Looks like you got 2 End Ifs in there.. but only one If..[/GREEN]
           dbsSSP.Close
           Exit Do 
         End If
         
        rst.MoveNext
      Loop

    End With

 

[musleh]

The answer to the first.
There s no code running except this


The second posting.
I had exact thing first. I did exactly what you did with the code. The problem is :

The code does not always go to Rank 1. It is wherever record the cursor on, it adds the value to that "Current_Level_of_Funding".

and the Rank field is numeric and the The "Current_Level_of_Funding" and "Red_Level_Support" is numeric also

Thank

 

[JoeAtWork]

Basically what you are doing is:

If any record in the table tbl_SSP has Rank=1, the field values will get copied. You are not doing anything to match the recordset record with the one on the form. You are just looping through all records in the table until you happen to hit one with Rank=1.

Why even open a recordset? Just include the Rank field on the form and then you can look it up directly. Or if you have to use a recordset, don't retrieve all records in the table, just retrieve the one that matches what is on the form (you do have a Primary Key for the table, correct?).