You Canceled the Previous Operation message 2

[b31luv]

Here is the code I'm using:

If DCount "[COPProjectNo]", "tbStoredProjectNo", "[COPProjectInfo] = '" & Me.Text52 & "'") > 0 Then

MsgBox "The Project No You Enter already Exist.  Please Verify Project No and Re-Enter", vbCritical
        
Else

End If

It looks proper but I keep getting this message: You Canceled the Previous Operation

What I'm trying to do is verify that a project number is not in the table before I let the user proceed. The project number is part of the primary key which consists of two fields, however, I want to verify that project number is correct or not already listed first before the user continues. Am I getting this message because I'm trying to count records in the same table I'm updating? Or do I need to turn on a reference?

 

[BoxHead]

It's hard to say with what you've posted. I'm guessing that this code is a re-typed sample because there is no opening parenthesis on the DCount statement and there is an empty Else statement in your code.

What event fires this code? What is the rest of the code? When you say that you don't want the User to proceed, what is it they are trying to do. That would probably be the 'Previous Operation' that is being cancelled.


HTH



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[Tigerlili3]

You also do not need to have single quotes around Me.Text52. You could write the DCount portion of your code as follows

 DCount("[COPProjectNo]", "tbStoredProjectNo", "[COPProjectInfo] = Me.Text52")

You only need to enclose it in single quotes like that if you were inserting a variable there that refers to Me.Text52 that has already been resolved. Like this:

Dim strMyVariable as string
strMyVariable = Me.Text52

If DCount("[COPProjectNo]", "tbStoredProjectNo", "[COPProjectInfo] ='" & strMyVariable & "'")> 0 Then
    (Add what to do if true)
Else
    (Add what to do if false)
End If

 

[PHV]

I'm guessing that this code is a re-typed sample
aka cargo cult programming
Sorry, I couldn't resist.

 

[b31luv]

I revised the code as specified and I still get the same error. I'm using the AfterUpdate event. My intent is to verify whether or not a project number is already logged into the databaswe before the client is allowed to enter anymore information. There is no other operation happening during this process. The user is basically entering information for a project; project number, project title, project manager, project location, etc.

This is the code I was using at first:

    Dim CountInfo As Integer

    CountInfo = DCount("[COPProjectNo]", "tbStoredProjectNo", "[COPProjectInfo] = '" & Me.COPProjectNo & "'") > 0
    
    If CountActive > 0 Then
        
        MsgBox "The Project No You Enter already Exist.  Please Verify Project No and Re-Enter", vbCritical
        
    Else
    
    End If

The Else statement was left blank because if the number is not in the table then the user can proceed.

I've also tried using:

    Me.Text52 = DLookup("[COPProjectNo]", "tbStoredProjectNo", "[COPProjectInfo] = '" & Me.COPProjectNo & "'") 
    
    If IsNull(Me.Text52) Then
    
         Me.COPProjectTitle.SetFocus    
                
    Else
         MsgBox "The Project No You Enter already Exist.  Please Verify Project No and Re-Enter", vbCritical

    End If

.

Me.Text52 is unbounded.

As soon as I get this to work I will revise the title of Me.Text52 to something that fits what I'm trying to do.

The last thing I tried is what I posted in my first post. I hope this proivdes enough information. The part of the code that is giving me problem sees to be the DCount and the DLookup.

 

[BoxHead]

1. If you don't have a specific event to run on the Else statement, don't use Else. The textbox will be updated.

2. If you want to use meaningful names for controls, I'd suggest putting txt before textbox names, lbl before label names and such. It avoids the confusion caused by checking to see if the value of a field equals the value of a control when they are called the same thing.

3. It seems that what you're trying to do is pretty basic, but I'm confused by what is a control name and what is a field name so I'll give an example.

I have a table name tblProjects. In that table, I have a field named projID. I have a form for users to enter new projects and I want them to be able to create their own projID numbers, I just don't want any projID numbers to be repeated. So on the form, I have a textbox called txtProjId that is linked to the projID field in tblProjects and on the AfterUpdate event of that textbox I run the following code.

Dim myVal as String
myVal = txtProjId
If DCount("[projID]","tblProjects","[projID] = '" & myVal & "'") > 0 Then
txtProjId = ""
msgbox "That Number is already being used.  Enter another Project ID", vbOkOnly
End If

That code checks the entered value against the existing projID values in the table and if it already exists, sets the textbox back to a zero-length string, and displays a message explaining the problem.

If the projID is new and unique, the focus goes onto whatever field the user clicked on or tabbed to that fired the AfterUpdate event.

Perhaps a much simpler option would be to open the table in design view, select the field you want to contain unique values and on the Indexed property at the bottom of the page select "Yes (No duplicates)" Access will generate it's own message to prevent duplicate values.


HTH


John

 

[b31luv]

John,
Based on your assistance here is what I have.

    Dim myVal As String
        myVal = Me.COPProjectNo
    If DCount("[COPProjectNo]", "tbProjectInfo", "[COPProjectNo] = '" & myVal & "'") > 0 Then
        Me.COPProjectNo = ""
        MsgBox "That Number is already being used.  Enter another Project ID", vbOKOnly
        Me.COPProjectTitle.SetFocus
        Me.COPProjectNo.SetFocus
    End If

It works perfect. I can't set the Yes(No duplicates) because I may have one project number that is involves several different sites so I have to allow duplicates for that.

I do have other questions?

What you are doing and I am doing (see previous post) seem to be the similar. The only difference I see is here:

[b]Dim myVal as String
myVal = txtProjId[/b]
If DCount("[projID]","tblProjects","[projID] = '" & [b]myVal[/b] & "'") > 0 Then

I've used DCount and DLookup several times and I never had this problem. Could you provide a little insight? I was under the impression that the information returned from DCount was an Integer and that the information returned from the DLookup was a String. Based on what had to be done to get what I needed, I have to view DCount as a String. Why is this?

By the way, I appreciate your assistance. Thanks.

 

[BoxHead]

Glad it worked!

DCount is an integer, but the myVal is a holder for the string that was entered into the textbox. That's used in the criteria for what should be counted.

To be honest, I would have normally used "[projID] = '" & txtProjId & "'") > 0 until I read tigerlili3's post.

DLookup will return the datatype that you send it looking for.

I'm glad this works now.

 

[b31luv]

I guess I owe tigerlil3 a thanks also. I only tried the first portion of what he/she had written. I was so engrossed in what I was trying that I didn't even try the second portion.

Thanks guys