so the form's code is
Code:
Public objS As School
Private Sub Form_Load()
Set objS = New School
Me.Text3 = "Search using a school's ID"
End Sub
Private Sub Text3_Change()
Debug.Print "render " & Me.Text3
If IsNull(Me.Text3) = False Then
If IsNumeric(Me.Text3) = True Then
Dim lng1 As Long
lng1 = Nz(Me.Text3.value, 0)
Me.txtName = Nz(objS.Name(lng1), "")
End If
End If
End Sub
Private Sub Text3_GotFocus()
If Me.Text3 = "Search using a school's ID" Then
Me.Text3.Format = "General Number"
Me.Text3 = ""
End If
End Sub
Private Sub Text3_LostFocus()
If Me.Text3 = "" Then
Me.Text3.Format = "ABC"
Me.Text3 = "Search using a school's ID"
End If
End Sub
The class object I made is
Code:
Option Compare Database
Dim rs As DAO.Recordset
Dim missingID As String
Private Function schoolLoad(SchoolID As Long) As Boolean
missingID = "This record ID was not found in the databse"
If IsNumeric(SchoolID) = False Then
schoolLoad = False
Exit Function
End If
'find particular school by the schools own table ID
Dim db As DAO.Database
Dim strSQL As String
strSQL = "SELECT * FROM tblSchools WHERE NewSchoolsID = " & SchoolID & " ;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
schoolLoad = True
'check to see if there is a record in database table, false if missing
If rs.RecordCount = 0 Then
schoolLoad = False
Exit Function
End If
End Function
Private Function FieldValueFrom(FieldName As String) As Variant
FieldValueFrom = Nz(rs.Fields(FieldName).value, "")
End Function
Public Function Name(SchoolID As Long) As Variant
If schoolLoad(SchoolID) = False Then
Name = missingID
Exit Function
End If
Name = FieldValueFrom("SchoolName")
End Function
Public Function Suburb(SchoolID As Long) As Variant
If schoolLoad(SchoolID) = False Then
Suburb = missingID
Exit Function
End If
Suburb = FieldValueFrom("SchoolSuburb")
End Function
Public Function Phone(SchoolID As Long) As Variant
If schoolLoad(SchoolID) = False Then
Phone = missingID
Exit Function
End If
Phone = FieldValueFrom("SchoolPhone")
End Function
Public Function Email(SchoolID As Long) As Variant
If schoolLoad(SchoolID) = False Then
Email = missingID
Exit Function
End If
Email = FieldValueFrom("SchoolEmail")
End Function
Public Function CallBackDate(SchoolID As Long) As Variant
If schoolLoad(SchoolID) = False Then
Exit Function
End If
CallBackDate = FieldValueFrom("CallBackDate")
End Function
Public Function ID(SchoolID As Long) As Variant
If schoolLoad(SchoolID) = False Then
Exit Function
End If
ID = FieldValueFrom("NewSchoolsID")
End Function
Public Function change_Name(SchoolID As Long, NewName As String) As Boolean
If schoolLoad(SchoolID) = False Then
change_Name = False
Exit Function
End If
If IsValidEmail(NewName) Then
change_Name = True
rs.Edit
rs.Fields("SchoolName").value = NewName
rs.update
Else
change_Name = False
End If
End Function
Public Function change_Email(SchoolID As Long, NewEmail As String) As Boolean
If schoolLoad(SchoolID) = False Then
change_Email = False
Exit Function
End If
If IsValidEmail(NewEmail) Then
change_Email = True
rs.Edit
rs.Fields("SchoolEmail").value = NewEmail
rs.update
Else
change_Email = False
End If
End Function
The concept is to have a variable which gets searched - and refreshed when required. So everything in done in memory.
I deleted both controls - still the same issue.