Thread: combo dropdown empty
03-02-13, 17:55 #1Registered User
- Join Date
- Aug 2006
Unanswered: combo dropdown empty
I have a Customer Select form consisting of one unbound combobox control. The user is intended to enter a customer name helped by the dropdown. When the control is entered, and the user starts typing a name, the control should show the first occurance that start with those letters, and the dropdown should show that and the ones that follow (the source is a query in alpha order). I've used this in several other instances with no problem, and thought I'd copied everything from a working instance.
In this instance the control itself acts correctly, following the typing of the user and the dropdown occurs, BUT it is EMPTY.
This is an mbd started originally in 2003 but running in 2010.
The form RecordSource is qryCustomer; popup=No; modal=No; single form.
The combobox ControlSource=blank; format=blank; columncount=1; listrows=16; grow=no; shrink=no; displaywhen=always; RowSource= SELECT DISTINCT [qryCustomer].[FullName] FROM [qryCustomer]; type=Table/Query; boundcolumn=1; limittolist=yes; allowvaluelistedits=no; inheritvaluelist=no; showonlyrowsource=no; enabled=yes; locked=no; autoexpand=yes.
The AfterUpdate property will call a routine to use the name selected.
Since this is working in other instances, can anyone spot what I've screwed up?
03-03-13, 08:35 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
Is the Field that holds the customer name formatted in any way, shape or form, at Table, Query or Form level?
Access 2003, like all versions, had bugs when first introduced, and one of the most reported bugs involved 'blank' Comboboxes! It turned out that if the Field was formatted anywhere, when displayed in a Combobox it would be blank.
You can easily test to see if this is the problem, because although the Field doesn't display, it is actually there! If you already have something in the AfterUpdate event of the Combobox, drop it down and click on one of the blank areas and see if the AfterUpdate event does what it is supposed to. If you haven't gotten that far, place a Messagebox in the event:
Private Sub ComboboxName_AfterUpdate() MsgBox Me.ComboboxName End Sub
If it proves out that there is actually something 'there,' i.e. the AfterUpdate event works properly, then my guess would be that the above mentioned bug is at work, here. I'm not really sure what to tell you to do about it, giving your situation of running the 2003 app under Access 2010! The fix before, when running a 2003 app in Access 2003, was to simply remove the formatting (which usually involved removing the > symbol that had been used, at some level, to make all of the Field display in all caps), or applying a hotfix found here:
Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007
I suppose if you still have your copy of Access 2003, you could download and apply the hotfix, re-save the 2003 app, and go from there, but given the circumstances, I'd simply remove the formatting.
With a Field that holds something like a name, formatting is often used to do things like capitalize all of the name, or the first character in the name, and can be done at Table or Form level with a simple > in the Format Property, or can be done though code in the AfterUpdate event of the Textbox on the Form where it is originally entered, so it may take a while to find it, especially if this app was originally designed by someone else.
If it appears that there is nothing actually 'there,' i.e. if the AfterUpdate event does nothing when you select a 'blank' item, I have no idea!
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
03-03-13, 17:33 #3Registered User
- Join Date
- Aug 2006
Yes, I was aware of the 2003 format problem and had already looked for a format at each level. The field in the query [FullName] is a a derived field =[LastName] & ", " & [FirstName] where LastName and FirstName are fields in the query. I opened the properties on FullName and even thought it showed Format=blank; I tried to force it by putting in ""; no luck. My thought now is that a format may be implied or forced by the ", ". My next experiment will be to change to a 2 column dropdown with LastName and FirstName in separate columns.
I'm out of commish for a couple weeks and will get back to this puzzle mid-month. In the meantime, additional thoughts from anyone are welcome, and thanks.