Clear Form and Goto

[uaguy3005]

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.

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

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

[Micron]

I question whether you are creating a new record or wiping out everything in a record - except for the checkboxes, which are being set to False. Are these controls bound to a table or query? If so you are updating a record. If not, then you're simply removing unbound control values then doing nothing with the result. Also, it appears that you assign a value to strLookup then maybe clear the value from the control, then try to go to a record that has that value. Question is, did you just over-write it?

BTW, your find record line is going to run regardless of what happens before that. It might be better if you just go to the record if a value is found and forget about clearing the form.

[Bulzie]

Seems wasteful if they are allowed to type data in to only have it clear out if the SN is already in there. Can you use the SN as a search combo box maybe and if it is not in the list, then pop down and let them add the other fields?

In your example above, I believe it is because you are not really undoing the record so when you go find the current SN record it is saving a record with that blank data. Maybe try me.Undo to remove all data, then the Docmd.Findrecord?

[uaguy3005]

Seems wasteful if they are allowed to type data in to only have it clear out if the SN is already in there. Can you use the SN as a search combo box maybe and if it is not in the list, then pop down and let them add the other fields?

In your example above, I believe it is because you are not really undoing the record so when you go find the current SN record it is saving a record with that blank data. Maybe try me.Undo to remove all data, then the Docmd.Findrecord?

Thanks! I think you're right. For some reason, I just got locked in on having the form open to a new record and having to clear a record before navigating back to one in the table if it already existed.