Two questions:
First error I receive is while modifying data on a tabbed form, the form’s focus is set after a listbox selection is made. I can make the changes in the data fields throught the form, as long as I don’t change the record. I can’t close the form, either. I receive the error: Run Time Error ‘2427’ You entered an expression that has no value.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim ctl As Control
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(“tblChanges”)
‘On Error GoTo Form_BeforeUpdate_Error
For Each ctl In Me.Controls
If TypeOf ctl Is Label Then
‘do nothing
Else
If Nz(ctl.OldValue) Nz(ctl.Value) Then
With rst
rst.AddNew
rst!SSN = SSN
rst!OBJECT_CHANGED = ctl.Name
rst!prior_value = ctl.OldValue
rst!current_value = ctl.Value
rst!changed_by = GetNetUser()
rst.Update
End With
End If
End If
Next ctl
On Error GoTo 0
rst.Close
Exit Sub
Form_BeforeUpdate_Error:
If Err.Number = 2427 Then ‘this is the error number for something which does not have any data, and therefore does not have an “oldvalue”.
Resume Next
Else
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure Form_BeforeUpdate”
End If
End Sub
I assume it has something to do with the field having nothing in it to begin with (possibly Null?), and then me adding data, however I thought the Nz function would resolve that issue, and I still get the error when I modify the field with pre-existing data.
Second question:
When my form loads, there is a tab with a list box on it, and the list box’s SQL’s text box refers to [forms]![frmS1_IndividualInfo].txtSearch, a text box obviously that I use to filter the list box for a name. This works fine in A2k, however in 2k3 I receive a criteria prompt before the form even loads. What changed, and more importantly, how do I fix it? 
) is that I use this code on other forms, and it works flawlessly. 
