And what is the sql of q_LkUp_StudyStatus? Does this query work standalone?
Maybe in the rowsource of the combobox "SELECT DISTINCT ....."
And what is the sql of q_LkUp_StudyStatus? Does this query work standalone?
Maybe in the rowsource of the combobox "SELECT DISTINCT ....."
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.
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.
Could it be that the combo box is a numeric field and the others are dates or text fields?
You need to edit the me.filter as before to adapt it for the WHERE criteria"
Also, I'm a bit confused as this is not the same combobox or rowsource mentioned in post #15.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
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:
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.
The list box (named "listSearch" in Property Sheet > Other > Name) needs the following set in After Update [Event Procedure].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
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.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
The following is an example based on limited information given:
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.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
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.
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":
For filtering with If Then Else or ElseIf I would use something like DoCmd.SetFilter , "Fieldname LIKE '" & Criteria & "' Or Fieldname LIKE '" & Criteria & "' "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
Example using above post could be something like (excerpt):
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.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
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".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.
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.
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;
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.
PMFJI,
You ARE aware that "Name" is an extremely poor name for an object??? "Name" is a reserved word in Access.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
"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:
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....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
I would suggest posting the dB, but it looks like your BE is SQL Server (maybe Express)....
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?
Maybe you would upload the FE for analysis????