VBA code for filters 4

[RevJohn0584]

I keep getting a run time error of "run time error '13': Type mismatch" on a microsft office (internet help) provided solution to multiple filters for a report. (

http://support.microsoft.com/kb/208529).

I have followed the steps, changed the criteria to match my required searches, and the VBA debugger takes me to this particular line of code:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then

---->strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "" _
And ""<-------
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Reports![rptNonStdPkData].Filter = strSQL
Reports![rptNonStdPkData].FilterOn = True
End If
End Sub

Highlighted area in debugger is emphasized here with ---> <--- showing the area of concern. I do not know what else to say for your assistance, except this is the problem I am facing. Everything else I have done on this works well.

Please help!

John

 

[PHV]

lameid, I'd use this:
And Me(Nam)[!].Tag[/!] <> "ShipDest

 

[lameid]

PHV is right... I rushed it and grabbed the wrong version of the field.

 

[RevJohn0584]

Ok, we seem to have made progress.....when I filter using date and ship dest, works great. When I use PartNumb, FAIL! When I attempt to filter by PartNumb, it still does not show all of the records I know are there. This field also has the ability to have both numeric, alpha or a combination of the two in it....

Man, you all are doing a great work here...just a little further to go. I am learning so much!

 

[lameid]

Do you know the diffrence between AND / OR?

And says that it has to match both conditions to return a record, OR says that it has to match either conditon... you can use parenthesis to force different groups to be performed differently. Mutch like Math, there is an order of operations at work. I never can remember which takes precedence so I always use parenthesis when mixing the operators.

If that is not the issue, then I have to go back to what is the SQL behind the report and what is it not returning that you think it should?

 

[RevJohn0584]

yes, thank you, I understand and and or functions. As stated earlier, I grabbed this directly from Microsoft...as i am a very new VBA user. However, it is always good to re-visit the basics in this manner, it allows that the foundation is correct and we are the same page.

What I am shy on understanding is the parenthesis mode you speak of....I get the jist of it, no problem - but the application of same is where I fall short. I will dive into this and set is as I think it should be, and then will post the code and the resultant SQL as before.

So many thanks!

 

[lameid]

The code is using AND so parenthesis are irrelevant. As long as you want AND everything is fine.

My questions remain the same...

If that is not the issue, then I have to go back to what is the SQL behind the report and what is it not returning that you think it should?

I want the SQL of the query that is the recordsource of the report. The filter is also needed.

 

[RevJohn0584]

Current code:

Option Compare Database

Private Sub cmdApplyFilter_Click()
Dim Cri As String, x As Integer, Nam As String
Dim strDelimeter As String

For x = 1 To 4
Nam = "Filter" & x

If Trim(Me(Nam) & "") <> "" Then
If IsNumeric(Me(Nam)) And CStr(Val(Me(Nam))) = Me(Nam) And Me(Nam).Tag <> "ShipDest" Then

strDelimeter = ""
Else
If IsDate(Me(Nam)) Then
strDelimeter = "#"
Else
strDelimeter = "'"

End If
End If
If Cri <> "" Then
Cri = Cri & " AND " & Me(Nam).Tag & "=" & strDelimeter & Me(Nam) & strDelimeter
Else
Cri = Me(Nam).Tag & "=" & strDelimeter & Me(Nam) & strDelimeter
End If
End If
Next
Debug.Print Cri
If Cri <> "" Then
Reports![rptNonStdPkData4].Filter = Cri
Reports![rptNonStdPkData4].FilterOn = True
End If
End Sub
Private Sub cmdCloseForm_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub
Private Sub cmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Apply a filter to the form first."
Else
DoCmd.OpenReport "rptNonStdPkData4", acViewPreview, , Me.Filter
End If

End Sub
Private Sub cmdClearFilter_Click()
Me.Filter = ""
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Form.Name
DoCmd.Close acReport, "rptNonStdPkData4" 'Close the Non Standard Pack Data report
DoCmd.Restore 'Restore the window size
End Sub
Private Sub Form_Close()
DoCmd.Close acReport, "rptNonStdPkData4"
End Sub
Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptNonStdPkData4", A_PREVIEW 'Open Non Standard Pack Data Report
DoCmd.Maximize 'Maximize the report window 'Maximize the report
End Sub
Private Sub cmdPrintRpt_Click()
On Error GoTo Err_cmdPrintRpt_Click

Dim stDocName As String

stDocName = "mcoOpnRpt1"
DoCmd.RunMacro stDocName

Exit_cmdPrintRpt_Click:
Exit Sub

Err_cmdPrintRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrintRpt_Click

End Sub
Private Sub cmdPrntRpt_Click()
On Error GoTo Err_cmdPrntRpt_Click

Dim stDocName As String

stDocName = "rptNonStdPkData4"
DoCmd.OpenReport stDocName, acNormal

Exit_cmdPrntRpt_Click:
Exit Sub

Err_cmdPrntRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrntRpt_Click

End Sub


SQL from the immediate window:

PartNumb='100408K'

This returns everything in the database and does not filter. I also get a separate dialogue box asking for the Part Number again.

Is this what you are requesting?

 

[lameid]

When I attempt to filter by PartNumb, it still does not show all of the records I know are there. This field also has the ability to have both numeric, alpha or a combination of the two in it....

Based on this I wanted the base SQL statement in additon to the filter...

The SQL statement:
Design the report
Get the form properties (right click the little black box where the rulers meet in the uper left of the report)
On the all tab, the very first item is the recordsource.
I am assuming this is a query. Design that query.
Switch to SQL view by using the drop down attached to the button that normally toggles between datasheet and Design view.
Copy that SQL statement and post it.

PartNumb='100408K'

This returns everything in the database and does not filter. I also get a separate dialogue box asking for the Part Number again.

Based on this (the opposite problem regarding filters), are you sure you are running the report and THEN clicking the the filter button?

 

[RevJohn0584]

The SQL statement:
Design the report
Get the form properties (right click the little black box where the rulers meet in the uper left of the report)
On the all tab, the very first item is the recordsource.
I am assuming this is a query. Design that query.
Switch to SQL view by using the drop down attached to the button that normally toggles between datasheet and Design view.
Copy that SQL statement and post it."

When I go to the recordsource, it is not a query, but a table. tblNonStdPkData is the name. When I go to the drop down, I see nothing of an "SQL" possibility. I have Design view, normal, pivot chart, crosstab chart and that is it. I must be missing soemthing, so if you would be so kind, please advise further. Thank you!

As for the report being open and then the filter applied, I have the code set to open the report when the form opens, and it keeps it in a preview status behind the form where the filters are located. As I update the filters, the report dynamically changes. That is the whole intention of this particular database - to track, search and print shipments that are out of our normal shipping process.

Thank again for your continued support and diligence in sticking with me while I fumble through this!

 

[joelflorendo]

Can you verify that PartNumb is actually the field name? It sounds like it's incorrect and that's why you're getting that second parameter value dialog box.

 

[RevJohn0584]

joeflorendo

right on the money. that stopped the dialogue form popping up - however, it is still not filtering to expectation.

i also have tested every cmd button on the form, and the clear filter button does not work as well. What can I do to help you help me (besides quit using Access =) )

