?
Solved

MS Access ComboBox not selecting record

Posted on 2011-10-08
31
Medium Priority
?
338 Views
Last Modified: 2012-05-12
I have a combo box which has 3 columns.  The first column width is 0" which I am using to get the value.  The combo box is populating and displaying 2 columns (first name, last name) but when I select a name it is not displaying on top (highlighted section in image).

Any ideas?
Data-Entry.jpg
0
Comment
Question by:CipherIS
  • 13
  • 7
  • 6
  • +2
31 Comments
 
LVL 75
ID: 36936930
What is the setting of your Column Widths property ?

Seems it s/b something like

0;1;1

mx
0
 
LVL 75
ID: 36936933
Also Column Count property s/b set to 3

Also ... these statements seem to contradict:

" The combo box is populating and displaying 2 columns (first name, last name) "

"but when I select a name it is not displaying on top (highlighted section in image)."

?
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36936937
Here is some standard code for synching a record with a selection in a combo box:
Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36936941
For this to work, you should have the form bound to the appropriate table, and the combo box's row source the same table, or a query based on it.  Then the From and To textboxes should display the values from the selected record.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36936947
If this isn't about synching a form to the selected value, but just displaying data from columns of the combo box, you can use control sources like this:

=Me![cboSelect].Column(1)

This will display the value in the 2nd column (column numbering is zero-based in code).

For this to work, the From and To values must be in the combo box's row source.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36936968
As Helen said, you should use the correct column index.

I'm understanding  you have something like this:

Column 0         1                 2
           ID        First            Last

Another approach if you dont need to edit the textboxes (ie:just display the information) is to set their control sources as follows (include the  = sign):

For Firstname textbox -
= YourCombo.Column(1)

For Lastname textbox -
= YourCombo.Column(2)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36936970
(Me! doesn't work in the control source property AFAIK)
0
 
LVL 75
ID: 36936977
I don' think the OP is trying to populate the From/To text boxes with the name.  It appears that the selection is not showing in the Combo box ... per the image.

mx
0
 
LVL 75
ID: 36936983
Me does not working in that context, but Forms does.

I can however, vaguely remember some instance where ME did work in a similar expression ... although technically it should not, since it only works in VBA code.

mx
Capture1.gif
Capture2.gif
0
 
LVL 75
ID: 36936985
And the Forms!  approach can be simplified of course to just what is shown in the image.

mx
Capture1.gif
0
 
LVL 1

Author Comment

by:CipherIS
ID: 36937469
I've attached a pic of the combo box properties.  Like i said - it is filling with data but i can not select a row and have it display as the image above (from DatabaseMX) with the "109" value.
Data-Entry-combobox.jpg
0
 
LVL 75
ID: 36937472
You have the Bound Column set to zero. That is a special case.  Set the Bound Column to 1.

mx
0
 
LVL 1

Author Comment

by:CipherIS
ID: 36937477
changed bound column to 1 - didn't work - i can select the name from the drow down but it does not display it in the combobox.
0
 
LVL 75
ID: 36937486
Sorry ... Make the Column Widths property this

0;1;1 ...

but really that's only going to display the First Name.  So, what you need to do is ... in the SELECT statement ... concatenate the First and Last name into one field ... then set the Column Width to this:

0;2

and keep Bound Column at 1

mx
0
 
LVL 75
ID: 36937491
That part of the SQL would be:

tblPeople.FirstName & " " & tblPeopleLastName As FullName

Replacing the separate First/Last name.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 36937502
maybe this will help clarify - i can select from dropdown combo box but it will not select name and close.  i will concatonate the names to make it easier.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36937508
What is the control source of your combo?  If it is not editable, such as a field in a non updatable query, you may see behavior like that.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36937517
Also double check the locked and enabled properties.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 36937537
enabled = true and locked = false
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36937561
How about the recordsource of your form (and the control source of your combo box).  Is the query behind the form definitely an updateable query?
0
 
LVL 75
ID: 36937563
Can you upload a sanitized version of the db ...

?
0
 
LVL 75
ID: 36937591
There is no Control Source.  Everything "Looks Ok".
And possibly I'm not totally clear as to what is or is not happening.  It seems ... you make a selection ... and it does not appear in the combo.  So summarizing ... to do list:

Change the SELECT statement Row Source to concatenate the F/L Name - to replace separate F/L Name columns.  
Column Count 2
Bound Column =1
Column Widths: 0;2

On other thing. Set the Allow Auto Correct to No.

If this doesn't fix it ... upload the db.

mx
0
 
LVL 6

Expert Comment

by:JVWC
ID: 36937605
Has this DB been recently converted from an earlier version of Access?

Cheers
JC
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36937618
So when you click an item in the list, the list stays dropped down, and the textbox portion of the combo does not populate?

One more thought - do you have any code to conditionally lock this control that may be misfiring or otherwise at fault?  (It is definitely behaving like it is locked for editing.)
0
 
LVL 1

Author Comment

by:CipherIS
ID: 36937636
I've uploaded the database.  open the form.  click on the dropdown and you will see that it will not select and display on the combobox.
db1.mdb
0
 
LVL 1

Author Comment

by:CipherIS
ID: 36937640
BTW - I did not create this dbase - I picked it up from another dev
0
 
LVL 75
ID: 36937645
I see the problem
See the Form Allow Edits property to Yes.

mx
0
 
LVL 6

Expert Comment

by:JVWC
ID: 36937647
To expand: I have seen similar problems before and it has always been with an earlied DB running in A07. Strangely, it may work on other machines...
The only fix was to rebuild the form in A2007.

One example here;
http://www.experts-exchange.com/Microsoft/Applications/Q_26689604.html?

Cheers
JC
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36937651
Even though the Combo has no Control Source, the the Form Allow Edits is set to No ... Combos and List boxes do not work.

Set Allow Edits to Yes and it works.

mx
Capture1.gif
0
 
LVL 75
ID: 36937652
Attached db is working ...

mx
db1-MX01.zip
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 36937680
Thx - resolved my issue.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question