Yet another 2501 error..

[melaniega]

I've been reading ya'lls threads on 2501 Open Form errors. I have been trying to implement what solutions I can (trying to modify them to fit my needs - obviously not well enough) and am still getting this error. Other errors make sense and are easy to fix, this one almost seems illogical. I do not have a VBA background, but have background in Java, JavaScript, webdesign, and MatLab... Let's just say I'm thoroughly confused.

I am trying to make sure people don't duplicate errors and freak out the macros in the database of report review status. Basically I want to perform a logical comparison of the report number (PI) to the entire table of report nubmers and if it duplicates a record, go to that record for editing (in the same form).

Any help on this would be greatly appreciated.

(code follows)
***************

Private Sub PI_AfterUpdate()
pinumber = DLookup("PI", "Report_Status", strPI)
If Not IsNull(pinumber) Then
'do this
'Message box warning of duplication
MsgBox "Review Status for PI # " _
& PI & " already exists." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Entry"
'Take User to record for editing
'Dim stDocName As String
'Dim stLinkCriteria As String
'stDocName = "Edit_Review_Status"
'stLinkCriteria = "[PI]=" & Me![PI] & ""
'stLinkCriteria = "PI = Me![PI]"
DoCmd.OpenForm "Edit_Review_Status", , , "[Edit_Review_Status]![PI]=" & Me![PI]
End If
End Sub

***************

 

[melaniega]

Sorry 'bout that. I commented everything out because I was getting mad at it.

I was trying it another way at first, and it kept giving the same error, and when I fixed one thing, it'd give a different error, I'd end up reverting back to the original version (just by circular reasoning) and back to the 2501 error again.

 

[TheAceMan1]

How are ya melaniega . . .

[blue]Basically I want to perform a logical comparison of the report number (PI) to the entire table of report nubmers and if it duplicates a record, go to that record for editing ([purple]in the same form[/purple]).[/blue]

You don't have to open another form to goto a record in the same form. Example:

[blue]   Dim Cri As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Cri = "[PI] = " & Me!PI
   
   If Not IsNull(DLookup("PI", "Report_Status", "[PI] = " & Me!PI)) Then
      MsgBox "Review Status for PI # " & PI & " already exists." & DL & _
             "You will now be taken to the record.", _
             vbInformation, _
             "Duplicate PI Detected! . . ."
      
      Me.Undo [green]'undo the duplicate PI.[/green]
      Me.Recordset.FindFirst Cri [green]'goto existing PI![/green]
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[melaniega]

Well that sounds fantastic! I will have to try that out at work tomorrow.

Thank you - here's hoping it works!

 

[HaworthBantam]

I've not read any other threads on this topic (I usually lurk around the VB6 forum), so I'm not sure what's already been discussed, but I've had experience of this "2501 - The Open Report Action was Cancelled...", and it drove me up the wall because nothing seemed obviously wrong.

My "solution" was to change the default printer and, magically, it worked - I could only assume (probably wrongly) that it was a printer driver issue.

Apologies if your problem is unrelated...

 

[melaniega]

I had that (printer) issues earlier today - without having made this change yet, and it was resolved by changing the default printer. The printer's margins/paper size didn't agree with the print settings for my report.

 

[melaniega]

TheAceMan1:

I put in your code, no more 2501.. got a (3464) data type mismatch error in criteria expression error. Clicked debug, and it's highlighting the If statement...
VBA hates my guts

 

[melaniega]

Ok, I fixed this and now I go to a blank/new entry...
it appears to be doing "Me.Undo 'undo the duplicate PI." but not the "Me.Recordset.FindFirst Cri 'goto existing PI!"...

 

[TheAceMan1]

melaniega . . .

Post what you currently have!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[melaniega]

   Dim Cri As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Cri = "[PI] = " & Me!PI
   
   If Not IsNull(DLookup("PI", "Report_Status", [PI])) Then
      MsgBox "Review Status for PI # " & PI & " already exists." & DL & _
             "You will now be taken to the record.", _
             vbInformation, _
             "Duplicate PI Detected! . . ."
      
      Me.Undo 'undo the duplicate PI.
      Me.Recordset.FindFirst Cri 'goto existing PI!
   End If

 

[TheAceMan1]

melaniega . . .

Change to:

[blue]   Dim Cri As String, DL As String
      
      DL = vbNewLine & vbNewLine
      Cri = "[PI] = '" & Me!PI & "'"
      
      If Not IsNull(DLookup("PI", "Report_Status", Cri)) Then
         MsgBox "Review Status for PI # " & Me.PI & " already exists." & DL & _
                "You will now be taken to the record.", _
                vbInformation, _
                "Duplicate PI Detected! . . ."
         
         Me.Undo 'undo the duplicate PI.
         Me.Recordset.FindFirst Cri 'goto existing PI!
      End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[melaniega]

That code works the same as previous, tells user of the duplication, and then opens blank, not the record they almost duplicated. I really appreciate all the help...

 

[melaniega]

It appears that the MeUndo would clear the current form, thereby setting Me!PI to Null, and resetting the criteria to null, and then sending me to a null (blank) form. Is this an incorrect assumption?

 

[TheAceMan1]

melaniega . . .

I've simulated what you've provided and have no problems with the code I provided. So questions ...
[ol][li]Is the datatype of PI [blue]numeric[/blue] or [blue]text[/blue] (check the table to be sure)?[/li]
[li]Is PI a [blue]primarykey?[/blue][/li]
[li]Name of the form your in?[/li]
[li]Post the [blue]record source[/blue] of the above form. If its a query name, post the [blue]SQL[/blue] of the query.[/li]
[li]Whats the name of the table with all the report numbers?[/li]
[li]Are you still using the same event:[blue]PI_AfterUpdate()[/blue]?[/li]
[li]What version access?[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[melaniega]

1. The PI "number" is of type text (because we have leading zeros)

2. Totally not a primary key.

3. Form within is same form I want to open (Edit_Review_Status)

4. SELECT Report_Status.PI, Report_Status.[Other PI], Report_Status.[Report Type], Report_Status.[Project Manager], Report_Status.[Assigned_to], Report_Status.Recieved, Report_Status.[QC/QA?], Report_Status.[Design Policy], Report_Status.[Prog Control], Report_Status.[Environ Adm], Report_Status.[Traffic Ops], Report_Status.[Project Review], Report_Status.Bridge, Report_Status.[Finance Mgmt],

5. Report_Status

6. Yes, I am using the PI_AfterUpdate()

7. 2007 - the bane of my existance.

I really appreciate all the help (sorry for the delay, I'm legally not allowed to work on work stuff when not "at work" - silly government rules)