Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    And what is the sql of q_LkUp_StudyStatus? Does this query work standalone?



    Maybe in the rowsource of the combobox "SELECT DISTINCT ....."

  2. #17
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    Here is the query sql:

    Select dbo_lkup_study_status.study_status, dbo_lkup_study_status.Study_status_grp, dob_lkup_study_status.Study_status_value from dbo_lkup_Study_status;

    It stands alone I think. Not sure what you mean, but it is a query specifically for Study Status combo boxes and can be run alone.

    I tried distinct and it did not solve the problem. It filters fine (correct results), but displays a parameter request for each record returned. Annoying.

    The filter and list work great except for with this field. I'm not sure why it filtered fine prior to writing the filter and list code and now it does not.

  3. #18
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    The filter code that seems to be an issue for the combo box is:

    If me.filteron = true then
    me.listPatName.rowsource = "select [qf_Patient_Details].pat_id_display, [qf_Patient_details].name from qf_Patient_Details where " & me.filter

    If I remove this code the combo box filters fine. If I include this code I get the parameter error.

  4. #19
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    Could it be that the combo box is a numeric field and the others are dates or text fields?

  5. #20
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    You need to edit the me.filter as before to adapt it for the WHERE criteria"

    Code:
    dim sSQL as string
    If me.filteron = true then
        sSQL =  "select [qf_Patient_Details].pat_id_display, [qf_Patient_details].name from qf_Patient_Details where " & me.filter
        sSQL = Replace(sSQL, "[" & Me.Name & "].", "")
        me.listPatName.rowsource = sSQL
    else ...
    End if
    Also, I'm a bit confused as this is not the same combobox or rowsource mentioned in post #15.

  6. #21
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    This will do exactly what you described in the original post.

    So you have a text box (top) for entering values and a list box (bottom) for showing filtered results after pressing button (right) like so:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	21 
