Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Check The Count Function Result

Posted by: LagoDavid Nov 17 2019, 09:54 AM

I have a text box that I need to evaluate to prevent duplication of a name. I cant simply create a unique index because the unique combination is several levels up in the hierarchy. So I am trying to do evaluation in the BeforeUpdate event. I have the following code and I don't know how to finish the code. I want to check the query column that contains the COUNT value and if the count value is greater than zero, display a MsgBox, cancel the update and return control to the text box. Here is what I have so far:

CODE
Private Sub txtSamplePointName_BeforeUpdate(Cancel As Integer)
   Dim strSQL As String
   Dim strSelect As String
   Dim strFrom As String
   Dim strWhere As String
   Dim dbs As Database
  
   strSelect = _
      "SELECT COUNT(*) AS NumberOfDups, tbl101Sites.SiteID, tbl110SamplePoints.SamplePointName "
   strFrom = _
      "FROM ((tbl101Sites INNER JOIN tbl102Areas ON tbl101Sites.SiteID = tbl102Areas.SiteID) " & _
      "INNER JOIN (tbl104Units INNER JOIN tbl106Equipment ON tbl104Units.UnitID = tbl106Equipment.UnitID) " & _
      "ON tbl102Areas.AreaID = tbl104Units.AreaID) " & _
      "INNER JOIN tbl110SamplePoints ON tbl106Equipment.EquipmentID = tbl110SamplePoints.EquipmentID "
   strWhere = _
      "WHERE tbl110SamplePoints.SamplePointName = '" & Me.txtSamplePointName & "'"
   strSQL = strSelect & strFrom & strWhere
  
   Set dbs = CurrentDb
   dbs.Execute (strSQL), dbFailOnError
   'from here I want to check the COUNT and if >0 cancel the update; otherwise proceed
      
End Sub

Posted by: cheekybuddha Nov 17 2019, 10:08 AM

CODE
dbs.Execute (strSQL), dbFailOnError

This code is for use with action queries (e.g. INSERT, UPDATE, DELETE)

Instead, use:
CODE
With dbs.OpenRecordset(strSQL)
  If .Fields("NumberOfDups") > 0 Then
    ' cancel the update
  Else
    ' otherwise proceed
  End If
  .Close
End With


hth,

d

Posted by: LagoDavid Nov 17 2019, 10:49 AM

PERFECT! Thank you so much.

Posted by: cheekybuddha Nov 17 2019, 10:52 AM

You can even reduce the code some more:

CODE
With dbs.OpenRecordset(strSQL)
  Cancel = .Fields("NumberOfDups")
  .Close
End With
Set dbs = Nothing    ' <-- It's good to do your housekeeping too!


hth,

d

Posted by: LagoDavid Nov 17 2019, 11:52 AM

So I am getting an error evidently because I cant use the COUNT function unless I am using an aggregate query. So I deleted the COUNT column and instead just checked the RecordSet RecordCount property for >0. Now I am not sure what code to put in the IF elements. With my current code, I cannot close the form without creating a new record.

CODE
With dbs.OpenRecordset(strSQL)
      If .RecordCount > 0 Then
      ' cancel the update
         MsgBox "This Sample Name is already in use at this Site.  Choose a different sample name.", _
         vbOKOnly Or vbExclamation, "Duplication of Sample Name"
         Cancel = True
      Else
      ' otherwise proceed
         Cancel = False
      End If
      .Close
   End With
Set dbs = Nothing

Posted by: LagoDavid Nov 17 2019, 12:43 PM

I am getting strange behavior when I try to use my CANCEL button to cancel the new record or cancel the update of an existing record. I am doing this in the BeforeUpdate event.

Should I be using a different event? OnDirty? Or is there something different I need to do in the IF statements?

Posted by: cheekybuddha Nov 17 2019, 01:01 PM

Try adding Me.Undo before Cancel = True

Posted by: LagoDavid Nov 17 2019, 04:00 PM

Works like a charm now. Thank you again.

Posted by: cheekybuddha Nov 19 2019, 02:53 AM

yw.gif