Full Version: Searching a Table Using a Form then Displaying data in another F
UtterAccess Forums > Microsoft® Access > Access Forms
jklinephd
Simple database:
able: "Clients" (Some varied info on clients)
Form: "Main_Form" Data Entry Form and Display form for the above table
Form: "Search_form" A form that will search the above table.
I want to create a "Search_form" that will search the table of "Clients" using last name and first name (A QBF). Then display a list of matches on the "Search_form", then allow the user to select one of the matches and go to the corresponding record and display that record in the form called "Main_form."
Suggestions? In the "Search_form" I need to display last Name, First Name, and a couple of other fields to allow for identification.
Thanks
Jeff
Northernlion
Here's how I have approached this in the past:
orget the search form, just do it all on main.
*Make a new combo box and select the third option down (display records on my form based on the value of the combo box*
*Include your PK, Last Name, and First Name in the combo box (in that order)
Now when you start typing in the combo box/click the down arrow you can search by last name and selecting a donor will bring up their record on the main form for editing/updating/whatever.
accesshawaii
I would first suggest creating an identifier for the names such as an Auto-ID and basing your relationship on that field, you can then create a form and place a combo box control in the header section using your id field and the name fields. You would then create another form off of your main form data and insert it into that form. Once you do that, try the below code.
CODE
Dim strFilter As String
If Not IsNull(Me.cboNames.Value) Then
    strFilter = "[NameID] = " & Me.cboNames.Value
    Me.fsub.Form.FilterOn = True
    Me.fsub.Form.Filter = strFilter
Else
    Me.fsub.Form.Filter = ""
End If

Oreally don't see a need to have the user click on the corresponding name to go to that record since all the information is right there in front of them unless that would be taking them to another datasource with related information.
jklinephd
Thanks Norhernlion, That worked, sort of. It gives me a list of the clients. What I would like is to have the popup combo show me all of the records for each client in the table. Unfortunately the table has multiple records for each client, each record representing a different contact with that client. I would love to be able to type in Johnson and have it show the 5 records associated with John Johnson that are in the Table.
I am aware that this is bad design, but I am stuck with it. The person who created it never did it relationally and treated it as a glorified spreadsheet. Converting to a better desing that separates clients into one table and maybe antoher table representing contacts is not going to happen soon.
kapeller
Hi!!!
Take a look at this example and see if this is what you may be looking for
Link
jklinephd
Thank you that is in the ballpark. I am modifying it now, adn I think it will work. I amy email you if I have trouble with the coding. My search is much simpler, so i think I can get it to work.
eff
jklinephd
Ok I stole your sample and hacked on it.
created a Query called qrySearchCriteriasub
Ocreated a Search form called searchForm and a sub form called searchform_sub
I used your code, but modified it to fit.
THere is what i chewed on:
CODE
  Private Sub searchCMD_Click()
On Error Resume Next
    Dim sSql As String
    Dim sCriteria As String
        sCriteria = "WHERE 1=1 "
        'This code is for a Like search where can enter part of a string
        'The source for this code can either be from a table or query
        If Me![Last_Name] <> "" Then
            sCriteria = sCriteria & " AND qrySearchCriteriaSub.last_name like """ & Last_Name & "*"""
        End If
        If Me![First_Name] <> "" Then
                sCriteria = sCriteria & " AND qrySearchCriteriaSub.first_name Like """ & First_Name & "*"""
        End If
        
        If Nz(DCount("*", "qrySearchCriteriaSub", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
        sSql = "SELECT DISTINCT [last_name],[first_name],[exam_date],[examiner],[type_eval] from qrySearchCriteriaSub " & sCriteria
        Forms![searchform]![searchform_sub].Form.RecordSource = sSql
        Forms![searchform]![searchform_sub].Form.Requery
        Else
        MsgBox "The search failed find any records" & vbCr & vbCr & _
        "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
        End If
End Sub

I took out this code:
CODE
   'This code is for a specific search where you will need to enter the exact string
        'The source for this code can either be from a table or query
        If Me![Index] <> "" Then
                sCriteria = sCriteria & " AND qrySearchCriteriaSub.Index = """ & Index & """"
        End If

i thought that it did not apply to my search. Basically I am searching for last Name and first Name (or one or the other) and displayong a subform with about 5 or 6 fields.
I get the sub form to display the query after I hit the search button, but the query contains everyone listed, not the search terms.
I did not add any of the code fromt he sub form
CODE
Option Compare Database
Option Explicit
Sub subOpenAddingDataForm()
    DoCmd.OpenForm "frmAddingData", , , "NewspaperID =" & NewspaperID
    Form_frmAddingData.cmdAddNewData.Visible = False
    Form_frmAddingData.Caption = "View Data"
    Form_frmAddingData.AllowEdits = False
    Form_frmAddingData.AllowAdditions = False
    Form_frmAddingData.AllowFilters = False
    Form_frmAddingData.AllowDeletions = False
End Sub
Private Sub AreaCode_DblClick(Cancel As Integer)
    Call subOpenAddingDataForm
End Sub
Private Sub DateofPaper_DblClick(Cancel As Integer)
    Call subOpenAddingDataForm
End Sub
Private Sub Detail_Click()
End Sub
Private Sub Index_DblClick(Cancel As Integer)
    Call subOpenAddingDataForm
End Sub
Private Sub NewsPaper_DblClick(Cancel As Integer)
    Call subOpenAddingDataForm
End Sub
Private Sub Subject_DblClick(Cancel As Integer)
    Call subOpenAddingDataForm
End Sub
Private Sub Title_DblClick(Cancel As Integer)
    Call subOpenAddingDataForm
End Sub

That seemed applicable to your adding data function and I am not including that.
One odd thing, int he code sample of mine at the top, I type in "If Me![last_name]" and when I save, it changes the code to "If Me![Last_Name] ", changing the case of the L and N. i don't know if that means anything.
So I am missing something. I would appreciate any guidance. I am a newbie at this stuff. Any help would be appreciated.
Jeff
kapeller
Hi Jeff
irstly never consider <
Has a problem because if I did not want anyone to change/modify the code I would not have posted it. I am glad you found some use for it.
Secondly

The reason for changing the case from l to L and n to N is that when you named the form control to Last_Name the code will always reflect that, otherwise you would have gotten a error.
All the best for your project
jklinephd
>
Has a problem because if I did not want anyone to change/modify the code I would not have posted it. I am glad you found some use for it.
econdly
<
The reason for changing the case from l to L and n to N is that when you named the form control to Last_Name the code will always reflect that, otherwise you would have gotten a error.
All the best for your project

Any suggestions on what si wrong with my code above: the "searchCMD_Click()" code and why it is not doing the query correctly?
I do appreciate your code.
Thanks
Jeff
jklinephd
All my form controls on each of the forms in this database are lower case for "last_name." I used an Add-In to change everything a while back. None of the corresponding table fields are capitalized either.
o why does Access want to capitalize things, if nothing is capitalized anywhere else?
Thanks
Jeff
kapeller
Hi Jeff
I am happy to take a look at your DB.
Is it possible to attached a copy of your database? It would be easier to see what is going on.
jklinephd
I will try. I have to copy it, sanitize it and fill it with some dummy info.
Thanks
jklinephd
It Is not pretty. I have deleted a lot of unrelated reports and quieries and sanitized the data.
Thanks again.
Jeff
kapeller
Hi Jeff
I have just down loaded your db and I am about to head off to work. I will take a look at the db when I get home later this afternoon.
kapeller
Hi Jeff
applogise for the delay in responding. I had extra work commitments.
I have attached your db and it is functioning.
I have made a number of minor changes in the query the search form and the sub form. Take a look at these. They are easy to follow.
If you have any more questions please just ask.
All the best for your project
jklinephd
Please don't apologize, I appreciate the help.
got it to work. Thank you so much!!
Jeff
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.