Access desktop combobox bug? RRS feed

  • Question

  • Using the latest version of desktop Access in Office 365, on a local drive. 

    I have a bound form (with the property of "Single form"), in which I placed a combo box in the header, to allow me to go to a selected record. I used the date/time field as the unique field to select from.  The earlier records in the table it is referencing,  did not record the time. 

    Last Summer, I added some new features to the database and created this table new table.  One of the enhancements, included adding time to the date field.  The time is set to display in 24Hr format. 

    The original database included an Individual table and an Activities table, associating many activities to each individual.  This new table was created to also associate the many activities to an event.  The new table now contains hundreds of event records.  The event record has a date/time range recorded in two date fields. 

    I now have the thousands of activity records associated with both individuals and events.  The majority of the event records now have 0:00 as the time.  The more recent records contain actual times.

    When I select a record in the combo box that has 0:00 as the time, Access jumps to that record, as expected.  However, when I attempt to select an event record which contains a time other than 0:00, Access goes to that record briefly and instantly returns to the current record it was on, when I executed the request.  It refuses to go to any record with an actual time in the date field.

    I used the combo box wizard to create the control, using the 3rd option to "go to selected records" in the table.  The beginning date field is the primary sort field, set to Descending Order.  It would appear that Access has a bug related to this feature, when using date/time as the reference field.

    • Edited by wcstarks Tuesday, July 25, 2017 3:12 PM
    Tuesday, July 25, 2017 2:37 PM

