Public Sub

[rnutts]

Hi
I have various forms for users to enter data. When the form opens I wish to warn them of about the form adding records. To this end I have created a public sub

Public Sub AddRecordsub()

Dim Message, Title, MyValue
Message = "Entering This Form Will Add Records. Are You Sure This Is What You Want To Do" ' Set prompt.
Title = "Add Record Message" ' Set title.
' Display message, title.
retValue = MsgBox(Message, Title, vbYesNo)
If retValue = vbYes Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
End If
End Sub

Within the main form I have a command button to open the form with the code below

Private Sub cmdnewassitclient_Click()
On Error GoTo Err_cmdnewassitclient_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmassistcliententry"
AddRecordsub

Exit_cmdnewassitclient_Click:
Exit Sub

Err_cmdnewassitclient_Click:
MsgBox Err.description
Resume Exit_cmdnewassitclient_Click

End Sub

The problem I am having is when the Public sub is called I am getting an error saying Function or Sub is not defined.

Any ideas where I am going wrong and also am I heading in the right direction with my code as this is only the second Public Sub/Function I have created

Thanks

Richard

 

[aceto]

Change your public sub to a function and use it to
return the value returned by the call to msgbox back
to the click event. That way your public function does not
have to have any knowledge of the form you want to open.
It's only purpose is to return the user's decision on whether
to open it or not.

Public Function AddRecord() as vbMsgBoxResult
  Dim Message as String
  Dim Title String
  
  Message = "Entering This Form Will Add Records. Are You Sure This Is What You Want To Do" ' Set prompt.
  Title = "Add Record Message" ' Set title.

  ' Display message, title.
  AddRecord = MsgBox(Message, Title, vbYesNo)

End Function



Private Sub cmdnewassitclient_Click()
  On Error GoTo Err_cmdnewassitclient_Click

  Dim stDocName As String
  Dim stLinkCriteria As String

  stDocName = "frmassistcliententry"

  If AddRecord = vbYes Then
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec
  End if


Exit_cmdnewassitclient_Click:
Exit Sub

Err_cmdnewassitclient_Click:
  MsgBox Err.description
  Resume Exit_cmdnewassitclient_Click
End Sub

 

[Pat Hartman]

I'm confused. Just opening a form should not add a record. Do you have some stray code that is causing these records to be added without user interaction?

As to calling the sub, as long as the sub is in the same module as the calling procedure or is defined as public in a standard module, there shouldn't be a problem. However, your variables are not global and so the called sub will not have access to them. You can overcome this by using an argument.

Public Sub AddRecordsub(FormName, LinkCriteria)
Dim Message, Title, MyValue
Message = "Entering This Form Will Add Records. Are You Sure This Is What You Want To Do" ' Set prompt.
Title = "Add Record Message" ' Set title.
' Display message, title.
retValue = MsgBox(Message, Title, vbYesNo)
If retValue = vbYes Then
DoCmd.OpenForm FormName, , , LinkCriteria
DoCmd.GoToRecord , , acNewRec
End If
End Sub

....
Call AddRecordsub(stDocName, stLinkCriteria)
....

 

[rnutts]

I have tried the change to a public function however I am now getting the error message 'Type Mismatch' when I click the command button 'cmdnewassistclient'

Any Thoughts

 

[aceto]

The 2nd and 3rd parameter to the MsgBox function are
in the wrong order in the call to MsgBox.

It should be:
AddRecord = MsgBox(Message, vbYesNo, Title)

The error was cut & pasted from the code in your original post.