Size:	2.2 KB 
ID:	32276

    Now for the text box that you enter parameters into you do NOT need to set any [Event Procedure] or anything. The text box used for users to enter their search parameters should NOT be bound to anything, just an extra text box on the form, which the same applies to the list box described later.

    The filtering button (named "btnFindRecord" in Property Sheet > Other > Name) needs the following code placed in the On Click [Event Procedure], where "Me!txtRecordSearchBox" is the field users enter their search parameter. Just be sure to set Property Sheet > Format > Column Count (i.e. 5) and Column Widths (i.e. 0";0.85";1";0.75";0.75") and Column Heads if you want to see corresponding field names.

    Code:
    Private Sub btnFindRecord_Click()
    On Error Resume Next
        Dim strSQL As String
        strSQL = "SELECT ID, Fieldname, Fieldname, Fieldname, Fieldname " _
                & "FROM YourTableName " _
                & "WHERE Fieldname LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Fieldname LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Fieldname LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Fieldname LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Fieldname LIKE '*" & Me!txtRecordSearchBox & "*' "
        CurrentDb.OpenRecordset strSQL
        Me!listSearch.RowSource = strSQL
        Me!listSearch.Requery
    End Sub
    The list box (named "listSearch" in Property Sheet > Other > Name) needs the following set in After Update [Event Procedure].

    Code:
    Private Sub listSearch_AfterUpdate()
    On Error GoTo listSearch_AfterUpdate_Err
        DoCmd.SearchForRecord , "", acFirst, "[ID] = " & str(Nz(Screen.ActiveControl, 0))
    listSearch_AfterUpdate_Exit:
        Exit Sub
    listSearch_AfterUpdate_Err:
        Beep
        MsgBox Err.Description, vbOKOnly, ""
        Resume listSearch_AfterUpdate_Exit
    End Sub
    Now with the aforementioned example if you press the search button without any parameter then all records will show but when a user enters something that matches fully or partially an item from the table it will only show those results. Just replace anything colored with whatever you want so long as it corresponds to the button name, text box name, and list box name as previously described.

    The following is an example based on limited information given:

    Code:
    Private Sub btnFindRecord_Click()
    On Error Resume Next
        Dim strSQL As String
        strSQL = "SELECT tblPatient.[SecondaryID], tblPatient.[LastName], tblPatient.[FirstName] " _
                & "FROM tblPatient, tblDemographics, tblStudyDetails " _
                & "WHERE tblPatient.[SecondaryID] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblPatient.[LastName] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblPatient.[FirstName] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblDemographics.[DOB] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblDemographics.[Sex] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblDemographics.[Age] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblDemographics.[Race] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblDemographics.[NHIA] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblStudyDetails.[MDLetter] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblStudyDetails.[PatLetter] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblStudyDetails.[PatPhone] LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR tblStudyDetails.[StudyStatus] LIKE '*" & Me!txtRecordSearchBox & "*' "
        CurrentDb.OpenRecordset strSQL
        Me!listSearch.RowSource = strSQL
        Me!listSearch.Requery
    End Sub
    
    
    Private Sub listSearch_AfterUpdate()
    On Error GoTo listSearch_AfterUpdate_Err
        DoCmd.SearchForRecord , "", acFirst, "[SecondaryID] = " & str(Nz(Screen.ActiveControl, 0))
    listSearch_AfterUpdate_Exit:
        Exit Sub
    listSearch_AfterUpdate_Err:
        Beep
        MsgBox Err.Description, vbOKOnly, ""
        Resume listSearch_AfterUpdate_Exit
    End Sub
    Just set Property Sheet > Format > Column Count = 3 and Column Widths = 0";0.5";0.5" and Column Heads = Yes or No. Also the SecondaryID I am assuming is a primary key field, if not then change it to the primary key field in both the SELECT of btnFindRecord_Click() and in the listSearch_AfterUpdate(). Furthermore, I assumed there are three tables, their naming convention, and the fieldnames for each, so change those as necessary.

    The way the search is setup you just need to know a partial for a result to show. So for "Harrell", just typing "Harr" will render any records that has a field with something containing "Harr" in it. Now if "Harrell" is typed it only shows records with "Harrell" in it. The same applies to dates and numbers. The point is this is very flexible for anyone's needs. If they want to filter based on date ranges then that involves a different set (two) of text boxes and a separate button that filters the list box based on those values (date range).
    Last edited by SierraJuliet; 01-20-2018 at 02:14 PM.

  7. #22
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by Slush View Post
    The filter code that seems to be an issue for the combo box is:

    If me.filteron = true then
    me.listPatName.rowsource = "select [qf_Patient_Details].pat_id_display, [qf_Patient_details].name from qf_Patient_Details where " & me.filter

    If I remove this code the combo box filters fine. If I include this code I get the parameter error.

    A parameter error means whatever is in the error message is not named that. Check its name in the Property Sheet NOT the navigation pane on the side because it might have a different name the same as how text boxes might be associated with a fieldname like DateCreated but the Property Sheet name is txtDateCreated for the actual field value itself. Also the brackets go on the fieldnames and not the table names unless you already have brackets on fieldnames then it is okay to place then on table names. Also use capital letters for statement clauses SELECT FROM WHERE because sometimes access is just picky.

    Example, assuming all fieldnames are correct as shown in table, table name is correct, filter variable is correct (not sure what that is from), and the combobox name is "listPatName":

    Code:
    Private Sub listPatName_GotFocus()
        Dim strSQL As String
        something about a filteron
        strSQL = "SELECT [pat_id_display], [name] FROM qf_Patient_Details WHERE '" & Me!filteron & "' "
        CurrentDb.OpenRecordset strSQL
        Me!listPatName.RowSource = strSQL
        Me!listPatName.Requery
    End Sub
    For filtering with If Then Else or ElseIf I would use something like DoCmd.SetFilter , "Fieldname LIKE '" & Criteria & "' Or Fieldname LIKE '" & Criteria & "' "

    Example using above post could be something like (excerpt):

    Code:
    If something = "" Or something <> "" Or something = something Then
        CurrentDb.OpenRecordset strSQL
        DoCmd.SetFilter , "Fieldname LIKE '" & Criteria & "' Or Fieldname LIKE '" & Criteria & "'"
    Else
        something
    End If
    Sidenote: I would set the code for combo box on Got Focus [Event Procedure] this way when you click the combo box it executes the query.

  8. #23
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by Slush View Post
    Here is the query sql:

    Select dbo_lkup_study_status.study_status, dbo_lkup_study_status.Study_status_grp, dob_lkup_study_status.Study_status_value from dbo_lkup_Study_status;

    It stands alone I think. Not sure what you mean, but it is a query specifically for Study Status combo boxes and can be run alone.

    I tried distinct and it did not solve the problem. It filters fine (correct results), but displays a parameter request for each record returned. Annoying.

    The filter and list work great except for with this field. I'm not sure why it filtered fine prior to writing the filter and list code and now it does not.
    Add "On Error Resume Next" and see if that fixes the problem with parameter requests, also see post #22 for possible reason why it is doing that. See post #21 for example placement of "On Error Resume Next".

  9. #24
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    Thank you for your suggestions. I will definitely use them in the future, but unfortunately my client wants to apply filters from within the form. They do not want to use a separate form. With everyone's help I was able to get the list box to update after filtering except when filtering using the combo box value. Currently, the combo box on the form is NOT used to filter the form directly. It is used to assign the patient's study status. It displays the possible study status values from a look up table. When the user filters using this field, they do not change the value (no update made to the field). They just right click and apply a number filter (>, <, = etc) using the access supplied filter.

    The combo box is based off of this query (q_lkup_StudyStatus) which just displays all values in the Study Status look up table:
    SELECT dbo_lkup_study_status.study_status, dbo_lkup_study_status.Study_status_grp, dob_lkup_study_status.Study_status_value FROM dbo_lkup_Study_status;

    The row source for the combo box is:
    SELECT [q_lkup_study_status].[study_status], [q_lkup_study_status].[Study_status_grp], [q_lkup_study_status].[Study_status_value] FROM q_lkup_Study_status ORDER BY [study_status_grp];

    The control source is study_status which is hidden. The user sees and filters on Study_status_grp.

    If I don't use the code to update the list box the combo box filters fine, but if I use the update list box code the combo box filters correctly, but I get the repeated prompt to enter a parameter value. I tried adding error handling (resume next, etc) but that did not work. I tried adding a new combo box to see if that could be filtered, but the same parameter error appeared. I checked all code as I know this indicates I have a misspelling or other error, but I can't find anything.

    The code to update the list box when the filter is in the form Current event:
    If me.filteron = true then
    ssql = "SELECT [qf_Patient_Details].[Pat_id_display].[name] from qf_Patient_details where " & me.filter
    ssql = Replace (ssql, "[" & me.name & "].", "")
    me.listPatName.rowsource = ssql
    Elseif me.filteron = false then
    me.listPatName.rowsource = "SELECT [qf_Patient_Details].[Pat_id_display].[name] from qf_Patient_details"
    End If

    The parameter prompt error is shown below. I don't use the word "Lookup_" (as in "Lookup_ComboStudyStatus") anywhere (I use lkup), so I don't know if that is a clue to the issue.I search my code and can't find the word anywhere if that matters. I'm not sure what else to try.

    Click image for larger version. 

Name:	Lkup.GIF 
Views:	14 
Size:	20.2 KB 
ID:	32361

  10. #25
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Maybe this is misspelled?
    The combo box is based off of this query (q_lkup_StudyStatus) which just displays all values in the Study Status look up table:
    SELECT dbo_lkup_study_status.study_status, dbo_lkup_study_status.Study_status_grp, dob_lkup_study_status.Study_status_value FROM dbo_lkup_Study_status;

  11. #26
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    Thanks. That was a forum typo. I am working on a remote machine and cannot easily copy code, so I manually typed that into the forum. Thanks for catching it. I wish it solved the problem. Because the issue exists even when I add a new combo box to the form, I was thinking maybe it was an Access limitation.

  12. #27
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    Code:
    If me.filteron = true then
       ssql = "SELECT [qf_Patient_Details].[Pat_id_display].[name] from qf_Patient_details where " & me.filter
       ssql = Replace (ssql, "[" & me.name & "].", "")
       me.listPatName.rowsource = ssql
    Elseif me.filteron = false then
       me.listPatName.rowsource = "SELECT [qf_Patient_Details].[Pat_id_display].[name] from qf_Patient_details"
    End If
    You ARE aware that "Name" is an extremely poor name for an object??? "Name" is a reserved word in Access.
    "PatName" would be a better name for the field in the table.


    You should add a debug line to see if the SQL is formed correctly:
    Code:
    If me.filteron = true then
       ssql = "SELECT [qf_Patient_Details].[Pat_id_display].[name] from qf_Patient_details where " & me.filter
       ssql = Replace (ssql, "[" & me.name & "].", "")
       Debug.Print ssql
       me.listPatName.rowsource = ssql
    Elseif me.filteron = false then
       me.listPatName.rowsource = "SELECT [qf_Patient_Details].[Pat_id_display].[name] from qf_Patient_details"
    End If
    Set a breakpoint on the line following the debug statement, copy the SQL from the immediate window and paste into a query (in SQL view). Execute the query....


    I would suggest posting the dB, but it looks like your BE is SQL Server (maybe Express)....

  13. #28
    Slush is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    38
    I agree. Name is a field created in a query for the list box only. It joins first and last name. It should have a better name. This is the only place it is used.

    The database BE is SQL Server and I can't upload it.

    I believe I found the problem, but I don't know how to solve it yet. The combo box (comboStudyStatus) is bound to the first column which is hidden (study_status), but the user is trying to filter using the second column's value (study_status_grp). I copied the combo box on the form and changed the bound column to study_status_grp and I was able to filter on the combo box without getting the parameter error. I think the issue is trying to filter on the unbound value in the combo box. I believe Access is returning the parameter error (lookup_comboStudyStatus.Study_Status_grp) because the user is filtering on the unbound column (study_status_grp).

    The comboStudyStatus is a pretty crucial field and has a lot of code attached to it. The control source is Study_Status which is the primary key in the look up table (showing all values for study status) and a foreign key in other tables. When the user updates this field it impacts a lot of other fields.

    How do I solve this?

  14. #29
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would upload the FE for analysis????

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 09-14-2015, 09:38 PM
  2. adding a filtered list to a form?
    By merlin777 in forum Forms
    Replies: 5
    Last Post: 10-19-2014, 07:26 PM
  3. Replies: 12
    Last Post: 03-27-2014, 06:14 AM
  4. Replies: 3
    Last Post: 08-10-2012, 10:02 PM
  5. update query fon a filtered form HELP!
    By campanellisj in forum Queries
    Replies: 0
    Last Post: 11-12-2010, 09:08 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums