List/Combo box not finding record

[snoopy4801]

I'm just inserting a simple list box on a form to search by employee last name and I have the employee ID as the primary key. When I go to create my combo box the option of "Find a record on my form based on the value I selected in my list box" does not appear as an option.

Does anybody know how to turn this on in the List box wizard? All of the books say this is what needs to be working....

Arrgh.... so frustrating!!!

 

[SOS]

The form needs to be bound to either the table or a query of the table for you to get the third option. If it is an unbound form there is nothing for it to search so it doesn't give you that option.

 

[missinglinq]

As SOS said, the form needs to be bound to the table/query, with controls bound to the various fields of the table/query, else how would Access know where to retrieve the record to?

 

[snoopy4801]

Thank you for your help, all of my other controls are pulling from the right tables so when I go into look at the properties of the form I have the correct control source.

should I be looking somewhere else?

The form and subform are working perfectly it's only when i go to add a control box or list box that no matter what i put in the data source I can get the list of employee names to view, but I'm not able to select any of them thus in turn it won't change the employee data in my form.

Hopefully I didn't make it sound worse now.

 

[SOS]

I think you may need to post your database for us to actually see what is going on. Can you do so?

 

[snoopy4801]

Here are the properties of my form. I am just trying to get the list box to search for employees by last name.

I know the List box says unbound, but the Form has the record source populated.

I can't post the database, but I can send screenshots if that helps at all?

 

[SOS]

1. When you do the wizard, place the combo in the form header and not in the detail section.

2. It shouldn't be looking for last name but for a specific record for that form based on the primary key of the recordset. If you search on name using the combo then it will only go to the first it encounters. If you want all records for a certain name then you will need a different way which will entail FILTERING the recordset and not using the FIND RECORD code which the combo wizard uses.

Which are you needing?

 

[snoopy4801]

I tried putting the combo box in the detail header, but I still don't get the third option in the wizard.

I don't mind it going to the first name with those letters but here is what is happening.

-Combo box starts with Adams
-The first employee's information in the form with department and so on says Alan Adams and all of his data is correct
-But if I type in Smith, the combo box will find Smith, but it will leave Alan Adams' info in the form and not change to Joe Smith's employment data

 

[SOS]

-But if I type in Smith, the combo box will find Smith, but it will leave Alan Adams' info in the form and not change to Joe Smith's employment data

Ah, very important detail there. Glad you said it because that does make a difference. When typing in, instead of selecting, are you also hitting the enter key after getting the right name up? If not you should, that should activate the combo box's After Update event which should then fire the other code. Also make sure the LIMIT TO LIST is set to YES.

 

[snoopy4801]

See I think that is where the problem is at. If i hit enter it just moves to the next person in the list. not the person I typed in.

But I did check the "After Event" box and it is blank.

But I do have Limit to List set to yes.

I really do appreciate the help!

 

[SOS]

See I think that is where the problem is at. If i hit enter it just moves to the next person in the list. not the person I typed in.

But I did check the "After Event" box and it is blank.

But I do have Limit to List set to yes.

I really do appreciate the help!

Okay, next thing to check -

What event does the code reside on for the combo box which works when you select. Is it on the combo box's After Update event (to tell, select the combo while the form is in design view, select the EVENT tab on the properties dialog and then look for [Event Procedure] (if you are on Access 2003 or earlier) and [Embedded Macro] if on Access 2007.

See where it lies and then go have a look.

 

[snoopy4801]

This is what the Event box looks like and I used the wizard to create the combo box.

 

[SOS]

Do you have the location where the database resides set as a trusted location?

 

[snoopy4801]

OMG!!! We were just talking about that. If that would affect this from working, then I'm 99.9% sure that is the problem.

We just upgraded and IT hasn't given us access to set "Trusted" permissions for our databases yet so we are just authorizing each session as we use them.

 

[SOS]

If you didn't happen to select the ENABLE MACROS button when you opened the database, then it wouldn't do anything when you go to add the combo box (no code or macro would be added as you can't run any).