how to add a combo box in a navigation form header Access 2013 & 2010

Hi guys,

I'm creating a navigation form in access and I'm trying to add a combo box in the header of the form to search for records in a subform which is in a database sheet format. For example, I should be able to type or select the name of an employee and take me to that record in the database sheet form which is a subform in the navigation form.

Also, In the subform in the navigation form I would like to click on the name of the employee and be able to open the form where all the detail information of this employee is.

I know that to do these two things I have to write a code or create macros but I tried everything and I can't figure it out!! Can you give me a hand?

I would appreciate!!!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can filter data in your datasheet like this:

In the AfterUpdate event of your combo, do something like this:

Me.YourDataSheetSubformCONTROL.Form.Recordsouce = "SELECT * FROM YourTable WHERE YourEmployeeField='" & Me.YourCombo.Column(0) & "'"

Obviously you'd have to change the names of the tables, fields and controls to match your project.

Also be aware of the "YourDataSheetSubformCONTROL". This is the name of the Subform CONTROL on the main form, and may or may not be named the same as the form you're using as a Subform.

To open "the form" where all the detailed employee info is:

On the Click or DoubleClick event of your datasheet do something like this:

DoCmd.OpenForm "YourDetailForm", , , "YourEmployeeIDField=" & Me.EmployeeIDControl

Again, you'd have to change that to reflect the table, field and control names in your project.
hugonietoAuthor Commented:

Thanks a lot for the help!! I tried what you suggested and non worked...

For the filter or search part I tried what you said and when I select a name from the combo box and enter it and I just get a blank page inside the subform.... it doesn't take me to that record I selected even tough is in there. When I clear the combo box or click on the tab in the navigation form it goes back to showing all the records in the subform. Do you have any idea of what is going wrong or what I'm missing?

For the other part... I had to create a macro in the for subform so it could open the detail form with the record I click on... it works now!!

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns=""><UserInterfaceMacro For="txtID" Event="OnClick"><Statements><ConditionalBlock><If><Condition>[Form].[Dirty]</Condition><Statements><Action Collapsed="true" Name="SaveRecord"/></Statements></If></ConditionalBlock><ConditionalBlock><If><Condition>IsNull([ID])</Condition><Statements><Comment>Open the form to a new record if the current ID is Null</Comment><Action Collapsed="true" Name="OpenForm"><Argument Name="FormName">OrderDetail</Argument><Argument Name="DataMode">Add</Argument><Argument Name="WindowMode">Dialog</Argument></Action><Action Collapsed="true" Name="Requery"/></Statements></If><Else><Statements><Comment>Use a TempVar to pass a filter to the OpenForm action</Comment><Action Collapsed="true" Name="SetTempVar"><Argument Name="Name">OrderDetail_ID</Argument><Argument Name="Expression">[ID]</Argument></Action><Action Collapsed="true" Name="OpenForm"><Argument Name="FormName">OrderDetail</Argument><Argument Name="WhereCondition">[ID]=[TempVars]![OrderDetail_ID]</Argument><Argument Name="DataMode">Edit</Argument><Argument Name="WindowMode">Dialog</Argument></Action><Action Collapsed="true" Name="RemoveTempVar"><Argument Name="Name">OrderDetail_ID</Argument></Action><Action Name="RefreshRecord"/></Statements></Else></ConditionalBlock></Statements><Sub Name="ErrorHandler"><Statements><Action Collapsed="true" Name="MessageBox"><Argument Name="Message">=[MacroError].[Description]</Argument></Action></Statements></Sub></UserInterfaceMacro></UserInterfaceMacros>

Open in new window

hugonietoAuthor Commented:
Sorry about the code below the thanks word... That was a mistake! just ignore it!!
ON-DEMAND: 10 Easy Ways to Lose a Password

Learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees in this on-demand webinar. We cover the importance of multi-factor authentication and how these solutions can better protect your business!

hugonietoAuthor Commented:
I found an article which does exactly what I want to do!!!! but It just doesn't work for me!!!! the only thing Im replacing is the name "Company" to the name of my field I want to search for which is by the name of the employee.

What am I doing wrong!!

Also, when I tried to set the LimitToList property to No on my combo box I get the error on this picture!! any idea how to fix it?  


hugonietoAuthor Commented:
I found out the solution for my problem. I had to look a similar access form and I dig into the sections I wanted in my forms...... I found out the macros that did this functions I wanted. I just had to modify these macros and implement them in my forms.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hugonietoAuthor Commented:
I found the solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.