Thanks continually!

 

[joelflorendo]

For the clear filter button, get rid of this:

Me.Filter = ""

The Me object refers to the object module you are currently in. So in this case where you are putting your code, the Me refers to the form. That's why it's not working.
Your clear filter button should be this:

Private Sub cmdClearFilter_Click()
     Reports![rptNonStdPkData4].Filter = ""
     Reports![rptNonStdPkData4].FilterOn = False
End Sub

I think you don't really even need the first line setting the filter to "", just set the FilterOn property to false.

As to the filtering on PartNumb still not working, what's it doing now? Did anything change besides the parameter value popup not showing?

 

[RevJohn0584]

the clear filter works on the report (I can watch it change in the background), but the form still has values left in the cbo boxes. That is something I can deal with - I would prefer these also were de-populated when this is clicked, but no great tragedy if they are not.

I believe that the PN box is working now as well, I am going to print off the table and proof it out - but initially it looks good.

I will verify and update - thanks again.

I am out for the day, so it will be tomorrow before I can update again.

ave a great one joeflorendo!

 

[lameid]

The following should clear your controls. Just add this code to the button click event that clears the filter.

For x = 1 To 4
      Nam = "Filter" & x
      Me(Nam).value = ""
Next X

 

[RevJohn0584]

joeflorendo -

this is probably a huge newbie mistake - but I am seeing a major issue with this whole thing - it is linked directly to a table. When I type in lookup values and apply filters, it is working - however, when i go to type in the next value set, it changes the table! NOT GOOD!

I am thinking I will most likely need to create a query and do this process with that. Given this development, I am sure there will be lots of questions....

Wow...thanks again for the help thus far and thanks in advance for the deluge that is about come....

 

[joelflorendo]

If all you want to clear out are the textboxes on the form, then you can use this within the cmdClearFilter_Click() sub after you turn off the report filter:

Dim X As Control
For Each X In Me.Controls
   If TypeOf X Is TextBox Then
      X = Null
   End If
Next X

If you have other kinds of controls on the form, you'll need to check for those controls (in the same manner as the textboxes here) and clear them out as well.

 

[lameid]

this is probably a huge newbie mistake - but I am seeing a major issue with this whole thing - it is linked directly to a table. When I type in lookup values and apply filters, it is working - however, when i go to type in the next value set, it changes the table! NOT GOOD!

Most Queries in Access allow you to update the data just like a table so a query will not solve this problem.
Instead find (edit, find / ctrl+F) THEN filter by selection.

 

[joelflorendo]

I might be mistaken but I don't see any reason why your form should be tied to your table at all. Since the data is really displayed in the report, that's where the tie back to the data should be. The form, to me, is just a utility to work with the report which will not allow you to change data.

Unless there is another reason why this form should be tied to your talbe, go into design view of your form and clear out the record source property on the data tab.

 

[lameid]

I think I misunderstood the question and Joel hit the nail on the head...

Clear out the recordsource property on your form so that it is unbound (no data behind it).

 

[RevJohn0584]

Ok gentlemen, one last issue. It seems you have been able to rectify all of my issues....now to the final problem I have created with this:

In the PN field, when I enter a pure number (much like I had before in the ShipDest issue), I get a data type mismatch error. i.e. 301154 causes this error, whereas 301154A will not.

I do believe once this is rectified, this will complete the issues I have been facing.

I am certainly blessed to have you both helping me along! Thanks a squillion....