HI,
I have a form with several controls on it. One of the fields is a serial number, what I woud like to happen is if a user enters a serial number that is already in the table, the form should clear and then go to that record in the form. I have it somewhat working, but the problem I have is that each time this triggers it creates a blank record in the table. Any suggestions appreciated.
Code:
Private Sub txtUnit_SN_AfterUpdate()
'After user enters a serial number, checks if the serial number is already in the system
'then goes to that record.
'Search Criteria
Dim asCriteria As String
asCriteria = "[Unit_SN] ='" & Me.txtUnit_SN & "'"
'Lookup Criteria
Dim strLookUp As String
strLookUp = Me.txtUnit_SN.Value
If DCount("[Unit_SN]", "tblSerial_Nums", asCriteria) > 0 Then
ClearAll Me 'Function in Module 1
End If
DoCmd.FindRecord strLookUp, , , acUp, , acCurrent, False
End Sub
Code:
Function ClearAll(frm As Form)
Dim ctl As Control
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = ""
Case acOptionGroup, acComboBox, acListBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
End Function