Too Many Arguments

iankerry

Registered User.
Local time
Today, 19:10
Joined
Aug 10, 2005
Messages
190
Hi all,

I have taken over some code and am struggling with a part of it. The code below works fine and does what it needs to do. I have added a couple of new fields to it, and that was fine too.
However, I have reached the point where i can't add anymore fields as I get "too many arguments".

So is there a maximum number and if so is there a way around it?
I use this routine to create emails with information in from the database, and I need to add more info.

The error comes on the Public Function DoRemote Line.
Thanks


Code:
sSQL = "SELECT dbo.shows.credit as CRD, dbo.promoters.treasureremail as TE, dbo.venues.paymentnotes AS paynotes, dbo.venues.paymentaccountnumber as PAN, dbo.venues.paymentsort as PS, dbo.venues.paymentname as PN, dbo.shows.[get out] as GO, dbo.shows.[get in] as GI, dbo.shows.[Running Time] as RT, dbo.shows.sound AS Snd, dbo.shows.lighting as Lght, dbo.shows.stage as Stg, dbo.shows.[perf space] as perfsp, dbo.shows.layout as layout, dbo.eventslive.[print requirements] as PR, dbo.Promoters.address1 as PAdd1, dbo.Promoters.address2 as PAdd2, dbo.Promoters.name as PName, dbo.Promoters.town as PTown, dbo.Promoters.county as PCounty, dbo.Promoters.postcode as PPostcode, dbo.Promoters.email as Pemail, dbo.Promoters.[box office], dbo.Promoters.phone as Pphone, " & _
    "dbo.Companies.mobile, dbo.companies.company, dbo.EventsLive.[event date], dbo.venues.[venue postcode] as VPostcode, dbo.EventsLive.Time, dbo.Companies.[Tel:], dbo.Companies.address1, dbo.Companies.address2, dbo.companies.[VAT Registered] as CVat, dbo.companies.[contact name],dbo.companies.[email] as Cemail, " & _
    "dbo.EventsLive.AdultTP,  dbo.EventsLive.FamilyTP, dbo.EventsLive.ChildTP, dbo.EventsLive.extraeventinfo, dbo.EventsLive.contractnotes, dbo.EventsLive.[Actual Cost of Show], dbo.EventsLive.[Accom costs etc],dbo.EventsLive.[Total Cost of Event]," & _
    "dbo.Shows.[Show Name], dbo.shows.refresh as ikrefresh, dbo.shows.cost,  dbo.eventslive.PromoShowCost, dbo.eventslive.PromoOnCosts,  dbo.eventslive.PromoTotalCost," & _
    "dbo.Companies.town as ctown, dbo.Companies.county as ccounty, dbo.Companies.postcode, dbo.EventsLive.EventID, dbo.EventsLive.webadultsold, dbo.EventsLive.webchildsold, dbo.EventsLive.webfamilysold," & _
    "dbo.Venues.VENUE, " & _
    "dbo.EventsLive.othertp, dbo.whichprojectlive.whichproject, dbo.EventsLive.[Promoter fee] FROM dbo.Companies INNER JOIN (dbo.Shows INNER JOIN (dbo.WhichProjectLive INNER JOIN (dbo.Venues INNER JOIN (dbo.Promoters INNER JOIN dbo.EventsLive " & _
    "ON dbo.Promoters.ID = dbo.EventsLive.PromoterID) ON dbo.Venues.ID = dbo.EventsLive.VenueID) " & _
    "ON dbo.WhichProjectLive.ID = dbo.EventsLive.WhichProject_ID) ON dbo.Shows.ID = dbo.EventsLive.ShowID) " & _
    "ON dbo.Companies.ID = dbo.EventsLive.CompanyID " & _
    "WHERE (((dbo.EventsLive.EventID)= '" & strFrmEventID & "' Or (dbo.EventsLive.EventID) Is Null));"

    'OPEN THE RECORDSET AND EXECUTE
    Set m_oRecordset = New ADODB.Recordset
    m_oRecordset.Open sSQL, oConnection1, adOpenStatic, _
    adLockBatchOptimistic, adCmdText
    'm_oRecordset.MarshalOptions = adMarshalModifiedOnly
    m_oRecordset.MarshalOptions = adMarshalAll
    Set m_oRecordset.ActiveConnection = Nothing
      
    '##count records for testing##
    With m_oRecordset
 
        'PASS THE RESULTS TO THE DoRemote ROUTINE

        'if you get an error here about wrong number of arguments, then the proble might be in the Public Function DoRemote below... just saying...
        
        Call DoRemote(Nz(!CRD, ""), Nz(!TE, ""), Nz(!paynotes, ""), Nz(!PAN, ""), Nz(!PS, ""), Nz(!PN, ""), Nz(!GO, ""), Nz(!GI, ""), Nz(!RT, ""), Nz(!Snd, ""), Nz(!lght, ""), Nz(!stg, ""), Nz(!perfsp, ""), Nz(!layout, ""), Nz(!pr, ""), Nz(!Company, ""), Nz(!ikrefresh, ""), Nz(!pNAME, ""), Nz(!cEmail, ""), Nz(![Show Name], ""), Nz(![event date], ""), Nz(!Time, ""), Nz(!Vpostcode, ""), Nz(!VENUE, ""), Nz(!AdultTP, ""), _
        Nz(!ChildTP, ""), Nz(!FamilyTP, ""), Nz(!OtherTP, ""), Nz(!EventID, ""), Nz(!address1, ""), Nz(!address2, ""), Nz(!town, ""), _
        Nz(!postcode, ""), Nz(![Tel:], ""), Nz(!mobile, ""), Nz(![Promoter Fee], ""), Nz(!extraeventinfo, ""), _
        Nz(!contractnotes, ""), Nz(![Cvat], ""), Nz(![Box Office], ""), Nz(!WhichProject, ""), Nz(![contact name], ""), Nz(!padd1, ""), Nz(!padd2, ""), Nz(!Ptown, ""), Nz(!pcounty, ""), Nz(!ppostcode, ""), Nz(!pemail, ""), Nz(!PPhone, ""), _
        Nz(![Actual Cost of Show], ""), Nz(![Accom costs etc], ""), Nz(![Total Cost of Event], ""), Nz(!WhichProject, ""), Nz(!PromoShowCost, ""), Nz(!PromoOnCosts, ""), Nz(!PromoTotalCost, ""), Nz(!WEBfamilySold, ""), Nz(!WEBadultSold, ""), Nz(!WEBchildSold, ""))
  

        'CLOSE THE RECORDSET (GOOD HOUSE KEEPING)  23
        m_oRecordset.Close
        oConnection1.Close

        Set m_oRecordset = Nothing
        Set oConnection1 = Nothing
    End With

