[X]My Assistant

[LagoDavid]

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

[cheekybuddha]

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

[LagoDavid]

PERFECT! Thank you so much.

[cheekybuddha]

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

[LagoDavid]

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

[LagoDavid]

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?

[cheekybuddha]

Try adding Me.Undo before Cancel = True

[LagoDavid]

Works like a charm now. Thank you again.

[cheekybuddha]