Returns to calling Sub before executing all of the code?

[mrsdee]

I have a Sub that renumbers records to allow for record insertion
anywhere in the table. For some reason the code returns to the
returning procedure after executing the LOC DoCmd.RunSQL(strSQL). I
have tried moving the last LOC above the DoCmd stmts and it returns to
the caller without executing any of the DoCmd stmts.

Any ideas why this may be happening?

Public Sub reNumber()
Dim newCtrlNum As Integer, curRecCtrlNum As Integer, frm As Form,
strSQL As String

'save current control number prior to renumbering-do renumbering prior
to assignment
'of number to new rec to prevent incidence of duplicate ctrl nums
Set frm = Forms![Update Product Area Checklist]
curRecCtrlNum = frm![txtCtrlNbr]

strSQL = "UPDATE[Product Checklist] SET [Control Number] = [Control
Number] + 1 WHERE [Control Number] >= " & curRecCtrlNum

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

'assign number to new item
Me.txtCtrlNumber.Value = curRecCtrlNum

End Sub

Thanks!

 

[David Pawloski]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

Armando Blanco
Microsoft MVP

 

[Jeff Boyce]

Disregard the "you can't do this" response. It is either SPAM or something
worse. Follow the link at your own peril!

The person posting the response has used several different aliases, and is
now representing him/herself as an MVP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mrsdee]

Thanks for the heads up! But do you happen to know the cause of my
problem?

Disregard the "you can't do this" response. It is either SPAM or something
worse. Follow the link at your own peril!

The person posting the response has used several different aliases, and is
now representing him/herself as an MVP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a Sub that renumbers records to allow for record insertion
anywhere in the table. For some reason the code returns to the
returning procedure after executing the LOC DoCmd.RunSQL(strSQL). I
have tried moving the last LOC above the DoCmd stmts and it returns to
the caller without executing any of the DoCmd stmts.

Any ideas why this may be happening?

Public Sub reNumber()
Dim newCtrlNum As Integer, curRecCtrlNum As Integer, frm As Form,
strSQL As String

'save current control number prior to renumbering-do renumbering prior
to assignment
'of number to new rec to prevent incidence of duplicate ctrl nums
Set frm = Forms![Update Product Area Checklist]
curRecCtrlNum = frm![txtCtrlNbr]

strSQL = "UPDATE[Product Checklist] SET [Control Number] = [Control
Number] + 1 WHERE [Control Number] >= " & curRecCtrlNum

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

'assign number to new item
Me.txtCtrlNumber.Value = curRecCtrlNum

End Sub

Thanks!

 

[mrsdee]

Thanks for the heads up! But do you happen to know the cause of my
problem?

Disregard the "you can't do this" response. It is either SPAM or something
worse. Follow the link at your own peril!

The person posting the response has used several different aliases, and is
now representing him/herself as an MVP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a Sub that renumbers records to allow for record insertion
anywhere in the table. For some reason the code returns to the
returning procedure after executing the LOC DoCmd.RunSQL(strSQL). I
have tried moving the last LOC above the DoCmd stmts and it returns to
the caller without executing any of the DoCmd stmts.

Any ideas why this may be happening?

Public Sub reNumber()
Dim newCtrlNum As Integer, curRecCtrlNum As Integer, frm As Form,
strSQL As String

'save current control number prior to renumbering-do renumbering prior
to assignment
'of number to new rec to prevent incidence of duplicate ctrl nums
Set frm = Forms![Update Product Area Checklist]
curRecCtrlNum = frm![txtCtrlNbr]

strSQL = "UPDATE[Product Checklist] SET [Control Number] = [Control
Number] + 1 WHERE [Control Number] >= " & curRecCtrlNum

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

'assign number to new item
Me.txtCtrlNumber.Value = curRecCtrlNum

End Sub

Thanks!

 

[mrsdee]

And the answer is.... the field that I was attempting to update was not
updateable. It was AutoNumbered. Thanks anyway!

Thanks for the heads up! But do you happen to know the cause of my
problem?

Disregard the "you can't do this" response. It is either SPAM or something
worse. Follow the link at your own peril!

The person posting the response has used several different aliases, and is
now representing him/herself as an MVP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a Sub that renumbers records to allow for record insertion
anywhere in the table. For some reason the code returns to the
returning procedure after executing the LOC DoCmd.RunSQL(strSQL). I
have tried moving the last LOC above the DoCmd stmts and it returns to
the caller without executing any of the DoCmd stmts.

Any ideas why this may be happening?

Public Sub reNumber()
Dim newCtrlNum As Integer, curRecCtrlNum As Integer, frm As Form,
strSQL As String

'save current control number prior to renumbering-do renumbering prior
to assignment
'of number to new rec to prevent incidence of duplicate ctrl nums
Set frm = Forms![Update Product Area Checklist]
curRecCtrlNum = frm![txtCtrlNbr]

strSQL = "UPDATE[Product Checklist] SET [Control Number] = [Control
Number] + 1 WHERE [Control Number] >= " & curRecCtrlNum

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

'assign number to new item
Me.txtCtrlNumber.Value = curRecCtrlNum

End Sub

Thanks!