End Sub

Public Function DoRemote(TE As String, paynotes As String, PAN As String, PS As String, PN As String, STRGO As String, STRGI As String, STRRT As String, _
STRSnd As String, lght As String, stg As String, perfsp As String, layout As String, pr As String, Company As String, refresh As String, str1PromoterName As String, cEmail As String, _
str3showname As String, dtDatefield As Date, str4time As String, Vpostcode As String, _
str5Venue As String, cur6AdultTP As String, cur7ChildTP As String, _
cur8FamilyTP As String, cur9othertp As String, str10EventID As Integer, _
str11ADDRESS1 As String, str12ADDRESS2 As String, str13TOWN As String, _
str14county As String, STR15POSTCODE As String, strTel As String, _
str17CoMobile As String, cur19Promoterfee As Currency, str20Extra As String, _
str21Contractnotes As String, strCoVAt As String, strBO As String, strWP As String, StrContactname As String, padd1 As String, padd2 As String, Ptown As String, pcounty As String, ppostcode As String, pemail As String, PPhone As String, _
strActualCostofShow As String, strAccomcostsetc As String, strTotalCostofEvent As String, strWhichProject As String, strPromoShowCost As String, strPromoOnCosts As String, strPromoTotalCost As String, strwebfamilysold As String, strwebAdultsold As String, strwebChildsold As String)
 
