when browsing to form in navigation subform how to allow search and first next

[saudkazia]

i'll cut straight to the chase. i am able to open a form to the desired record in a navigation subform with the following

DoCmd.BrowseTo acBrowseToForm, "workcard", "main.navigationsubform", "workid=" & Me!workid

the problem is once the workcard form is opened i am not able to use the lookup nor go previous or next record. this limits functionality tremendously. if i don't go through the above procedure the lookup works fine. i would ideally like to be able to show all records when these controls are triggered.

i tried this on the beforeupdate event

If Me.FilterOn = False Then
'do nothing
Else


Me.Filter = ""
Me.FilterOn = False
End If

and afterupdate this is the macro
search for record | record first | ="[workid] = " & Str(Nz([Screen].[ActiveControl],0)) (built in from wizard)

but for some reason access doesn't think its not filtered (even filter toggle in record navigation is grayed out) and hence it doesn't do anything - ie. still on same record. How can I tell access to show all records and go to the record that i want. Or what is the best approach for this.

[ssanfu]

You have to think in terms of record sets.

Let's say you have a table of workers (tblWorkers). There are 100 workers, each with a unique workID.
There are 3 workers with a first name of "Sam" and 9 workers named "John".

If you created a query "SELECT workID, FirstName FROM tblWorkers;" and executed the query, how many records would be in the record set? (ans 100)
If you change the query to "SELECT workID, FirstName FROM tblWorkers WHERE FirstName = 'Sam';" how many records would be in the record set? (ans 3)
Now change "Sam" to "John". How many records would be in the record set? (ans 9)

Sam Spade 's workID is 10.
Let's change the query to "SELECT workID, FirstName FROM tblWorkers WHERE workID= 10;".
Now how many records will be in the record set when the query is executed? (ans 1).
Could you go to a differnet record or remove a filter??

There is NO filter in effect. Just a record set that has 1 record.
Since there is only 1 record in the record set, you cannot go to a different record number!

How can I tell access to show all records and go to the record that i want.

You would need to change the form's record source; change the record source to return all records, then:
a) filter the record set to the workID you want
b) search for the workID and move to the record (if the workID exists).

Open the form without the where clause and filter/search for the workID. OK when the record set is small but ... can slow down when you have lots of workers in the table (say 8,000 workers......)



I never use the navigational form.... I create my own forms, so I am not sure how to overcome your problem.

Without the navigational form, I would possibly use "DoCmd Openform" with an OpenArgs parameter. When the form opens, code would run to filter the form to the specific workID. The filter could then be removed allowing access to all of the records... (I have done this)....