Count Results From Filter

[aas4mis]

I have a form with an unbound field as my lookup.

[CODE=vb]Private Sub txtToShip_After Update()
Dim strFilter As String

strFilter = "[toship] like " & Chr$(39) & "*" & Forms!frmDataEn try!txtToShip & "*" & Chr$(39)
DoCmd.ApplyFilt er , strFilter

Forms!frmDataEn try!txtToShip = ""
End Sub[/CODE]

This code works but I need to be able to alert the user that there were multiple results returned. I have a textbox for this purpose, I'm not sure how to turn the visibility on when multiple results were returned or off when there is only one result. Any ideas?

[jaxjagfan]

I have a form with an unbound field as my lookup.

[CODE=vb]Private Sub txtToShip_After Update()
Dim strFilter As String

strFilter = "[toship] like " & Chr$(39) & "*" & Forms!frmDataEn try!txtToShip & "*" & Chr$(39)
DoCmd.ApplyFilt er , strFilter

Forms!frmDataEn try!txtToShip = ""
End Sub[/CODE]

This code works but I need to be able to alert the user that there were multiple results returned. I have a textbox for this purpose, I'm not sure how to turn the visibility on when multiple results were returned or off when there is only one result. Any ideas?

You can use Dcount function to return a count to a variable. If the variable count is greater than 1 then Me.txtCount.Vis ible = true. You can also set the textbox value to the count using DCount.

Set the properties of the TextBox Visible to No by default. You can then use code to make it visible after you apply your filter.

[aas4mis]

You can use Dcount function to return a count to a variable. If the variable count is greater than 1 then Me.txtCount.Vis ible = true. You can also set the textbox value to the count using DCount.

Set the properties of the TextBox Visible to No by default. You can then use code to make it visible after you apply your filter.

Using the dcount function to return a value is my holdup. I'm assuming that the code would reside in the AfterUpdate portion of my lookup, after the filter was applied. Please explain how I return the value.

[jaxjagfan]

Using the dcount function to return a value is my holdup. I'm assuming that the code would reside in the AfterUpdate portion of my lookup, after the filter was applied. Please explain how I return the value.

What is the table/query name and field name of the data source you are filtering? These are required elements of Dcount - the where portion is optional.
What exactly are you counting?

The more details you give us - the more precise our answers.

I'm going home but may log in from there in a bit.

[jyoung2]

if you use the Dcount as the control source then in your after update event then at line 8 you can place

Me.txtCount.req uery 'Requery a field on the form


if me.txtcount > 1 then
Me.txtCount.Vis ible = true
else
Me.txtCount.Vis ible = false
end if

[aas4mis]

Thanks all for your help in pointing me the right direction. The following code worked for me.
[code=vb]Private Sub txtToShip_After Update()
Dim strFilter As String

strFilter = "[toship] like " & Chr$(39) & "*" _
& Forms!frmDataEn try!txtToShip & "*" & Chr$(39)

DoCmd.ApplyFilt er , strFilter

Dim recTotal As Integer

recTotal = DCount("Toship" , "tblRegular 08", strFilter)

If recTotal > 1 Then
Me.txtWarning.V isible = True
Else
Me.txtWarning.V isible = False
End If

Forms!frmDataEn try!txtToShip = ""
End Sub[/code]

I have another question. There are two unbound fields that I can search from in my form ([ToShip] or [Order Detail]). How would I go about putting lines 9-17 into a function named warnMulti() so that I would be able to call warnMulti("tosh ip") or warnMulti("orde r detail").

Just curious for next time. Should I have posted my second question as a seperate thread?

[jyoung2]

Private Sub txtToShip_After Update()
Dim strFilter As String

strFilter = "[toship] like " & Chr$(39) & "*" _
& Forms!frmDataEn try!txtToShip & "*" & Chr$(39)

DoCmd.ApplyFilt er , strFilter

warnMulti("tosh ip")

Forms!frmDataEn try!txtToShip = ""
End Sub