??You are calling DoRemote with
Code:
 Call DoRemote(Nz(!CRD, ""), Nz(!TE, ""),
But the function definition says that TE should be the first parameter
Code:
Public Function DoRemote(TE As String, paynotes As String,...
My guess is you have added fields to the Call, but not to the function itself.
 
Hi jdraw,

Thanks for replying, much appreciated. Sorry I must have slightly changed the code before pasting, but i have rectified it now, and the code def runs ok.

But the problem still remains, if i wish to add another field to the Call Remote line, and the DoRemote line I get "Too many arguments".

is there a limit to the number i can have?

Ian
 
Can you not just pass the recordset?
 
That's mind blowing! Surely there is a better way for whatever it is you are doing.
Regardless, I counted something like 51 parameters in the call and 56 in the function itself, which I would expect would raise a different error. Maybe I just lost count somewhere in all that.
 
Can you not just pass the recordset?

I am not really sure what that means Gasman to be honest. The thing is however ugly it looks it has been working, and i just want to add another field - which i thought would be simple!
 
is this related? i would assume it would apply to you as well.


furthermore, the fact that table fields are limited to ''x'' bytes and stuff goes all the way back to the beginning of machines.
 
That's mind blowing! Surely there is a better way for whatever it is you are doing.
Regardless, I counted something like 51 parameters in the call and 56 in the function itself, which I would expect would raise a different error. Maybe I just lost count somewhere in all that.

Yeah it is rather isn't it - if there is a better way it would be good to know, but i am not much of a coder, and this is a system that helps the charity i work for, operate smoothly and I don't want to mess it up!

I'll go away and count now, but I think if there was a difference in the numbers the code wouldn't run would it?

Ian
 
is this related? i would assume it would apply to you as well.


furthermore, the fact that table fields are limited to ''x'' bytes and stuff goes all the way back to the beginning of machines.

That'll be it - seems like there are 59 in this routine. I'll do some research to see if i can work out what ParamArray means!

Thanks
Ian
 
That'll be it - seems like there are 59 in this routine. I'll do some research to see if i can work out what ParamArray means!

Thanks
Ian
do you know how that was solved ian? i have a page on my website that explains google's algorithm in detail. let me know if you want to read it.

=>
https:// www. google. com/ search? q=ms +access +too+ many+ arguments
(without all the spaces)
 
I'll do some research to see if i can work out what ParamArray means!

when it talks about "user defined type", it is talking about object oriented programming concepts. in the link I posted, when it talks about passing an array to the function, it is also talking about arguments like this:
Code:
function f_name(arg1() as string) as return_value_here
 
if there was a difference in the numbers the code wouldn't run would it
Correct, and it doesn't, does it? To suggest a better method would require some context, but it seems that the current method is becoming unmanagable. It would be quite bizarre if the table being edited is in the same database as where this code lies. If it's not, then why try to edit data remotely as opposed to doing it over there? Or maybe call a function that runs a simple query "over there".
 
Was that a slap on the wrist vba? might be too subtle for me...

but i ALWAYS go to google to look for answers first, amongst other things i typed "compile error too many arguments, " but i didnt find that clear page that you had.

firstly i typed in "too many arguments" and reached the marriage guidance page...
 
Here are some specs
But if you can tell us what is beg done -some additional context- then maybe there are some options.
Can you post a copy or some related info to help with what the code is trying to achieve?

Yes terms like relationships and arguments often need a "database" support adjective.....
 
Last edited:
firstly i typed in "too many arguments" and reached the marriage guidance page...
LOL! no it wasn't, Ian. don't worry, I'm not an ass. do you want to read about how google's algo works? I'm offering.
 
Correct, and it doesn't, does it? To suggest a better method would require some context, but it seems that the current method is becoming unmanagable. It would be quite bizarre if the table being edited is in the same database as where this code lies. If it's not, then why try to edit data remotely as opposed to doing it over there? Or maybe call a function that runs a simple query "over there".

Thanks Micron - yes it does run at the moment, as I think we have established that 59 arguments is the limit. The tables I reference are on an SQL server, with linked table in my database.

All of this as you say unmanageable code is just to come up with an email like this:

Dear Matthew

This is a formal agreement and confirmation of details between yourself and Arts Alive.

To confirm your booking, please check all the details carefully then reply to this email, s....

Best wishes, The Arts Alive Team
___________________________________________________________________________________________________________________________________________________

EVENT DETAILS
Date: 24/04/2020
Show: The Hard Way
Venue: Cl****sh Hall SY****
Time: 19:30:00
Adult Ticket Price: £10 Child Ticket Price: £6 Family Ticket Price: £ no family tickets
Box Office details for all publicity: 0158******
___________________________________________________________________________________________________________________________________________________

COMPANY INFO
Company: Louise Jordan
Contact name and address: Louis....
Company email: lrdan.co.uk
Company Mobile: 0791 Company Tel: 07
Is Company VAT registered?: No

Get-in time required: 90mins
Get-out time required: 60mins


With all those fields coming from the code.

No being much of a coder and i am not sure i know where to start... and it would take up way to much of your and the forums time!

cheers
ian
 
LOL! no it wasn't, Ian. don't worry, I'm not an ass. do you want to read about how google's algo works? I'm offering.
Phew,

and yes please - why not - will save me banging my head against this wall! :-)
 
Here are some specs
But if you can tell us what is beg done -some additional context- then maybe there are some options.
Can you post a copy or some related info to help with what the code is trying to achieve?

Yes terms like relationships and arguments often need a "database" support adjective.....

Thanks jdraw, I'll stick that on my wall!

Our database system holds details of over 1,000 events we do year in rural communities in the UK. The data sits on a azure server and i have linked tables locally.

This whole routine is really set up to allow me to push a button, the code runs and depending on certain field values, it creates a perfect contract in an email for the whole event, listing all the details we need to know and the other parties need to know.

Do that help?

Ta
Ian
 

Users who are viewing this thread

Back
Top Bottom