Dumb question about Combo Boxes and Lookups...

[ToddK]

I am trying to create a combo box on a form that will allow the user of my database to select a value from the total possible list of values, and I want, when this value is selected, for the record displayed in the panel below the combo box to refresh to reflect the record that matches the value selected in the combo box. I expected this to be easy - but it has proven anything but. I can lay down the combo box - but when I select values from it, it does not move the "pointer" to the record which matches the selected value and show me the rest of the record.

Unfortunately, I also want to be able to enter new records from the same form...I don't know if this makes the job much harder or not.

Can anyone help me with what no doubt is a dumb question? I am using Access 97...

 

[LarryJ]

Should be fairly easy.

Make sure you have the wizard turned on before you add the combobox to your form. When you do you will have three options on how you want to handle the combobox. The third one says something like "find records on my form based on the value in the combobox". This is what you want to select.

Now the form must be based on a table/query, not an unbounded form. If this is true, then when you select a value in the combobox, the form should scroll to the selected record.

I'll keep an eye on this thread and try to help, if this isn't working for you.

 

[ToddK]

Dumb question...

Larry: I already pursued what you have indicated, but it did not do as you suggested...the behaviour did not match the expected.

How would I know if the data was based on unbound form? I can tell you that I have added several hundred records to the tables using this form, which would suggest it is bound?

I already have a simple text box mapped to the key field - which is called Source. Source contains numbers (representing record numbers) - and it is the primary key (I think I got that right). My combo box, ideally, would contain the results of 3 fields - Source, Author and Year - when I use the wizard, it discourages my use of Source (because it is primary key). When I actually try to use the combobox, it will not bring the record data that matches the choice with it in the remainder of the form.

Does that help any in explaining what behaviour I am seeing?

 

[Dugantrain]

To use a combo box to find records, you need to base it on the Primary Key Number field. Delete the text box that this field shows up in and then use the Wizard to create a Combo box and base it on that field. As Larry suggested, use the third option "Find a record on this form...". You can use a combo box look-up for any of your fields, but if data in that field shows up in more than one record, then the combo box will display that data more than once (for example, if an Author is in 10 different records, then he'll show up 10 times in your combo box) .

 

[ToddK]

Still dumb question about combo boxes and lookups

OK, perhaps I should describe what I am doing.

My table contains a self-generated primary key called AddressID. My table, called Bibliography, contains a number of fields; for the sake of argument, the fields I would like to include in my combo box, which I ultimately want to use to display the whole records (other fields, in simple text boxes, are on the form) when I select an entry from the combo box, are called Number, Author and Year.

I have a form created already, linked to the table called Bibliography. Now, I want to lay down my combo box. I open the form in Design view, and from toolbox, select a combo box. I draw it where I want it to go, and when I release, I get the wizard which asks: How do you want your combo box to get its values? I select "I want the combo box to lookup the values in a table." I am next asked to select source, and I choose table name (Bibliography). I then select fields from a selector - I include the primary key, the Number, Author and Year. It then asks me to size things, which I do. The last question posed is about remembering the value or storing it in field. I select "remember". I then enter a name in the next window, and I am done. However, the combo box shows text in it saying "unbound".

I can select the items in the list, but when they are picked, it does nothing to move to the record selected -> the other fields on the form do not update. Can anyone figure out what I am missing or doing wrong....

I am sure it is something really really stupid...I just want that combo box to select the right record based on my selection in the combo box, and show me all of the fields for the matching record.

Thanks to anyone for helping/understanding this Friday rant.

 

[Russell Hauf]

Hi Todd,

Not a dumb question. What do you want to show in the combo box?

Basically, you should base your form on the table, and base your combo on the field you want to show and the AddressID (not the whole table - write a query with only those 2 fields, then base the combo on that query. Base the form on the whole table). The bound column should be the AddressID (either 1 or 2, since the query has 2 fields), and you should set that column's width to 0. So say you select AddressID and Author, and you want the user to pick the author to see the rest of the details. In the column widths line of the combo box properties, put

0; (Access will change to 0"

And the bound column would be 1.

Ok, now in the AfterUpdate event of the combo box, put something like:

DoCmd.ApplyFilter , "AddressID = Forms!MyFormName!MyComboBoxName"

Hope this helps,

Russell