filter using combo box quit working once form was put on navigation form

[robsworld78]

I have a bunch of forms created which have an unbound combo box which is used to search the records.

On the "record source" for my "employees" form I have a query looking up all the fields in the table and the field "employeeID" with a critera Like [Forms]![Employees].[myselector] & "*"

The search works great when I open the form, if nothing is in the combo box all records show, once I select a record in the combo box it filters out the right record.

Now I'm trying to setup a "navigation system" and using the access 2010 navigation form, however once I add any form to it that has one of these combo box searches the form pops a dialog box when selected via the tab. If I click the combo box it still shows the record names however when I click a name a dialog box pops up and asks for me to input what it should figure out on its own. If I enter an employee ID it will retrieve the correct record

It seems like it can't locate the "myselector" combo box anymore to compare the ID now that's its in a subform on the navigation form. I tried changing the like statement to reference other areas of the database but can't come up with anything.

If anyone can help that would be great.
Thanks

 

[robsworld78]

Surely someone has had this happen to them, please help...

 

[John Big Booty]

Given that you have moved from referencing the controls on your form directly and moved to referencing them on a Sub-form, perhaps this link will help you get the correct syntax.

Caveat emptor: I've not used Access '10 or "Navigation" forms

 

[robsworld78]

thanks for the link, I tried a few things but got no where, no really sure what words to change in syntax. That's a great page but would be better if it explained which words in the syntax refered to what.

Whatever I type in criteria in the record source under employeeID it will just give a dialog box with what I typed in asking for an ID number in the end. If I put a valid number in it will show the record.

Does that mean the combo box isn't finding the "employeeID" field on the "employee form" so it has nothing to compare to so it has to ask?

Do I change all the words in a syntax like this? I know I change "subform1", I'm guesing to "navigationsubform" but do I change "form" and "recordsource" as well?

Me!Subform1.Form.RecordSource

Then on code like below do I change "parent"? I'm guessing I leave "me" alone, how about "recordsource"?

Me.Parent.RecordSource

Any ideas?

 

[John Big Booty]

Sorry, I'd really need to take a look at your DB, but given that I don't have Access '10, it's a bit of a moot point

 

[robsworld78]

What version do you use? It's build in 2007 format.

 

[John Big Booty]

I've got '03 here and '07 at home so post a copy and I'll take a look when I get home.

 

[robsworld78]

sounds good, thanks!

This is only my 2nd database I've built so let me know how I've done as of yet, I think its coming together nicely. I still have lots of changes to make on forms for the looks end but that I'll leave till I'm done.

Almost Everything runs from the "orders" form, most reports need to run from that form because they get filtered, I'm sure you'll figure it out. The point of the database is to track inventory for different employees.

You'll see how the forms work on there own but not on the "navigation form"

 

[John Big Booty]

Just tried to open your DB using "07, and it says "Unrecognised database format"

 

[robsworld78]

ah that sucks, I guess it is in '10 format, I thought it was '07 because at the top in brackets it says (Access 2007)

 

[John Big Booty]

Try doing a "Save As", and see if that helps

 

[robsworld78]

I looked at that option and under file type only access database shows up. In my options under default file type for blank database its set to 2007, it doesn't even have an option for 2010, it shows '03 as well.

So it is in the only file type I can go unless I go lower but can't change an existing db

 

[John Big Booty]

Perhaps someone with '10 can take a look at it for you.

 

[JANR]

Change the criteria for the recordsource for each of the forms to:

Like [Forms]![Navigation Form]![NavigationSubform].[Form]![myselector] & "*"

and it should work again.

JR

 

[robsworld78]

Thanks JR works like a charm, I was actually just getting back to that problem as I needed to solve it to start moving on so you're reply couldn't have come at a better time.

 

[JANR]

No problem

JR

 

[tmaleshafske]

I am having the same problem and don't exactly understand what I need to do. Specifically where do I go about changing the record source? Do I need to do it on the original form or on the navagation form? Could someone please walk me through how to do this it would be greatly appreciated. Thanks If need be I can send you a copy of the database that I am working on

Thanks