• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

MS Access ComboBox not selecting record

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
CipherIS
Asked:
CipherIS
  • 13
  • 7
  • 6
  • +2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What is the setting of your Column Widths property ?

Seems it s/b something like

0;1;1

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Helen FeddemaCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Helen FeddemaCommented:
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
 
Helen FeddemaCommented:
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
 
mbizupCommented:
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
 
mbizupCommented:
(Me! doesn't work in the control source property AFAIK)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
And the Forms!  approach can be simplified of course to just what is shown in the image.

mx
Capture1.gif
0
 
CipherISAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You have the Bound Column set to zero. That is a special case.  Set the Bound Column to 1.

mx
0
 
CipherISAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
That part of the SQL would be:

tblPeople.FirstName & " " & tblPeopleLastName As FullName

Replacing the separate First/Last name.
0
 
CipherISAuthor Commented:
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
 
mbizupCommented:
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
 
mbizupCommented:
Also double check the locked and enabled properties.
0
 
CipherISAuthor Commented:
enabled = true and locked = false
0
 
mbizupCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you upload a sanitized version of the db ...

?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
JVWCCommented:
Has this DB been recently converted from an earlier version of Access?

Cheers
JC
0
 
mbizupCommented:
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
 
CipherISAuthor Commented:
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
 
CipherISAuthor Commented:
BTW - I did not create this dbase - I picked it up from another dev
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I see the problem
See the Form Allow Edits property to Yes.

mx
0
 
JVWCCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Attached db is working ...

mx
db1-MX01.zip
0
 
CipherISAuthor Commented:
Thx - resolved my issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 13
  • 7
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now