Incorrect Number of arguments when declaring sub

[TIbbs]

I am having problems with this code, not sure where i am going wrong.
My code looks for values in a table prior to appending, if no records are present in the table an error message is displayed, allowing the user to choose to open a form to input new data in.
I get this message: "Argument not optional", pointing at my OpenConnection.
My sub is available in another form and I declared it as public as well as the variables. bellow is the code. They work in my form1, as I tested them before.

Code in form1:

Option Explicit
' The member variables used throughout the project
Public XMLDoc As MSXML2.DOMDocument40
Public cnn As ADODB.Connection
Public rst As ADODB.Recordset
Public TypeName As String

' constant used throughout the code for when we want to
' save the document
Const XMLPath As String = "N:\Databases\XmlFile\download.xml"

Public Sub OpenConnection()
Set cnn = CurrentProject.Connection
End Sub

Public Sub CloseConnection()
cnn.Close
Set cnn = Nothing
End Sub

Code in current form2:

Private Sub cmdValidateInput_Click()
On Error GoTo Err_cmdValidateInput_Click
Dim sSQL As String
Dim intResponse As Variant

'Check to see if there are any values in the POPImport table.



intResponse = MsgBox("There are no values in the POP Import table. Would you like to add values now?", _
              vbYesNo + vbQuestion, "Edit Table")
sSQL = "SELECT Count(qryPOPImport.intOrderNumber) AS CountOfOrder " & _
       "FROM qryPOPImport;"

       
'Open connection to the database
OpenConnection
'set the recordset
Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open sSQL, cnn, adCmdText

'count the number of orders in the POP import table.
'Evaluate to proceed with the order processing

If rst.Fields("CountOfOrder") = 0 Then

'Give user the option to enter the new records.
If intResponse = vbYes Then
DoCmd.OpenForm "frmPOPImport", acFormDS, acEdit
Else
MsgBox "No new values"
End If

Else

'update Sku values in POP Import table
DoCmd.SetWarnings False
DoCmd.OpenQuery "udtImportPOPSku", acViewNormal, acReadOnly
DoCmd.SetWarnings True

ProcessPOPImport

End If

rst.Close
Set rst = Nothing
' Close Connection to database
CloseConnection
     
'Error handling
Exit_cmdValidateInput_Click:
    Exit Sub

Err_cmdValidateInput_Click:
    MsgBox Err.Description
    Resume Exit_cmdValidateInput_Click

End Sub

 

[KenHigg]

This is just a stab in the dark but maybe you need to call it something other than OpenConnection as that may be reserved - ??

 

[TIbbs]

This is just a stab in the dark but maybe you need to call it something other than OpenConnection as that may be reserved - ??

Then I don't understand how it works in my form1 when I run the code
I'll try and change and see if I get any luck.

 

[TIbbs]

Now I get another error "Sub or function not defined"?
I just thought of something? do I need to re-define the sub or functions for each form?
And if that is the case would a sub be better than a function in terms of performance and re-usability?

 

[MSAccessRookie]

You said:

Code in form1:
     .
     .
     .
Code in current form2:
     .
     .
     .

Does this mean that the Private Code is associated with the Form form1? I do not think that will work.

If you create a New Module to store it in (I call mine Common Subs), all of the forms should be able to find it.

 

[KenHigg]

Without getting into what it is you are trying to do, here are a couple of notes on sub and functions.

1. In general, if you want to run a pc of code from different forms, you put it in a stand alone module.
2. Subs are used when you don't need to return any values. Functions are used when you do.

Having said that I'm not sure why you even put the one line of code in a seperate routine...

 

[LPurvis]