public sub warnMulti(fldcount as string)

Dim recTotal As Integer

recTotal = DCount(fldcount, "tblRegular 08", strFilter)

If recTotal > 1 Then
Me.txtWarning.V isible = True
Else
Me.txtWarning.V isible = False
End If

end sub

[aas4mis]

Never fails, with each post I get more and more excited about VBA. Thanks again for pointing me in the right direction.

[aas4mis]

OK. This code keeps getting more and more interesting. I now have the following code inside a module so I can call it from multiple events.

[code=vb]Function warnMulti(fldCo unt As String)

Dim recTotal As Integer

recTotal = DCount(fldCount , "tblRegular 08", strFilter)

If recTotal > 1 Then
Forms!frmDataEn try!txtWarning. Visible = True
Forms!frmDataEn try!txtWarning1 .Visible = True
Else
Forms!frmDataEn try!txtWarning. Visible = False
Forms!frmDataEn try!txtWarning1 .Visible = False
End If

End Function[/code]

My problem now is the strFilter part. I set a breakpoint on it and it shows that strFilter = Empty. For some reason strFilter is not getting passed from my event to the function. I've tried changing my event to public but that didn't help. Any pointers?

[jaxjagfan]

OK. This code keeps getting more and more interesting. I now have the following code inside a module so I can call it from multiple events.

[code=vb]Function warnMulti(fldCo unt As String)

Dim recTotal As Integer

recTotal = DCount(fldCount , "tblRegular 08", strFilter)

If recTotal > 1 Then
Forms!frmDataEn try!txtWarning. Visible = True
Forms!frmDataEn try!txtWarning1 .Visible = True
Else
Forms!frmDataEn try!txtWarning. Visible = False
Forms!frmDataEn try!txtWarning1 .Visible = False
End If

End Function[/code]

My problem now is the strFilter part. I set a breakpoint on it and it shows that strFilter = Empty. For some reason strFilter is not getting passed from my event to the function. I've tried changing my event to public but that didn't help. Any pointers?

Declare strFilter as a Global String in the top of the module and get rid of the Dim strFilter in the form. That way when it gets set in the form - it will get passed to the function.

Public strFilter as String

[aas4mis]

Declare strFilter as a Global String in the top of the module and get rid of the Dim strFilter in the form. That way when it gets set in the form - it will get passed to the function.

Public strFilter as String

... I was trying to pass multiple arguments since I would be using this module in multiple click events and kept getting compile errors. Just to help others I'll post my code that worked. The problem was me not using the "call" statement before my function. This must be used for functions with multiple arguments. (wish I knew that before).

[code=vb]module:
Option Compare Database
Global strFilter As String

Public Function warnMulti(fldCo unt As String, tblSrc As String)

Dim recTotal As Integer

recTotal = DCount(fldCount , tblSrc, strFilter)

If recTotal > 1 Then
Forms!frmDataEn try!txtWarning. Visible = True
Forms!frmDataEn try!txtWarning1 .Visible = True
Else
Forms!frmDataEn try!txtWarning. Visible = False
Forms!frmDataEn try!txtWarning1 .Visible = False
End If

End Function

Form:
Private Sub txtOrderNumber_ AfterUpdate()

strFilter = "[order detail] like " & Chr$(39) & "*" _
& Forms!frmDataEn try!txtOrderNum ber & "*" & Chr$(39)
DoCmd.ApplyFilt er , strFilter

Call warnMulti("[order detail]", "tblRegular 08")

Forms!frmDataEn try!txtOrderNum ber = ""

strFilter = ""

End Sub

Private Sub txtToShip_After Update()

strFilter = "[toship] like " & Chr$(39) & "*" _
& Forms!frmDataEn try!txtToShip & "*" & Chr$(39)
DoCmd.ApplyFilt er , strFilter

Call warnMulti("tosh ip", "tblRegular 08")

Forms!frmDataEn try!txtToShip = ""

strFilter = ""

End Sub[/code]

Thanks for the help!