UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Check The Count Function Result, Access 2016    
 
   
LagoDavid
post Nov 17 2019, 09:54 AM
Post#1



Posts: 556
Joined: 12-October 03
From: Texas


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
Go to the top of the page
 
cheekybuddha
post Nov 17 2019, 10:08 AM
Post#2


UtterAccess Moderator
Posts: 11,918
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
LagoDavid
post Nov 17 2019, 10:49 AM
Post#3



Posts: 556
Joined: 12-October 03
From: Texas


PERFECT! Thank you so much.
Go to the top of the page
 
cheekybuddha
post Nov 17 2019, 10:52 AM
Post#4


UtterAccess Moderator
Posts: 11,918
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
LagoDavid
post Nov 17 2019, 11:52 AM
Post#5



Posts: 556
Joined: 12-October 03
From: Texas


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
Go to the top of the page
 
LagoDavid
post Nov 17 2019, 12:43 PM
Post#6



Posts: 556
Joined: 12-October 03
From: Texas


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?
Go to the top of the page
 
cheekybuddha
post Nov 17 2019, 01:01 PM
Post#7


UtterAccess Moderator
Posts: 11,918
Joined: 6-December 03
From: Telegraph Hill


Try adding Me.Undo before Cancel = True

--------------------


Regards,

David Marten
Go to the top of the page
 
LagoDavid
post Nov 17 2019, 04:00 PM
Post#8



Posts: 556
Joined: 12-October 03
From: Texas


Works like a charm now. Thank you again.
Go to the top of the page
 
cheekybuddha
post Nov 19 2019, 02:53 AM
Post#9


UtterAccess Moderator
Posts: 11,918
Joined: 6-December 03
From: Telegraph Hill


yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 09:42 PM