Just to mention that "OpenConnection" is indeed a reserved word.
It's a method of the CurrentProject object (used in ADPs) and, prior to 2007, is a valid method of DAO's DBEngine and Workspace objects. (Since 2007 DAO doesn't support ODBCDirect and hence no valid connections, though AFAICR the methods are still present.)

If ever in doubt - check Allen Browne's definitive guide http://allenbrowne.com/AppIssueBadWord.html.
(Though he only lists it as the DAO consideration - it's still listed! ;-).

 

[TIbbs]

Thanks, I seemed to have soted it now. I created a module that holds the string connection. and refer all mu subs to open that connection

 

[TIbbs]

Without getting into what it is you are trying to do, here are a couple of notes on sub and functions.

1. In general, if you want to run a pc of code from different forms, you put it in a stand alone module.
2. Subs are used when you don't need to return any values. Functions are used when you do.

Having said that I'm not sure why you even put the one line of code in a seperate routine...

I have a series of lines of code below that refer to the sub openConnection and CloseConnection.Each sub opens a new recorset that is used to output tree nodes to an xml file. I use it to iterate to our tables and then send the information in the right order, to be processes by an external application. I thought it would not be worth trying to paste all of it here.
but just to give you an idea:

Sub ProcessXML_Click()
On Error GoTo Err_ProcessXML_Click
'Check to see if XML file does exist

If (Dir(XMLPath) = "") Then

'If no file create new file and append
LoadXML
CreateXMLTree
Call OpenDB
AppendProducts
Call CloseDB
saveXML

Else

'otherwise just append
LoadXML
Call OpenDB
AppendProducts
Call CloseDB
saveXML

End If

'Error Messages
Exit_ProcessXML_Click:
    Exit Sub

Err_ProcessXML_Click:
    MsgBox Err.Description
    Resume Exit_ProcessXML_Click

End Sub

Public Sub LoadXML()
On Error GoTo Err_LoadXML

Set XMLDoc = New MSXML2.DOMDocument40
XMLDoc.preserveWhiteSpace = False
XMLDoc.async = False
XMLDoc.resolveExternals = True
XMLDoc.Load (XMLPath)

'Error Messages
Exit_LoadXML:
    Exit Sub

Err_LoadXML:
    MsgBox Err.Description
    Resume Exit_LoadXML

End Sub

Public Sub saveXML()
    
    XMLDoc.Save (XMLPath)
  
End Sub

Sub CreateXMLTree()
On Error GoTo Err_CreateXMLTree
Dim objRoot As IXMLDOMElement
Dim Products, Customers, Suppliers, SOP, POP, Invoices, ProdGrp, Transactions, TaxRates, PriceLists, StockTrans As IXMLDOMNode
Dim xmlText As MSXML2.IXMLDOMProcessingInstruction

Set xmlText = XMLDoc.createProcessingInstruction("xml", "version=" & Chr(34) & "1.0" & Chr(34))

'Create xml Company root element
'and append it with text version of the document
Set objRoot = XMLDoc.createElement("Company")
XMLDoc.appendChild xmlText
XMLDoc.appendChild objRoot

'Create child nodes and add them to the tree


Set Products = XMLDoc.createElement("Products")
objRoot.appendChild Products


Set Customers = XMLDoc.createElement("Customers")
objRoot.appendChild Customers


Set Suppliers = XMLDoc.createElement("Suppliers")
objRoot.appendChild Suppliers

Set SOP = XMLDoc.createElement("SalesOrders")
objRoot.appendChild SOP

Set POP = XMLDoc.createElement("PurchaseOrders")
objRoot.appendChild POP

Set Invoices = XMLDoc.createElement("Invoices")
objRoot.appendChild Invoices

Set ProdGrp = XMLDoc.createElement("ProductGroups")
objRoot.appendChild ProdGrp

Set Transactions = XMLDoc.createElement("Transactions")
objRoot.appendChild Transactions

Set TaxRates = XMLDoc.createElement("TaxRates")
objRoot.appendChild TaxRates

Set PriceLists = XMLDoc.createElement("PriceLists")
objRoot.appendChild PriceLists

Set StockTrans = XMLDoc.createElement("StockTransactions")
objRoot.appendChild StockTrans
          
              
'Error Messages
Exit_CreateXMLTree:
    Exit Sub

Err_CreateXMLTree:
    MsgBox Err.Description
    Resume Exit_CreateXMLTree

End Sub

There are about 6 subnodes on this tree that we append to, so you get the idea, I need to make my code as re-usable as possible.

 

[KenHigg]

Sounds like you have a good plan going. Glad you have it working...