form record selector not working

[jmk909er]

This is a little hard to explain but here goes:
I have frmInrto that displays a list of projects by project number, the text box is refered to as "Reference Number". When you click on the project number it opens a full record with details in frmProjects using this code and linking the "Reference Number":



'Enables link in frmIntro to link to frmProjects and filters by Reference Number
Private Sub Reference_Number_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmProjects"

stLinkCriteria = "[Reference Number]=" & "'" & Me![Reference Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

When the frmProject form opens I want to be able to use the record selector at the bottom to also go into next and previous records. The problem is that with it being filtered by reference number it just shows 1 record in the selector. If I put code in "Me.Filter = no" after it loads it just goes to the first record and then shows all the records.

Is there any way to have it load with the Reference Number selected and still be able to use the record selector to go to other records??

Thanks, Joe

[HiTechCoach]

This is a little hard to explain but here goes:
I have frmInrto that displays a list of projects by project number, the text box is refered to as "Reference Number". When you click on the project number it opens a full record with details in frmProjects using this code and linking the "Reference Number":

'Enables link in frmIntro to link to frmProjects and filters by Reference Number
Private Sub Reference_Number_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmProjects"

stLinkCriteria = "[Reference Number]=" & "'" & Me![Reference Number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

When the frmProject form opens I want to be able to use the record selector at the bottom to also go into next and previous records. The problem is that with it being filtered by reference number it just shows 1 record in the selector. If I put code in "Me.Filter = no" after it loads it just goes to the first record and then shows all the records.

Is there any way to have it load with the Reference Number selected and still be able to use the record selector to go to other records??

Thanks, Joe

Joe,

to clear the filter use:

Me.Filter = ""

It is possible to open the form and then do a "find" then record and make it the current record.


I prefer to use a combo box so that you can search by [Reference Number] but return the Primary key filed that is normally a number value (autonumber data type)

I use the function placed in a code module:

Public Function bimOpenFormFindRec(pFormName As String, pControlToSearch As String, pFindThis As String, pIsNumeric As Boolean, Optional pOpenArgs As String) As Byte

    If pOpenArgs > "" Then
        DoCmd.OpenForm pFormName, , , , , , pOpenArgs
    Else
        DoCmd.OpenForm pFormName
     End If

    DoCmd.SelectObject A_FORM, pFormName
    DoCmd.GoToControl pControlToSearch
    
    If pIsNumeric Then
        DoCmd.FindRecord Val(pFindThis), A_ENTIRE, False, acSearchAll, False, A_CURRENT
    Else
        DoCmd.FindRecord pFindThis, A_ENTIRE, False, acSearchAll, False, A_CURRENT
    End If
    
    DoCmd.SelectObject A_FORM, pFormName
    DoCmd.GoToControl pControlToSearch

End Function

[jmk909er]

Thanks HiTechCoach, I think that Me.Filter = " " may be what I am looking for but I need more info. I am thinking I can make my own "Next" and "Previous" buttons on my form so that when I click on it, it removes the filter and goes to the next record, but when I include code for undoing the filter it goes to the first record on the table and not the next record.

I tried the Me.Filter = " " that you gave me hoping that it would stay on the current record after the filter was removed but what happened is it errored out, if I take the space out between the quotes it works but exibits the same behavior as before, (removing the filter and going to the first record)

I am by no means an expert, can you offer me anything else to go on?

Thanks, Joe

[HiTechCoach]

Joe,

I use the function that I posted.

To use place teh code in a new code module and then save it as modFormTools

To use it you would substitute the Docmd.Open form line like this:

Private Sub Reference_Number_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmProjects"
    
    stLinkCriteria = "[Reference Number]=" & "'" & Me![Reference Number] & "'"
    ' DoCmd.OpenForm stDocName, , , stLinkCriteria

bimOpenFormFindRec  stDocName, "[Reference Number]", Me![Reference Number], False

End Sub

You could create a remove filter button that then also sues code similar to the function I gave you to retunr back to the record you were on before you removed the filter.