Better Search Form

Been trying to improve upon my search form and well, I have a sample here but can't get it working correctly

I have the main form with 2 subforms

The main form has the search criteria, the first subform is in Datasheet view.  The apply filter applies it to the datasheet subform.  I have it set that if you select the record and double click it should open that record in the 2nd subform.

That is where the issue is.  I can't get it to do it.  When you doubleclick the "very first record" it opens it just fine.  Any other record, it doesn't.

Not sure where I am wrong.  I am posting that dB here.
Ernest GroggSecurity Management InfoSecAsked:
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.

Dale FyeOwner, Dev-Soln LLCCommented:
from my iPad,

can you post just the code from your double click event?
Ernest GroggSecurity Management InfoSecAuthor Commented:

Ernest GroggSecurity Management InfoSecAuthor Commented:
This is the code from the Apply Filter

Private Sub cmdApplyFilter_Click()
    Dim strFirstName As String
    Dim strLastName As String
    Dim strFilter As String

' Build First Name criteria string
    If IsNull(Me.txtFirstName.Value) Then
        strFirstName = "Like '*'"
        Select Case Me.fraFirstName.Value
            Case 1
                strFirstName = "Like '" & Me.txtFirstName.Value & "*'"
            Case 2
                strFirstName = "Like '*" & Me.txtFirstName.Value & "*'"
            Case 3
                strFirstName = "Like '*" & Me.txtFirstName.Value & "'"
            Case 4
                strFirstName = "= '" & Me.txtFirstName.Value & "'"
        End Select
    End If
' Build Last Name criteria string
    If IsNull(Me.txtLastName.Value) Then
        strLastName = "Like '*'"
        Select Case Me.fraLastName.Value
            Case 1
                strLastName = "Like '" & Me.txtLastName.Value & "*'"
            Case 2
                strLastName = "Like '*" & Me.txtLastName.Value & "*'"
            Case 3
                strLastName = "Like '*" & Me.txtLastName.Value & "'"
            Case 4
                strLastName = "= '" & Me.txtLastName.Value & "'"
        End Select
    End If
' Build filter string
    strFilter = "[FirstName] " & strFirstName & _
                " AND [LastName] " & strLastName
' Apply filter to form
        Me.frm.Form.Filter = strFilter
        Me.frm.Form.FilterOn = True
End Sub

Open in new window

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Ernest, I see Dale is helping so you are in good hands ~ Requery sets the record back to 1 -- use Bookmark to save position and set it back after requery

Dale, Ernest was using a split form and, in another thread, I suggested a regular form with unbound combos in the header and gave him filter code to customize
Ernest GroggSecurity Management InfoSecAuthor Commented:
Thanks Crystal!
Lots of problems.  We'll start with these two.
1. Many form events will not work if any control on the form can obtain the focus.  So, move the dbl-clk code to one of the detail fields such as LastName.
2. tblStaff does not have a primary key although it does have an autonumber.  Make the autonumber the PK.  Then reference the ID in frm2's RecordSource query.  Personally - I NEVER use ID as the name of the PK.  My PK's always use a meaningful name.  In this case, I would suggest StaffID.  If you get a model with a hundred tables and the PK of every table is ID, you have no way of easily identifying PK/FK pairs.  You have to rely completely on looking at the data model and since way too many people who use ID as the field name don't bother with RI, there is no model to look at.  BTW, NONE of your tables have a PK defined.  That is poor practice.  If there is a natural key, you can use it.  Otherwise,  add an autonumber as a surrogate.

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
Ernest GroggSecurity Management InfoSecAuthor Commented:
I was just a sample...I didn't set it up but was working with primarily the coding Crystal was working with me on.

I have my DB that is right....but I think I got that to work properly by moving it to the ID in the form's record source.

just testing to make sure at this point.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.