All replies

  • Hi,

    Do you know if you're using an Embedded Macro or VBA?

    Tuesday, July 25, 2017 2:44 PM
  • I added more info to my original posting.  Please review.

    I wrote no Macro, or VBA code, myself.  I used the Access Create Combobox wizard, and made modifications to it in the resulting Design view, and also in the SQL view.

    Does that answer your question?

    • Edited by wcstarks Tuesday, July 25, 2017 3:18 PM
    Tuesday, July 25, 2017 3:16 PM
  • Can you post the properties for your combobox? For example, we'll need to know its Bound Column and Row Source.
    Tuesday, July 25, 2017 3:27 PM
  • Since I am a new member of the forum, I am not allowed to upload an image, until my account has been verified.  So, I'll write the properties.

    Row Source:

    SELECT Format([Begin_Date],"yyyy/mm/dd") AS dDate, Weekends.Weekend_Type FROM Weekends ORDER BY Format([Begin_Date],"yyyy/mm/dd") DESC;

    Row Source Type: Table/Query

    Bound Column 1 (referring to the Begin_Date)

    Control source is blank

    Access exhibited the same issue before I formatted the date field for display in the combo box.  I thought, maybe, if I formatted out the time, that might fix the problem, but it did not.

    • Edited by wcstarks Tuesday, July 25, 2017 3:45 PM
    Tuesday, July 25, 2017 3:39 PM
  • Hi,

    Actually, formatting out the time may have made it worse. Can you post what it was originally? Was it simply?

    SELECT Begin_Date, Weekend_Type FROM Weekends ORDER BY Begin_Date DESC;

    Tuesday, July 25, 2017 3:45 PM
  • Yes.  The issue was the same either way.
    Tuesday, July 25, 2017 3:56 PM
  • Okay, thanks. You said you can see the form display the selected item but then go back to the previously displayed record? If so, we'll need to see the code behind the combo. Do you know how to get it?
    Tuesday, July 25, 2017 4:00 PM
  • No.  I would like to be able to do so.
    Tuesday, July 25, 2017 4:02 PM
  • Sure. In the Properties window of the combobox, go to the Events tab. Look for the After Update event and click on the three dots to the right and describe to us what you get when a new window opens up (or copy and paste it here if possible).
    Tuesday, July 25, 2017 4:09 PM
  • After Update


    Ovject Type: blank

    Object Name: bland

    Record First

    Where Condition:

    ="[Begin_Date] = " & "#" & Format([Screen].[ActiveControl],"mm/dd/yyyy") & "#"

    Tuesday, July 25, 2017 4:13 PM
  • Hi,

    Make sure you have a backup copy of your database and then try changing the above line slightly as follows:

    ="[Begin_Date]>=#" & [Screen].[ActiveControl] & "#"

    And let us know if the behavior does not change. If it did change, let us know how. Thanks.

    Oh, by the way, please reset the Row Source back to without using the Format() function. So, it should be the same as my example earlier. Thanks.

    • Edited by .theDBguy Tuesday, July 25, 2017 4:21 PM
    Tuesday, July 25, 2017 4:20 PM
  • I didn't mention, that it said it was an embedded macro.  I copied and pasted the Where Condition.


    If the above does not work, we'll probably ask you to switch it to use VBA.

    Tuesday, July 25, 2017 4:22 PM
  • I copied and pasted both the row source code from you and the macro code from you.  It now doesn't work at all.  It remains on the current record and appears to make not attempt go anywhere.  There is no update of the screen.
    Tuesday, July 25, 2017 4:29 PM
  • Hi,

    Just so we're clear... Using the new row source allows you to select the right items, correct? And when you said you copied my changes to the macro, did you only copy the Where Condition part? It's only the Where Condition part I need you to change in the macro.

    Tuesday, July 25, 2017 4:31 PM
  • Yes.  I copied both codes as you described.  Row Source in the properties, and Where Condition in the Macro.  I did not mix them up.

    I can see the correct items in the list and select them, but nothing happens on the screen.  No attempt to go anywhere is apparent.

    Tuesday, July 25, 2017 4:36 PM
  • After selecting, the combo box shows the selected item date, which is different that the current record.
    Tuesday, July 25, 2017 4:37 PM
  • Well, VBA is difficult for me.
    Tuesday, July 25, 2017 4:41 PM
  • Hi,

    I just did a quick test, and my combobox is working as it should. I'll try to post a video in a second. I am suspecting the problem has to do with how you updated the data in the previous records. Stand by...

    Tuesday, July 25, 2017 4:41 PM
  • When I created the Weekend event table last Summer.  The first records added, for the remainder of the season, were the ones which included actual times.  They were added via an import procedure, importing from an excel form.  The process added a new Weekend record and linked the new activities to both the members and to the new Weekend record.

    Earlier This week, I created 200 some odd new Weekend records needed to link all the older activities to. 

    The dates/times in the original Weekend records were entered via the automated process for the activities from August of last year until last week.  The dates/times for the manually added Weekend records were enter manually, ignoring the time, which went in as 0:00.

    The import process was not written by me.

    Tuesday, July 25, 2017 4:53 PM
  • Either way, I suspect the problem is with the data and not with the code. Take a look at the following image to see how it works for me.

    All I did was create a new table and added three records in it with date and times. Then, I went back to the second record and removed the time component. Then, I created a simple form and added a combobox using the Wizard like you did.

    In the video, you can see as I select an item from the dropdown, the form is displaying a different record matching the one I selected.

    • Edited by .theDBguy Tuesday, July 25, 2017 4:57 PM
    Tuesday, July 25, 2017 4:56 PM
  • Both the Excel form and the tables have the date fields custom formatted as

    "mm/dd/yyyy hh:mm".  The dates are entered in 24 Hr format.

    Tuesday, July 25, 2017 4:58 PM
  • Hi,

    Just FYI... Formatting is different from data types. You can make a bunch of numbers look like a date, but the computer can tell if it is a real date or not. What is the data type of your table field?

    Tuesday, July 25, 2017 5:07 PM
  • The only difference I can see in your sample, is that my date/times include 0:00 when no time is entered.  Yours is just blank.

    In the table, the Data type is Date/Time, formatted as mm/dd/yyyy hh:mm

    Tuesday, July 25, 2017 5:21 PM
  • Hi,

    I could try to "format" my sample to display the 0:00, but it won't make any difference. What is displayed doesn't necessarily indicate what is stored.

    To help you find the root cause of the problem, it may be helpful to see your actual file.

    However, I would like to try another experiment with you.

    Let's try to use VBA rather than Embedded Macro for the combobox.

    So, in the After Update property, click on the dropdown arrow and select [Event Procedure] to replace [Embedded Macro] and then click on the three dots. You should get a different screen than last time.

    Wherever the cursor shows up (should be inside Private Sub and End Sub stub), paste the following code:

    Dim rs As Object
    Set rs = Me.RecordsetClone
    With rs
         .FindFirst "CDate(Begin_Date)=#" & Format(CDate(Screen.ActiveControl), "yyyy-mm-dd") & "#"
         If Not .NoMatch Then
              Me.Bookmark = .Bookmark
         End If
    End With
    Set rs = Nothing

    Let us know if it at least changes the records.

    Tuesday, July 25, 2017 5:37 PM
  • I replace embedded macro with Event procedure and inserted the above code as instructed.  But it still will not go anywhere new.  My original setting would at least go to those with 0:00 for the time, but now nothing happens.
    Tuesday, July 25, 2017 6:04 PM
  • Hi,

    Here's the updated version of my demo displaying the 00:00.

    And here's the VBA code I used for it:

    Private Sub Combo11_AfterUpdate() '7/25/2017 'thedbguy@gmail.com Dim rs As Object Set rs = Me.RecordsetClone With rs .FindFirst "Format(DateField,'yyyy-mm-dd hh:nn')='" _
    & Format(CDate(Me.Combo11), "yyyy-mm-dd hh:nn") & "'" If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With Set rs = Nothing End Sub

    Hope it helps...

    Tuesday, July 25, 2017 6:09 PM
  • .FindFirst "CDate(Begin_Date)=#" & Format(CDate(Screen.ActiveControl), "yyyy-mm-dd") & "#"

    I think you probably meant:

        .FindFirst "DateValue(Begin_Date)=#" & Format(CDate(Screen.ActiveControl), "yyyy-mm-dd") & "#"

    The DateValue function strips off the time of day, the CDate function does not.

    Ken Sheridan, Stafford, England

    Tuesday, July 25, 2017 9:27 PM
  • Hi Ken, Thanks for the assist. Cheers!
    Tuesday, July 25, 2017 9:45 PM