I need a bit of assistance from the group on an issue that I just can't 
figure out. Here is the story: I have a listbox that when clicked by the 
user I want an SQL statement to check if there a duplicate entries for a 
specific agent for a specific code -- if so I want a messagebox displayed 
with instructions.

I have tried putting the following code in the lstbox_Click event and the 
lstbox_MouseDown event with the same result. After each click I get the 
messagebox regardless of the number of instances produced in the SQL 
statement (1 or 5 seems to be the same here...)

Here is the code...

    Dim strSQL As String
    Dim intResult As Variant
    
    strSQL = "SELECT Count(tblQueuesData.strErrorCode) AS " _
        & "CountOfstrErrorCode FROM tblQueuesData WHERE " _
        & "(((tblQueuesData.strErrorCode) = " _
        & "[Forms]![frmSearchByAgent]![txtErrorCode]) And " _
        & "((tblQueuesData.strEmpNo) = " _
        & "[Forms]![frmSearchByAgent]![strEmpNo])) HAVING " _
        & "(((Count(tblQueuesData.strErrorCode))>1));"
    intResult = strSQL
    
    If intResult >= 2 Then
        MsgBox "    Duplicate Entries" & vbCrLf & _
            "Please see a Manager", vbOKOnly, "Search Result"
    End If

Any assistance is greatly appreciated.

Kenny

Kenny

You haven't actually run your query - you've just set intResult equal to the query string, so intResult is equal to "SELECT
Count...".

So intResult is always the same and always greater than 2.

Brian

>  from:    Kenny Alligood <kennyalligood@h...>
>  date:    Wed, 23 Oct 2002 01:10:14
>  to:      access@p...
>  subject: Re: [access] Help with code...
> 
> I need a bit of assistance from the group on an issue that I just can't 
> figure out. Here is the story: I have a listbox that when clicked by the 
> user I want an SQL statement to check if there a duplicate entries for a 
> specific agent for a specific code -- if so I want a messagebox displayed 
> with instructions.
> 
> I have tried putting the following code in the lstbox_Click event and the 
> lstbox_MouseDown event with the same result. After each click I get the 
> messagebox regardless of the number of instances produced in the SQL 
> statement (1 or 5 seems to be the same here...)
> 
> Here is the code...
> 
>     Dim strSQL As String
>     Dim intResult As Variant
>     
>     strSQL = "SELECT Count(tblQueuesData.strErrorCode) AS " _
>         & "CountOfstrErrorCode FROM tblQueuesData WHERE " _
>         & "(((tblQueuesData.strErrorCode) = " _
>         & "[Forms]![frmSearchByAgent]![txtErrorCode]) And " _
>         & "((tblQueuesData.strEmpNo) = " _
>         & "[Forms]![frmSearchByAgent]![strEmpNo])) HAVING " _
>         & "(((Count(tblQueuesData.strErrorCode))>1));"
>     intResult = strSQL
>     
>     If intResult >= 2 Then
>         MsgBox "    Duplicate Entries" & vbCrLf & _
>             "Please see a Manager", vbOKOnly, "Search Result"
>     End If
> 
> Any assistance is greatly appreciated.
> 
> Kenny

Kenny,

Yes, Brian is correct.  In order to actually execute the SQL statement, 
you should have this statement:

   DoCmd.RunSQL strSQL


Greg

Kenny,

<< Yes, Brian is correct.  In order to actually execute the SQL statement, 
you should have this statement:

   DoCmd.RunSQL strSQL >>

Oh, and be that as it may, you still need to assign intResult.  May I 
suggest you use the DCount function instead?  Assuming all your fields are 
text not numerical.

   intResult = DCount("[strErrorCode]", "tblQueuesData", _
               "[strErrorCode] = '" & Me.txtErrorCode & _
               "' And [strEmpNo] = '" & Me.txtEmpNo & "'")

   If intResult >= 2 Then
      MsgBox "Duplicate Entries" & vbCrLf & _
      "Please see a Manager", vbOKOnly, "Search Result"
   End If


Greg

Thanx Brian. I realized that after I sent the message. I appreciate your 
reply.

Kenny

Thanx Greg for the reply. I realized after posting the message what my 
error was but had not considered the DCount() for the assigment of the 
variable. I will try it...

Kenny