Search form with ComboBox

[Voodeux2014]

I know how to do this in older versions of Access but cannot figure it out in Access 2013. I am trying to set up a combo box on a form that will allow you to select an employee. Once the employee is selected in that combobox I want the form to update and show me all the information for that employee. I have a search button set up, but figured this would make it easier for some users.

[pbaldy]

If the form is based on a table, the combo box wizard should offer you the option to "Find a record...".

[Voodeux2014]

It only give me the normal two options, not the third that would bring that. Would i be because the form has information from more than one table that are all related by the employee ID?

[pbaldy]

Yes, you'll only get the option if the form is based on a single table. You can make a test form with that configuration, add the combo to it, then copy the code/macro to your form.

[Voodeux2014]

And to top it off now. I had my form working to where it would go to a new record on loading. I added new fields to the form from an additional related table and now it gives an error that states you cannot go to the specified error. Also it will only display one record.

[pbaldy]

If I had to guess, you've made the underlying query read-only, so it can't go to a new record. Typically related tables would be in subforms, not all in one recordsource.

[Voodeux2014]

I didn't Make an actual query. Would it be easier to attach the database and have you take a look at it to see what I am doing incorrect?

[pbaldy]

You can if you want. It doesn't matter if you use a saved query or SQL in the recordsource, the same rules apply. Here's info on what makes a query read-only:

http://allenbrowne.com/ser-61.html

[Voodeux2014]

Well it will not let me upload the database for some reason. Keeps giving me an error. I dont have any queries made at the moment so it will have to be SQL somewhere. But I am just using the tools provided and not digging into the actual coding of the database. So that link would be helpful if i knew where to locate that info. Right now I only have one record that has all the data included and want to get it functional before I start adding all the rest of the information.

[pbaldy]

My guess is that you've clicked on the record source property of the form and built a query. In the properties window, does the property start like:

SELECT ...

if so, you've built a query and the above rules apply to it. To upload the db, make sure you do a compact/repair and then zip.

[Voodeux2014]

Yes it does state that in the properties. What do I need to do to actually change that?

[Voodeux2014]

also if it helps, I believe it has to do something with the Phones table that was added. It was working before the addition of that table.

[pbaldy]

Well, you can correct the display of records by editing the join between the employee and phone tables. I think you'll still end up with a read-only form though. From a practical standpoint, can't a person have multiple computers or phones? I would expect both of those to be handled like you have monitors, and the form itself to be based only on the employee table.

[Voodeux2014]

Well it is relatively a small company. Each person gets a dedicated computer and phone. But if it is required to put it in the table form I will see if i can get it to work that way.

[pbaldy]

Another alternative, and it's how I would design it if I was certain there could only be one of each, is to reverse your logic. I'd have fields in my employee table for which phone/computer each employee had. Your form would be based only on the employee table, and each of those fields would be represented by combo boxes that got selections from the computer/phone tables.