Results 1 to 4 of 4
  1. #1
    uaguy3005 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2010
    Posts
    45

    Clear Form and Goto

    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


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    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?

  4. #4
    uaguy3005 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2010
    Posts
    45
    Quote Originally Posted by Bulzie View Post
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using search form with listbox to goto record
    By dweekley in forum Access
    Replies: 2
    Last Post: 05-04-2017, 01:31 PM
  2. Replies: 2
    Last Post: 02-22-2017, 05:45 AM
  3. How do I add to my code goto new form
    By smc678 in forum Programming
    Replies: 5
    Last Post: 01-01-2013, 08:03 PM
  4. Replies: 1
    Last Post: 06-09-2012, 05:44 PM
  5. Replies: 1
    Last Post: 06-01-2011, 06:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums