Full Version: Input Form And What To Do If Duplicates
UtterAccess Forums > Microsoft® Access > Access Forms
nmartin1230
I have an input form for tracking CD's that are sent out. I have an afterupdate event to run a duplicate query to see if that CD has already been sent out. Now, CD's can be duplicated for various reasons that is why I have a checkbox for Redo's. I am trying to write code to for the afterupdate event to check if the checkbox Redo is checked or not. If not, run query with data from form to see if it has already been made. If it has been made, popup a message box saying "it already has been made" click "OK" and clear form. If it hasn't been made, then don't run query and input info into this control box and continue entering data into form to create record. I have used me.recordsetclone.recordcount =>1 and the current dcount() and nothing seems to be counting how many records are in the query results. Here is my current code that I am using to no avail.....
CODE
Private Sub BoxNum_AfterUpdate()
DoCmd.SetWarnings False
Dim LResponse As Integer

If Me.Redo = 0 Then
DoCmd.OpenQuery "Find duplicates for tblBurnedCD"
If DCount("*", "Find duplicates for tblBurnedCD") > 1 Then
LResponse = MsgBox("This CD has already been made, checked and sent to the customer." & Chr(13) & "If this cd is a copy/redo then check the Redo checkbox.", vbOKOnly, "")
Else
Me.Password = [Text17] & "-Box" & [BoxNum]
If LResponse = vbOK Then
DoCmd.Close acQuery, "Find duplicates for tblBurnedCD", acSaveNo
Me.Undo
Else
Me.Password = [Text17] & "-Box" & [BoxNum]

DoCmd.SetWarnings True
End If
End If
End If
End Sub

Any help would be very much appreciated!
Doug Steele
What's the SQL for the query Find duplicates for tblBurnedCD? You're not passing any parameters to the query, so I don't see how it can know whether it's a duplicate unless the query is based on parameters provided by the form. (BTW, there's no reason to use DoCmd.OpenQuery: simply referring to the query in the DCount statement is sufficient)
nmartin1230
Thank you for your response! Yes the SQL is for the query 'Fin duplicates fro tblBurnedCD' and the parameters are being passed by the form. I will take the DoCmd as suggested and see if that changes anything. So, when I commented out the DoCmd line the query doesn't run. Do I need to change the Dcount line to make it work as you stated? Sorry, little new and confused by this.
Doug Steele
Not sure what you mean by "when I commented out the DoCmd line the query doesn't run". What makes you say that? You're correct that a window showing the results of the running the query doesn't appear, but it's not necessary. DCount evaluates the query without popping up.

What's the SQL for the query?
nmartin1230
What makes me say that is when I put in a BoxNum that I know is already in the table the MsgBox didn't popup. Here is the SQL for the query:
SELECT First(tblBurnedCD.JobTitle) AS [JobTitle Field], First(tblBurnedCD.BoxNum) AS [BoxNum Field], Count(tblBurnedCD.JobTitle) AS NumberOfDups
FROM tblBurnedCD
GROUP BY tblBurnedCD.JobTitle, tblBurnedCD.BoxNum
HAVING (((First(tblBurnedCD.JobTitle))=[Forms]![frmBurnedCD]![cboJobTitle]) AND ((First(tblBurnedCD.BoxNum))=[forms]![frmBurnedCD]![BoxNum]) AND ((Count(tblBurnedCD.JobTitle))>1) AND ((Count(tblBurnedCD.BoxNum))>1));
Doug Steele
Okay, I just cobbled together an example, and can guarantee that the DoCmd.OpenQuery statement is not necessary.

I must confess that I'm puzzled as to what your query is supposed to be showing. For one thing, regardless of how many CDs have been burned, it's going to return at most 1 row of data. And what is the purpose of the two First statements? Unless there's an ORDER BY, First is essentially a meaningless concept in Relational Database.

One thing I just noticed about your code is that LResponse is only set if DCount("*", "Find duplicates for tblBurnedCD") > 1, and its value is only checked in the Else part of that If statement! (Of course, you're checking whether they clicked on the OK button, and that's the only choice you've given them!) And you can't undo a change in the AfterUpdate event.

What about

CODE
Private Sub BoxNum_AfterUpdate()

Dim strWhere As String

  If Me.Redo = 0 Then

    strWhere = "JobTitle = '" & Replace[Me![cboJobTitle], "'", "''") & "' AND BoxNum = '" & Replace(Me![BoxNum], "'", "''") & "'"
    If DCount("*", "tblBurnedCD", strWhere) > 1 Then
      MsgBox("This CD has already been made, checked and sent to the customer." & Chr(13) & _
        "If this cd is a copy/redo then check the Redo checkbox.", vbOKOnly, "") = vbOK
    Else
      Me.Password = [Text17] & "-Box" & [BoxNum]
     End If
  
  End If
  
End Sub


although to be honest, I'd probably put it in the form's BeforeUpdate event, not the text box's AfterUpdate event.

(note that the code assumes that both JobTitle and BoxNum are text fields)
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.