Better Search Form

Ernest Grogg
Ernest Grogg used Ask the Experts™
on
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.
TestDB.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
from my iPad,

can you post just the code from your double click event?
Ernest GroggSecurity Management InfoSec

Author

Commented:
Sure...


Forms!frmReportFilter5!frm2.Form.Requery
Ernest GroggSecurity Management InfoSec

Author

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 '*'"
    Else
        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 '*'"
    Else
        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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
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 InfoSec

Author

Commented:
Thanks Crystal!
Distinguished Expert 2017
Commented:
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.
Ernest GroggSecurity Management InfoSec

Author

Commented:
I know...it 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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial