I have a form with a bunch of fields for a record. I would like to use a combo box so the user can select the record they want to display. What is the best way to do this? Is there another way besides assigning the values of the combo.column(x)? The biggest problem with that is the record has more fields than a combo box will allow. Is there a way to use the primary key in the combo box to access the record on the form?

Thanks

(I posted this yesterday to a similar question.)

Using a command button is one of the easiest ways:

1. Create a form that displays your data. This form will display ALL the data. It should be based on a simple query which retrieves all the data. (You’ll have to create the query before you create the form, obviously.)

2. Drop a COMMAND BUTTON on the form where you have the combo box. When you do this, the command button wizard will start. All you have to do is follow the instructions when they come up. Specify that you want a form operation and you want to open the form. Specify that you want to select specific records not the whole table. When asked, click on the name of the combo box and the location field in the table to create the linkage. After you have finished with the command button wizard save the form and then you are ready to open the other form and specify a location and click on the command button.

Robert

P.S. you don't need anything in the combo box other than the identifying field. The other fields show on the form.

Thanks for your help! I am not sure why this works like this one time and not another. I must not know what control this but how do you get the drop down list to show every record without the navigation arrows on the right? Right now, it only shows 2 record and the arrow up and down keys to see the rest.

If you don't want to click to show the information then you have to use a list box instead of a combo box.

I must not be explaining correctly. See my attachement. I would prefer the style of the box on the left.

Just to clarify this, you have to be using a Bound form and you want to choose the 3rd option on the Combobox wizard to create a Search combo.

After the wizard has generated the combo, I would go into the query and edit it based on your screen shot. Insert a second column to something like:

Fullname: [lastname] & ", " & [Firstname]

in the set the last and firstname columns to not Show and sort first by last then by first. The resulting SQL should look something like this:

SELECT keyfield, [lastname] & ", " & [Firstname] As Fullname FROM table
ORDER BY [lastname], [Firstname];

Change the column count to 2 and the column widths to 0";2"

On the left you have specified ListRows property of 4 or greater. Scroll bars are inserted automatically when there are more rows than can be displayed at once. The combobox LisRows property is under Format.

Robert

Lol...I still am not making myself clear. The combo box works perfectly. I like the STYLE of the left hand box because it displays all of the records in "one drop down" list as opposed to the one on the right which only shows a few records and a 'navigation' arrows on the right. Or, am I not understanding what you are telling me on how to do this? :-)

I have had to do this before. Here is how I select a record from a table with combo boxes

1)on the combo afterUpdate event add:

Sub ComboName_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Fname] = '" & Left(Me![ComboName], InStr(Me![ComboName], ",") - 1) & "' and [Lname] = '" & Mid(Me![comboName], InStr(Me![ComboName], ",") + 2) & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

2)In the combo properties make sure you have the Row sourse type to "Table/Query" and in the Row Source you put a select statement to fill in the Fname (Firstname) and Lname(Last Name) For example:

SELECT DISTINCTROW ([TableName].[LNAME] & ", " & [tableName].[Fname]) FROM TableName Where ORDER BY [TableName].[LNAME];

This will setup the combobox so when you click on it, all the names are in order.

3) make sure you form is connected to a record source to get the data.

When all is done, when you click on a name, all the other fields will populate/ display with the persons name you select.

Good Luck

End Sub

.

RedLine,

Your method contains several problems.

First, one should use more descriptive control names:
cboPeopleSearch is much better than Combo187

Second, You should always have the combo return the PK value of the record rather then search on names. Names are not necessarily unique so your method will always return the first John Doe, leaving other John does not found.

Third, when you use the Wizard to create a search combo it will generate similar code for you, but based on the PK not the displayed data.

Not sure whose advice you're taking here but did you change the ListRows property?

Yeah after I posted it I edited the names,etc I know the rules of using better names HA HA... I realize that the it should return the PK value, but this is something that I have used in the past and have never had a problem with. I have attempted to use the wizard in the past and I could never really make it do what I wanted. So I just do the work around. I try not to use it.
Thanks for the catch though!

The wizard does have a problem when you need to display more than one column from the table. In that case you go back to edit the query as I suggested earlier. But the code generated should be left alone so it uses the PK in the Find First.

Yes. You answered what I was specifically asking (about allowing more records to show without a navigation bar) after the initial post. Thanks and sorry for all the confusion!