Unable to navigate as a form is effectively filtered to a single record

[LimitedAccess]

Hi Chaps

First post so hope I'm not thrown to the floor and kicked for this question

Youknow how it is when you know just enough to get yourself through, but stumble on silly little things for hours

This relates to a database of company contracts past and present and my question relates to displaying a single contract on a form which allows the user to see all details of a selected contract (Contract title, Project Manager involved, value, client, etc etc) The Table of all this data (about 5000 records) has the field ContractNumber as the Primary Key and is an Autogen number field.

Now I have a simple 1st form that has a ComboList for the user to select the desired contract to view. When the user hits a button on this simple form it opens the main viewing form which displays the information beautifully. No problems there. This uses

DoCmd.OpenForm "Frmcontract", , , stLinkCriteria

where the variable stLinkCriteria is the selection from the ComboList on the 1st form.

My problem is that a user has asked if I can include a Next record/Previous record navigation to the main viewing form. Now obviously I can't simply make the navigation of the form visible as they can only navigate the filtered form which now only consists of the one contract. So what I'm wondering is how I can achive this request.

I still want to use the original 1st form with the ComboList setup as this also includes a ComboList for the Contract name (if you don't know the number) and this also works fine and people are used to using this 1st form.

I'm sure there must be a way to do this, but I'm stumped.

I first thought that I could use a couple of command buttons on the main viewing form that saved the current contract number to a variable, closed the main form then opened it again with a +1 or -1 value to the Contractnumber field, but that falls over if the table of contract numbers has any deleted records.

Help please

 

[Uncle Gizmo]

First of all using the same form again is the way to go, it makes a lot more sense and cuts down on development time in the future. If you keep adding different forms to handle the same data in different ways, then it very quickly becomes a headache!

I think the way I would handle this if I was you is to open the form in design view and delete the filter so that the form displays all of the records.

Next I would use the command button wizard to create two buttons, one for next record and one for previous record once you have this working then you can decide how you want this new functionality to interact in your database.

 

[LimitedAccess]

First of all using the same form again is the way to go, it makes a lot more sense and cuts down on development time in the future. If you keep adding different forms to handle the same data in different ways, then it very quickly becomes a headache!

I think the way I would handle this if I was you is to open the form in design view and delete the filter so that the form displays all of the records.

Next I would use the command button wizard to create two buttons, one for next record and one for previous record once you have this working then you can decide how you want this new functionality to interact in your database.

Thanks, but the problem with removing the filter is that the form then displays the first record in the table rather than remain displaying the selected contract from the 1st form, although it does then allow you to navigate through all of the records. But with some 5000 records it would take an age to navigate back to the area of the table that the user originally selected from the 1st form with the ComboList.
If I combine the various contract selection ComboLists and word search utilities on the main contract viewing form I'm worried that the space taken and the extra complexity that it will add may confuse the user and result in complaints. This is why I have favoured this tree of navigated forms.

 

[TylerTand]

Hope this helps

I had the same thing. I have a combo box that lists a group of organizations that displays their members in a sub form. I wanted to be able to go to the Organization name combo box and select a different organization and then have the form requery to get all of their associates. If the "Next" or "previous" company are really the next one or previous one in the drop down list than this will work fine. Copy the following code into the Event tab in the "After Update" option:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TO View Only" (The name of the main form)

stLinkCriteria = "[TO Record ID]=" & Me![Combo40] (how the main and sub forms are linked)
'DoCmd.OpenForm stDocName, , , stLinkCriteria

Me.Filter = stLinkCriteria
Requery (the key to it all working!)


Hopefully your result will look like mine and you can now select a different company from your drop down box and the form will requery with the correct employees will be associated with that company! If this doesn't work let us know
Tyler