none
All text and combo boxes flicker when searching for a record RRS feed

  • Question

  • In my form all my text and combo boxes flicker when searching for a specific record using the macro. I have a search box that when an Asset Id is entered it will load the text and combo boxes with the data associated with that asset id. If i search for another record all the data within in the boxes flicker like crazy. The design of the form doesn't flicker but the data inside the boxes do. Any ideas?
    Tuesday, February 19, 2019 12:57 PM

All replies

  • Hi. We could start with your macro. Can you post it here?
    Tuesday, February 19, 2019 3:56 PM
  • What version and Build is your version of Access? 2007 and early builds of 2010 had the flickering issue which was later fixed with Office Updates.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, February 19, 2019 4:14 PM
  • What version and Build is your version of Access? 2007 and early builds of 2010 had the flickering issue which was later fixed with Office Updates.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Microsoft Office Professional Plus 2010

    Microsoft Access Version: 14.0.7214.5000 (32 bit)

    Default file format for the database happens to be Access 2007 with no way to change it to a newer format.


    Tuesday, February 19, 2019 5:17 PM
  • Hi. We could start with your macro. Can you post it here?

    I want the searchbox to be able to use either an asset id or serial number. I tried many different WHERE clauses with OR but nothing would work. 

    SearchForRecord

          Object Type        Form

         Object Name        Edit Record

                  Record        First

    Where Condition =     ="Asset_ID='" & [Forms]![Edit Record]![SearchForDevice] & "'"   

    SearchForRecord

          Object Type        Form

         Object Name        Edit Record

                  Record        First

    Where Condition =     ="Serial_Number = '" & [Forms]![Edit Record]![SearchForDevice] & "'"


    Tuesday, February 19, 2019 5:17 PM
  • It looks like your version is updated so that isn't the problem. Also the file version for 2007 is the same as 2010 so you are good there as well.

    The problem could reside in the macro you are using. As DbGuy said, if you post the macro we can see what it is doing and might be able to offer a remedy.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, February 19, 2019 5:21 PM
  • Hi. We could start with your macro. Can you post it here?

    I want the searchbox to be able to use either an asset id or serial number. I tried many different WHERE clauses with OR but nothing would work. 

    SearchForRecord

          Object Type        Form

         Object Name        Edit Record

                  Record        First

    Where Condition =     ="Asset_ID='" & [Forms]![Edit Record]![SearchForDevice] & "'"   

    SearchForRecord

          Object Type        Form

         Object Name        Edit Record

                  Record        First

    Where Condition =     ="Serial_Number = '" & [Forms]![Edit Record]![SearchForDevice] & "'"



    Hi. Just as a test, if you remove the second SearchForRecord action and simply enter a valid Asset_ID in the search box, does it work without the flicker?
    Tuesday, February 19, 2019 5:49 PM
  • Hi. Just as a test, if you remove the second SearchForRecord action and simply enter a valid Asset_ID in the search box, does it work without the flicker?

       I removed the second SearchForRecord and still briefly flickers other values before I get the one I searched for. That is the only macro I have set. I am just using this to look up a record from my table so i can change some of its values and status in nicer looking form then just changing it within the dataset. 
    Tuesday, February 19, 2019 6:03 PM
  • Typically an unbound control to go to a specific record in a form would be a combo box with a RowSource property such as:

    SELECT Asset_ID, Asset, 1 As SortColumn  
    FROM Assets  
    UNION
    SELECT 0, "<New Asset>", 0
    FROM Assets  
    ORDER BY SortColumn, Asset;

    Asset_ID would be a number (usually an autonumber for convenience) data type.  The Controls BoundColumn property would be 1, its ColumnCount property 2, and its ColumnWidths property 0 to hide the first column.

    With code like this in its AfterUpdate event procedure:

        Const MESSAGE_TEXT = "No matching record"
        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        If Not IsNull(ctrl) Then
            If ctrl = 0 Then
                ' go to new record and move focus to Asset control
                DoCmd.GoToRecord acForm, Me.Name, acNewRec
                Me.Asset.SetFocus
            Else
                With Me.RecordsetClone
                    .FindFirst "Asset_ID = " & ctrl
                    If Not .NoMatch Then
                        ' go to record by synchronizing bookmarks
                        Me.Bookmark = .Bookmark
                    Else
                        MsgBox MESSAGE_TEXT, vbInformation, "Warning"
                    End If
                End With
            End If
        End If

    If you are unfamiliar with entering code into a form's, report's, report section's  or control's event procedures, this is how it's done in form or report design view:

    1.  Select the form, report, section or control as appropriate and open its properties sheet if it's not already open.

    2.  Select the relevant event property and select the 'build' button (the one on the right with 3 dots).

    3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

    4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new line(s) between these.

    Ken Sheridan, Stafford, England

    Tuesday, February 19, 2019 6:16 PM
  • Typically an unbound control to go to a specific record in a form would be a combo box with a RowSource property such as:

    SELECT Asset_ID, Asset, 1 As SortColumn  
    FROM Assets  
    UNION
    SELECT 0, "<New Asset>", 0
    FROM Assets  
    ORDER BY SortColumn, Asset;

    Asset_ID would be a number (usually an autonumber for convenience) data type.  The Controls BoundColumn property would be 1, its ColumnCount property 2, and its ColumnWidths property 0 to hide the first column.

    With code like this in its AfterUpdate event procedure:

        Const MESSAGE_TEXT = "No matching record"
        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        If Not IsNull(ctrl) Then
            If ctrl = 0 Then
                ' go to new record and move focus to Asset control
                DoCmd.GoToRecord acForm, Me.Name, acNewRec
                Me.Asset.SetFocus
            Else
                With Me.RecordsetClone
                    .FindFirst "Asset_ID = " & ctrl
                    If Not .NoMatch Then
                        ' go to record by synchronizing bookmarks
                        Me.Bookmark = .Bookmark
                    Else
                        MsgBox MESSAGE_TEXT, vbInformation, "Warning"
                    End If
                End With
            End If
        End If

    If you are unfamiliar with entering code into a form's, report's, report section's  or control's event procedures, this is how it's done in form or report design view:

    1.  Select the form, report, section or control as appropriate and open its properties sheet if it's not already open.

    2.  Select the relevant event property and select the 'build' button (the one on the right with 3 dots).

    3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

    4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new line(s) between these.

    Ken Sheridan, Stafford, England

    The asset ids are alphanumeric and are 8 characters long. Some of the devices are spares and have not been assigned an id yet. That is why i would like the ability to search by either serial numbers or asset ids within the same search box. 
    Tuesday, February 19, 2019 6:33 PM
  • If you want to search two different fields you should be able to use " OR " like this:

    Where Condition =     ="Asset_ID='" & [Forms]![Edit Record]![SearchForDevice] & "' OR Serial_Number = '" & [Forms]![Edit Record]![SearchForDevice] & "'"

     Open the form in form view with something in SearchForDevice. Then press Ctrl+G to open the Immediate window. Type ?[Forms]![Edit Record]![SearchForDevice] and press Enter. That will show you what value Access sees in that control. Is it what you expect?


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, February 19, 2019 6:37 PM
  • If you want to search two different fields you should be able to use " OR " like this:

    Where Condition =     ="Asset_ID='" & [Forms]![Edit Record]![SearchForDevice] & "' OR Serial_Number = '" & [Forms]![Edit Record]![SearchForDevice] & "'"

     Open the form in form view with something in SearchForDevice. Then press Ctrl+G to open the Immediate window. Type ?[Forms]![Edit Record]![SearchForDevice] and press Enter. That will show you what value Access sees in that control. Is it what you expect?


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Yup, It is what i am expecting to see. I just don't understand why the data flickers another value quickly when i hit enter to search. It always finds the desired record, it is just ugly seeing it flicker in all the text and combo boxes.
    • Edited by PoD Hazard Tuesday, February 19, 2019 6:59 PM
    Tuesday, February 19, 2019 6:52 PM
  • The asset ids are alphanumeric and are 8 characters long. Some of the devices are spares and have not been assigned an id yet. That is why i would like the ability to search by either serial numbers or asset ids within the same search box. 
    You can have two unbound combo boxes.  So long as the BoundColumn property references the primary key of the table in each, the visible column can be any non-key column.  The code behind each would be the same.

    Normally a primary key is a number data type, but for a primary key of text data type you simply need to wrap the value in literal quotes characters:

        .FindFirst "NameOfPrimaryKeyGoesHere = """ & ctrl & """"

    Each literal quotes character is represented by a contiguous pair of quotes characters.

    The unbound combo boxes can be synchronized by setting the value of the other to the value of the current combo box in its AfterUpdate event procedure.  Put this as the first executable code following the variable declarations.


    Ken Sheridan, Stafford, England

    Tuesday, February 19, 2019 7:07 PM
  • The asset ids are alphanumeric and are 8 characters long. Some of the devices are spares and have not been assigned an id yet. That is why i would like the ability to search by either serial numbers or asset ids within the same search box. 

    You can have two unbound combo boxes.  So long as the BoundColumn property references the primary key of the table in each, the visible column can be any non-key column.  The code behind each would be the same.

    Normally a primary key is a number data type, but for a primary key of text data type you simply need to wrap the value in literal quotes characters:

        .FindFirst "NameOfPrimaryKeyGoesHere = """ & ctrl & """"

    Each literal quotes character is represented by a contiguous pair of quotes characters.

    The unbound combo boxes can be synchronized by setting the value of the other to the value of the current combo box in its AfterUpdate event procedure.  Put this as the first executable code following the variable declarations.


    Ken Sheridan, Stafford, England

    I am sorry. You are losing me here. The only unbound text box i have would be where i would type in what i want to search. All the other text and combo boxes are linked to the table the data is being pulled from. 
    Tuesday, February 19, 2019 7:17 PM
  • Do you have anything else for form events such as Current, Activate, Before_Update, etc.? They might be firing because the macro changes the current record.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, February 19, 2019 7:25 PM
  • Do you have anything else for form events such as Current, Activate, Before_Update, etc.? They might be firing because the macro changes the current record.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Only thing is On Load which is set to GoToControl - Control Name - SearchForDevice
    Tuesday, February 19, 2019 7:29 PM
  • Hi. Just as a test, if you remove the second SearchForRecord action and simply enter a valid Asset_ID in the search box, does it work without the flicker?

       I removed the second SearchForRecord and still briefly flickers other values before I get the one I searched for. That is the only macro I have set. I am just using this to look up a record from my table so i can change some of its values and status in nicer looking form then just changing it within the dataset. 

    Okay, just as another test, try adding a new unbound Textbox on your form and call it "txtSearch." In the AfterUpdate event of this new Textbox, try entering the following code:

    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    
    With rs
        .FindFirst "Asset_ID='" & Me.txtSearch & "' OR Serial_Number='" & Me.txtSearch & "'"
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
    
    Set rs = Nothing

    The above is an [Event Procedure]. So, make sure the After Update event of txtSearch is set to [Event Procedure] instead of [Embedded Macro]. To enter the above code, click on the three dots (ellipsis) next to [Event Procedure] in the After Update event.

    If it works, does it still flicker?

    Hope it helps...

    Tuesday, February 19, 2019 8:25 PM
  • Hi. Just as a test, if you remove the second SearchForRecord action and simply enter a valid Asset_ID in the search box, does it work without the flicker?

       I removed the second SearchForRecord and still briefly flickers other values before I get the one I searched for. That is the only macro I have set. I am just using this to look up a record from my table so i can change some of its values and status in nicer looking form then just changing it within the dataset. 

    Okay, just as another test, try adding a new unbound Textbox on your form and call it "txtSearch." In the AfterUpdate event of this new Textbox, try entering the following code:

    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    
    With rs
        .FindFirst "Asset_ID='" & Me.txtSearch & "' OR Serial_Number='" & Me.txtSearch & "'"
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
    
    Set rs = Nothing

    The above is an [Event Procedure]. So, make sure the After Update event of txtSearch is set to [Event Procedure] instead of [Embedded Macro]. To enter the above code, click on the three dots (ellipsis) next to [Event Procedure] in the After Update event.

    If it works, does it still flicker?

    Hope it helps...

    That actually did the trick. So in reality i don't even need an actual search button anymore?
    Wednesday, February 20, 2019 2:39 AM
  • Hi. Just as a test, if you remove the second SearchForRecord action and simply enter a valid Asset_ID in the search box, does it work without the flicker?

       I removed the second SearchForRecord and still briefly flickers other values before I get the one I searched for. That is the only macro I have set. I am just using this to look up a record from my table so i can change some of its values and status in nicer looking form then just changing it within the dataset. 

    Okay, just as another test, try adding a new unbound Textbox on your form and call it "txtSearch." In the AfterUpdate event of this new Textbox, try entering the following code:

    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    
    With rs
        .FindFirst "Asset_ID='" & Me.txtSearch & "' OR Serial_Number='" & Me.txtSearch & "'"
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
    
    Set rs = Nothing

    The above is an [Event Procedure]. So, make sure the After Update event of txtSearch is set to [Event Procedure] instead of [Embedded Macro]. To enter the above code, click on the three dots (ellipsis) next to [Event Procedure] in the After Update event.

    If it works, does it still flicker?

    Hope it helps...

    That actually did the trick. So in reality i don't even need an actual search button anymore?

    Hi. Glad to hear it worked for you. The above doesn't use a button, but there's nothing saying you can't use one too. It's all up to you. If you want less clutter on your form, then maybe you can skip the button. Cheers!
    Wednesday, February 20, 2019 4:18 PM