Access desktop combobox bug?

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.

Answer 1

Hi,

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

Answer 2

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?

Answer 3

Can you post the properties for your combobox? For example, we'll need to know its Bound Column and Row Source.

Answer 4

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.

Answer 5

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;

Answer 6

Yes.  The issue was the same either way.

Answer 7

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?

Answer 8

No.  I would like to be able to do so.

Answer 9

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).

Answer 10

After Update

SearchForRecord

Ovject Type: blank

Object Name: bland

Record First

Where Condition:

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

Answer 11

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.

Answer 12

I didn't mention, that it said it was an embedded macro.  I copied and pasted the Where Condition.

Hi,

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

Answer 13

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.

Answer 14

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.

Answer 15

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.

Answer 16

After selecting, the combo box shows the selected item date, which is different that the current record.

Answer 17

Well, VBA is difficult for me.

Answer 18

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...

Answer 19

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.

Answer 20

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.

Answer 21

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.

Answer 22

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?

Answer 23

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

Answer 24

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.

Answer 25

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.

Answer 26

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
'[e-mail]

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...

Answer 27

.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

Answer 28

Hi Ken, Thanks for the assist. Cheers!