.edit

[Gib]

I am trying to change the value of two records in the same
table based on result of a combobox selection.

I have tried with the code below and nothing works. The
lines that are "Comments" are what I tried.

Any suggestions would be welcome!

Thanks

Private Sub BBP_No_BeforeUpdate(Cancel As Integer)
''Dim rs As Recordset
''Dim rs As Variant
Stop

If Me.BBP_No.OldValue Is Null Then
'never mind this is a new entry
ElseIf Me.BBP_No.OldValue = Me.BBP_No Then
'never mind no change was made
Else
''Set rs = OldBBP.RecordsetClone
''Set rs = OldBBP.Recordset
''Set rs = OldBBP
With Me.BBP_No.OldValue
.Find "BBP_No=" & Me.BBP_No.OldValue
.edit
Me.BBP_No.OldValue!Avaliable = Yes 'This is
a yes/no field
.Update
End With

With Me.BBP_No
.Find "BBP_No=" & Me.BBP_No
.edit
Me.BBP_No!Avaliable = No 'This is a yes/no
field
.Update
End With
Me.BBP_No.Requery
End If

 

[Dale Fye]

Gib,

You've got me confused. If my memory serves me right, in order for a
control to have an "OldValue", it must be bound to a field in the forms
underlying recordset. In which case, you don't need to do anything except
move off the record or execute the (me.dirty = False) statement to force the
change to take place in your database.

You indicate that you want to update 2 records in the table, but your code
(if it worked) would only accomplish changing the first record. If, what
you really want to do is change the value of that field in every record
where the "OldValue" previously existed, you could just do:

currentdb.execute "Update yourTable " _
& "SET yourField = " & me.BBP_No _
& " WHERE yourField = " & me.BBP_No.OldValue

Hope this Helps.

If not, post back with a better explaination of exactly what you are trying
to accomplish, not how you are trying to accomplish it.

Dale

 

[Guest]

Dale,

If you are confused, think of me!

I will try to explaine.

The main_form has tabs. In the tabs are sub_forms with
sub_subforms. The sub_form asks only for the Date and
Event[combobox] (Shipped or Returned).

When the Date and Event are answered, the sub_subform is
activated to collect thru a combobox the "BBP_No(s)" If
the Event="Shipped" then the "(s)" are limited to those
with Avaliable="Yes" When a "BBP_No" has been selected
it's "Avaliable" is changed to "No"

Because the count "BBP_No's" exceeds 100 at times, there
are chances that an error has been made.

Ex. Date=Today
Event="Shipped"
BBP_No selected="200001"
'Avaliable' of BBP_No "200001" is changed to "No"
Tomorrow I discover that "BBP_No" "200002" is the actual
item.

When I return and make the change (to "BBP_No" "200002"),
the 'Avalible' of "200002" is changed to "No" and
the 'Avaliable' of "200001" remains "No".

I need the 'Avalibilty' of "200001" changed to "Yes" so
that if it does go on a job it can be a selection in
the 'BBP_No' combobox once again.

Thanks
Gib