Updating records if Null

[Paulsburbon]

I'm hoping to have a button that fills in data in fields that are null. I was wondering the best way to do this. Currently I have a button that will go thru a set of records and fill in for all the records. I'm hoping to only have this work if a certain field is null and if there is somethere than to skip it. Here is the code I have for the button.

Private Sub Toggle89_Click()

On Error GoTo Toggle89_Click_Err
Dim vbrResult As VbMsgBoxResult
Dim vbrResult1 As VbMsgBoxResult
Dim RandomValue
Dim Discount
Dim strSQL As String
Dim strSQL1 As String

vbrResult = MsgBox("Run Auto Quote? This cannot be undone!", vbQuestion + vbYesNo, "Auto Quote")
If vbrResult = vbYes Then
Randomize ' Initialize random-number generator
RandomValue = Int((21 * Rnd) + 1) ' Generate random value between 1 and 5
Select Case RandomValue
Case 1: Discount = 0.1
Case 2: Discount = 0.1025
Case 3: Discount = 0.105
Case 4: Discount = 0.1075
Case 5: Discount = 0.11
Case 6: Discount = 0.1125
Case 7: Discount = 0.115
Case 8: Discount = 0.1175
Case 9: Discount = 0.12
Case 10: Discount = 0.1225
Case 11: Discount = 0.125
Case 12: Discount = 0.1275
Case 13: Discount = 0.13
Case 14: Discount = 0.1325
Case 15: Discount = 0.135
Case 16: Discount = 0.1375
Case 17: Discount = 0.14
Case 18: Discount = 0.1425
Case 19: Discount = 0.145
Case 20: Discount = 0.1475
Case 21: Discount = 0.15
Case Else: Discount = 0
End Select

strSQL = "UPDATE [tblClaimLineItems] " & _
"SET [curPrice] = ([curRetail] * (1-" & Discount & "))" & _
"WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "';"
DoCmd.RunSQL strSQL
End If
vbrResult1 = MsgBox("Would you like all the Replaced Item Text to be copied from the insured Description?", vbQuestion + vbYesNo, "Auto Quote")
If vbrResult1 = vbYes Then
strSQL1 = "UPDATE [tblClaimLineItems] " & _
"SET [chrReplacedItemDescription] = [chrLostItemDescription]" & _
"WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "';"
DoCmd.RunSQL strSQL1
End If
Exit Sub
Toggle89_Click_Err:
MsgBox "Error is " & Err.Description
Exit Sub
End Sub

I'm sure I would use an If [chrReplacedItemDescription] = Null then run code, However I'm not quite sure how to get into the looping part of the code. What is the best way to load a recordset and loop thru one record at a time? Thank you for any help.

 

[Ian Mac]

I haven't tested this but why don't you limit the recordset:

strSQL1 = ""
strSQL1 = strSQL1 & "UPDATE tblClaimLineItems "
strSQL1 = strSQL1 & "SET chrReplacedItemDescription = [chrLostItemDescription] "
strSQL1 = strSQL1 & "WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "' "
strSQL1 = strSQL1 & "AND chrReplacedItemDescription Is Null;"

 

[Paulsburbon]

That worked perfect, Such a simple answer. I love you guys. If I had to do that on my own it would have had 4000 line of code and would have never worked right. Thanks again!

 

[Paulsburbon]

Well, now what if I wanted to do that with a currency field? It defaults to $0.00 and will not change any fields. Should I do a > $0.00?

 

[Paulsburbon]

Ok I did the following and it worked. Is it correct?

AND curPrice = 0;"

Just making sure it's the best way to do this. It does work. Thanks again.