Run-time error '2501' DoCmd.OpenForm

[PADFOR]

I changed my primary key data type from integer to text, but when I go to open form using the primary key selected from the combo box, I receive the following message:

Microsoft Access

Run-time error '2501':

The OpenForm action was canceled.

You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box.

For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form.

I have included the code below:

Private Sub cmdNext_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim var As Variant

var = Forms![frmMaintainableItemParentSelection]![cmboMaintainableItemParentTag]

If IsNull(var) Then

MsgBox "No Maintainable Item Parent Tag has been selected, please select a Maintainable Item Parent Tag", , "Maintainable Item Parent Tag Warning"

Else

stDocName = "frmEditMaintainableItemParentData"
stLinkCriteria = "[MaintainableItemParentTag]=" & Me![cmboMaintainableItemParentTag]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit

End If

End Sub

The program stops at this line:
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit

Any help would be appreciated.

Regards

PADFOR

 

[ghubbell]

I won't ask why you've changed to a text primary. You must have a good reason!

Try this:

Private Sub cmdNext_Click()
On Error GoTo ErrCNC
DoCmd.RunCommand acCmdSaveRecord
If IsNull(Me.cmboMaintainableItemParentTag) Then
MsgBox "No Maintainable Item Parent Tag has been selected, please select a Maintainable Item Parent Tag", vbInformation, "Maintainable Item Parent Tag Warning"
Else
DoCmd.OpenForm "frmEditMaintainableItemParentData", , , "[MaintainableItemParentTag]=" & Me![cmboMaintainableItemParentTag], acFormEdit
End If

ExitCNC:
Exit Sub

ErrCNC:
MsgBox Err.Number & " " & Err.Description, vbInformation, "''Next button click'' error."
Resume ExitCNC
End Sub

Which is just cleaner code and saves your record before movement. If you still have difficulty, check that your primary is in fact the value you have in the combo and check that the form you're trying to open is as well prepared to accept a text value. Good luck, Gord
ghubbell@total.net

 

[PADFOR]

Thank you for your suggestions, but I still receive the same error. Any more suggestions would be welcomed.

PADFOR

 

[ghubbell]

Test this please:

Private Sub cmdNext_Click()
On Error GoTo ErrCNC
DoCmd.RunCommand acCmdSaveRecord
If IsNull(Me.cmboMaintainableItemParentTag) Or Me.cmboMaintainableItemParentTag = "" Then
MsgBox "No Maintainable Item Parent Tag has been selected, please select a Maintainable Item Parent Tag", vbInformation, "Maintainable Item Parent Tag Warning"
Else
DoCmd.OpenForm "frmEditMaintainableItemParentData", , , "[MaintainableItemParentTag] = " & Me![cmboMaintainableItemParentTag], acFormEdit
End If

ExitCNC:
Exit Sub

ErrCNC:
MsgBox Err.Number & " " & Err.Description, vbInformation, "''Next button click'' error."
Resume ExitCNC
End Sub

Essentially the same but we'll check for a zero length string in your combo too, and watch the spacing of the criteria line. When you changed to the string as an ID is it for sure the bound column in the combo? Remember combos columns are zero based so the info you're asking from it to open the other form has to be in its bound column whether it is hidden or not?
Is the field on "frmEditMaintainableItemParentData" really named "MaintainableItemParentTag"... A few things to check. Not to worry. We'll get it! Gord
ghubbell@total.net

 

[PADFOR]

I noticed that when I changed the code as shown below, it opened the form. The only problem is, you have to specific the record number. See code below:

Private Sub cmdNext_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim var As Variant

var = Forms![frmMaintainableItemParentSelection]![cmboMaintainableItemParentTag]

If IsNull(var) Then

MsgBox "No Maintainable Item Parent Tag has been selected, please select a Maintainable Item Parent Tag", , "Maintainable Item Parent Tag Warning"

Else

stDocName = "frmEditMaintainableItemParentData"
' stLinkCriteria = "[MaintainableItemParentTag]=" &
' Me![cmboMaintainableItemParentTag]
DoCmd.OpenForm stDocName, , , "[MaintainableItemParentTag] = '22' ", acFormEdit

End If
End Sub

As you can see from the code, the primary key ID has single quotes either side of it. When I run the original code, the primary key doesn't contain single quotes, and there fore doesn't work. So I have to place single quotes around the primary key value I select from the cmbo box. Any suggestions on how I would do that?

PADFOR

P.S. Cheers for your assistance on this problem.

 

[ghubbell]

Could it be as simple as changing your "Var" from a variant to a string?! Gord
ghubbell@total.net

 

[PADFOR]

I cracked it. Thanks for all your help. Please find the working code below:

Private Sub cmdNext_Click()

Dim stDocName As String
Dim stDocName1 As String
Dim stLinkCriteria As String
Dim var As String

If IsNull([cmboSystem]) Then

MsgBox "No Item Tag has been selected, please select a System Name/Tag", , "System Name/Tag Warning"

Else
stDocName1 = "frmEditSystemData"
var = "'" & [cmboSystem] & "'"
stLinkCriteria = "[SystemTag]=" & var
DoCmd.OpenForm stDocName1, , , stLinkCriteria, acFormEdit
End If

End Sub

I create a variable called "Var", and assigned cmboSystem to it, with single quotes either side. I then used "Var" as part of the "stLinkCriteria".

Cheers

PADFOR