Relationship Analysis

[Alexieoo]

So I have modified and seen some improvement. But if you refer to the screen shot, there seems to be an issue with the line in my code and I am not sure why? According to the original author of the code it is supposed to apply the string as the form's Filter.

But when I type in a procedure name and click the filter button the debugger prompts me to that line.

[June7]

What is the error message?

Learn debugging techniques. Refer to link at bottom of my post.

[ssanfu]

You have a debug statement..... when the code stops at the line "Me.Filter", what does the immediate window for the variable "strWhere" show?

[Alexieoo]

Hey so I took your advice to try debugging more thoroughly by using the "stepping through" tactic but when I press F8 this is the first error message I get (refer to screenshot). I have also provided another screenshot for when I try to filter by procedure name. In turn, it goes to the window I previously posted and highlights "Me.Filter = strWhere". Hope this helps. Thanks so much for the feedback and help.

[ssanfu]

Usually you get that error when you use the "Me" keyword in a standard module; "Me" it can only be used in code behind a form/report.

Is the sub "cmdFilter_Click" in the module "ProcedureFilterCode" or in the module behind "Form1"?

[Alexieoo]

Usually you get that error when you use the "Me" keyword in a standard module; "Me" it can only be used in code behind a form/report.

Is the sub "cmdFilter_Click" in the module "ProcedureFilterCode" or in the module behind "Form1"?

It''s in the module ProcedureFilterCode. But when I click on "view code" in design view the exact same code comes up.

[Alexieoo]

As you can see here the standard module but the code is also in the background of the form (Unless I am mistaken). In reference to my older screenshots, I get two different errors depending on what I do. If I try to simply search something I get the run time error 3075. If I go directly to my code and use the step through process I get the compile error.

[ssanfu]

You cannot use the "Me" keyword in a standard module. "Me" can only be used in a form module (code behind a form).

To use the code in a standard module, you will have to change "Me" to use a fully qualified name: "Forms!form1.txtProcedureName".

Unless you are truing to use the filter code for multiple forms, it would be better to have the code in the form module.

[Alexieoo]

You cannot use the "Me" keyword in a standard module. "Me" can only be used in a form module (code behind a form).

To use the code in a standard module, you will have to change "Me" to use a fully qualified name: "Forms!form1.txtProcedureName".

Unless you are truing to use the filter code for multiple forms, it would be better to have the code in the form module.

Thanks so much for your input. I have made the required changes. But I am still getting the error in the screenshot. There seems to be some sort of syntax error I can't find. Is there a way I can move my code into the form module? thanks.

[ssanfu]

Is there a way I can move my code into the form module?

Open the module "ProcedureFilterCode", cut (ctrl-X) the code, open the module for "Form1", paste.

Open "Form1" in design view.
Open the properties dialog for the filter button, click on the EVENTS tab, click on the ellipsis for the click event.
This is to ensure that the event is connected to the code.

Note: there should only be one sub named "cmdFilter_Click()".

If you left the filter code in the module "ProcedureFilterCode" (didn't delete it), rename it. I use the prefix "xx_". So the sub name would be renamed to "XX_cmdFilter_Click()".



You could post your dB. Do a "Compact and Repair", then zip it.

[Alexieoo]

I have attached my dB. Please ignore the tblFollowups if you look at it. I know the field names go against all coding rules that one is still a work in progress and might not even be needed. I have also created a junction table so that many procedures can be linked to one patient or many patients can be linked to one procedure, which is also a work in progress. Thanks!

[Alexieoo]

I just realized I did not create that junction table properly. Is this the correct format?

[ssanfu]

The reason your filter code errors is that you removed the 5 characters " AND " (in RED below) that must be at the end of the 3 lines.

Private Sub cmdFilter_Click()
'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    Const conJetDate = "\#mm\/dd\/yyyy\#"       'The format expected for dates in a JET query string.
    Dim strWhere As String                      'The criteria string.
    Dim lngLen As Long                          'Length of criteria string.

'Text field. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtProcedureName) Then
        strWhere = strWhere & "[SurgicalProcedure] Like ""*" & Me.txtProcedureName & "*""" & " And "
    End If

    'Date field. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtDateFrom) Then
        strWhere = strWhere & "[ProcedureDate] >= " & Format(Me.txtDateFrom, conJetDate) & " AND "
    End If

    'Date field.
    If Not IsNull(Me.txtDateTo) Then
        strWhere = strWhere & "[ProcedureDate] < " & Format(Me.txtDateTo + 1, conJetDate) & " AND "
    End If

    'See if the string has more than 5 characters (a trailing " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then    'Nothing in the string
        MsgBox "No Criteria", vbInformation, "Nothing to do."
    Else                   'Yes: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging. Prints to Immediate Window (Ctrl+G)
        Debug.Print strWhere

        'Apply the string as the form's filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Also comment out or delete the code in the module "ProcedureFilterCode"

[Alexieoo]

Hey I took your advice I also noticed that you changed the cmd from "Forms!PtFilter" to the pronoun "Me.". I tried to use the filter with both and got the same error. It seems as though I have a datatype mismatch?

[ssanfu]

There are syntax errors in your current code. You have mismatched quotes in the line that errors. You can see this by looking at the 'AND' you added; there are two quotes after the AND.

Make a copy of your dB, then replace your entire sub "cmdFilter_Click()" with the code from my post (Post #58).

On a side note, I see many thing in your dB that I would consider problems.
One example would be the primary keys.
See Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp