recordset problems

[Xbox]

Can anyone enlighten me with this problem?

If BASE = False Then
            rs.Edit 
            rs!HidWgt = rs!Weight
            rs.Update
            rs.Edit
            rs!WgtBrk = rs!Weight
            rs.Update
End if
this works for all records except for the last record

If BASE = False Then
           rs.MovePrevious            
            rs.Edit
            rs!HidWgt = rs!Weight
            rs.Update
            rs.Edit
            rs!WgtBrk = rs!Weight
            rs.Update
            rs.MoveNext
End if
works but now the first record is blank

Help please.
Thanks in advance

 

[Calvin]

get rid of the middle update and edit functions

If BASE = False Then
            rs.Edit 
            rs!HidWgt = rs!Weight
            rs!WgtBrk = rs!Weight
            rs.Update
End if

Is this running in a Do Loop? If so, there may be better syntax.

 

[dcx693]

What kinds of errors do you receive? Also why do you do an update and then edit the same record again?

 

[Xbox]

Thanks but that has the same affect as the first piece of code I have above.

Yes it is part of a loop routine. See below

    Do While Not rs.EOF

        ' a ton of conditional code goes here
         
        BASE = False
        If rs!Qty = "BASE" Then BASE = True

       
        If BASE = False Then
            rs.MovePrevious
            rs.Edit
            rs!WgtBrk = WgtBrk
            rs.Update
            rs.MoveNext
            rs.Edit
            rs!HidWgt = WgtBrk + 1
            rs.Update
        Else
            rs.Edit
            rs!HidWgt = rs!Weight
            rs!WgtBrk = rs!Weight
            rs.Update

        End If
        
           
    Loop

As you can very tell, I am shooting in the dark. I can't seem to fully grasp how to manipulate recordsets and at the point of trying anything that works.

 

[dcx693]

What exactly are you trying to do with that code?

 

[Calvin]

I'm not sure of what your trying to accomplish, but have you tried something like the following:

[COLOR=blue][B]if NOT rs.BOF AND NOT rs.EOF then rs.MoveFirst[/B] [/COLOR]
 Do While Not rs.EOF

        ' a ton of conditional code goes here
         
        BASE = False
        If rs!Qty = "BASE" Then BASE = True

       
        If BASE = False Then
            [COLOR=blue][B]if NOT rs.BOF then [/B] [/COLOR]rs.MovePrevious
            rs.Edit
            rs!WgtBrk = WgtBrk
            rs.Update
            [COLOR=blue][B]if NOT rs.EOF then [/B] [/COLOR]rs.MoveNext
            rs.Edit
            rs!HidWgt = WgtBrk + 1
            rs.Update
        Else
            rs.Edit
            rs!HidWgt = rs!Weight
            rs!WgtBrk = rs!Weight
            rs.Update
            [COLOR=blue][B]if NOT rs.EOF then rs.MoveNext[/B] [/COLOR]
        End If
         
    Loop

but this is overdoing it a bit, it help if we better understood what you are trying to acomplish as your end result.

 

[Xbox]

sorry guys. Let me try that again.

The user has three fields.

Weight
WgtBrk
HidWgt

Weight is a field the user gets to input. I want whatever is entered in WEIGHT to also get copied over to WgtBrk and HidWgt for every record.

example: if user enters 2 as weight then wgtbrk = 2 and hidwgt =2.
That's it. Sounds simple and very close to my objective but I either miss out on the first record or the last.

Thanks

 

[Calvin]

If your working with Integers then the following should do the trick.

CurrentDb.Execute ("UPDATE MyTable SET [HidWgt]=" & intWeight & ", [WgtBrk]=" & intWeight)

Declare an Integer intWeight and pass it the value that the user is inputing and this SQL update statment will update all records in the table (change 'MyTable' to the actual table name) in just one step.

 

[jon_sg]

I agree with Calvin

I have had a similar problem in the past. The Do Loop seems to register EOF on the last record and omit it. I always use UPDATE for ammendments or INSERT INTO SQL statements for additions. I usually try to save the query and open it as a recordset object as it seems to work faster than SQL statements or Docmd. for large recordsets.