understanding what i am doing so i can solve the issues! (1 Viewer)

[iankerry]

Hi All

I have spent the morning trying to work out where i am going wrong, and to try and understand what i am doing, but have failed. I have googled some of the error messages i have received and though i have solved them. Because i am not much of a programmer, i think i must be missing some basic concept with what i am trying to do, which is,

creat an email from a button, which includes lots of fields from my access database (sql backend).

First i setup the fields which will hold the data

Private Type SUMMARY_DATA
        dtDatefield As Date
        str1PromoterName As String
        str2Email As String
        str3showname As String
        str4time As String
        str5Venue As String
        cur6AdultTP As Currency
        cur7ChildTP As Currency
        cur8FamilyTP As Currency
        cur9otherTP As Currency
        str10EventID As Integer
        str11ADDRESS1 As String
        str12ADDRESS2 As String
        str13TOWN As String
        str14county As String
        STR15POSTCODE As String
        str16CoTel  As String
        str17CoMobile As String
        str18CoEmail As String
        cur19CostPromoter As Currency
        str20Extra As String
        str21Contractnotes As String

I ended up numbering these because i was getting the Too Few Parameters error a lot.

next comes the sql string to bring in the data... there are a lot of fields, I know, and there are likely to be more in the end, but i don't know another way around it.

  currentID = Me.EventID


emailSQL = "SELECT dbo_Promoters.NAME, dbo_Venues.VENUE, dbo_EventsLive.FamilyTP, dbo_EventsLive.extraeventinfo, dbo_EventsLive.contractnotes, dbo_EventsLive.ChildTP, " & _
"dbo_EventsLive.EventID, dbo_EventsLive.[event date], dbo_EventsLive.time, dbo_Promoters.email, dbo_EventsLive.AdultTP, dbo_eventslive.othertp, dbo_eventslive.[Promoter fee], " & _
"dbo_Companies.address1, dbo_Companies.address2, dbo_Companies.town, dbo_Companies.county, dbo_Companies.postcode, dbo_Companies.mobile ,  dbo_Companies.coEmail, dbo_Companies.[Tel:], dbo_Shows.[Show Name] " & _
"FROM (dbo_Shows INNER JOIN dbo_Companies ON dbo_Shows.CompanyID = dbo_Companies.ID) 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 " & _
"WHERE (dbo_EventsLive.EventID)= " & [currentID] & ";"

At the moment I get the Too Few Parameters error (expected 1), which i think is SQL releated - quotes, commas or wrong feild names etc. I have pasted the code into a new query and it runs ok.

The next stage is to pass the values over to my new email fields - the first sticking point is marked in red - and it to do with the SQL?

If OpenConnection Then
        If rs.State = adStateOpen Then rs.Close
         [B][COLOR="Red"]rs.Open emailSQL, cn, adOpenKeyset, adLockOptimistic[/COLOR][/B]
         
         If Not rs.EOF Then
            dblRecord = rs.RecordCount
            ReDim arrSummaryData(1 To dblRecord)
            'create an Array depends on number of records and put all data with features
            With rs
                 intRecordCount = 1
                 Do While Not .EOF
                      arrSummaryData(intRecordCount).str1PromoterName = IIf(IsNull(![NAME]), "", ![NAME])
                      arrSummaryData(intRecordCount).str2Email = IIf(IsNull(![email]), "", ![email])
                      arrSummaryData(intRecordCount).str3showname = IIf(IsNull(![Show Name]), "", ![Show Name])
                      arrSummaryData(intRecordCount).dtDatefield = IIf(IsNull(![event date]), "", ![event date])
                      arrSummaryData(intRecordCount).str4time = IIf(IsNull(![time]), "", ![time])
                      arrSummaryData(intRecordCount).str5Venue = IIf(IsNull(![VENUE]), "", ![VENUE])
                      arrSummaryData(intRecordCount).cur6AdultTP = IIf(IsNull(![AdultTP]), "", ![AdultTP])
                      arrSummaryData(intRecordCount).cur7ChildTP = IIf(IsNull(![ChildTP]), "", ![ChildTP])
                      arrSummaryData(intRecordCount).cur8FamilyTP = IIf(IsNull(![FamilyTP]), "", ![FamilyTP])
                      arrSummaryData(intRecordCount).cur9otherTP = IIf(IsNull(![Other1TP]), "", ![Other1TP])
                      arrSummaryData(intRecordCount).str10EventID = IIf(IsNull(![EventID]), "", ![EventID])
                      arrSummaryData(intRecordCount).str11ADDRESS1 = IIf(IsNull(![address1]), "", ![address1])
                      arrSummaryData(intRecordCount).str12ADDRESS2 = IIf(IsNull(![address2]), "", ![address2])
                      arrSummaryData(intRecordCount).str13TOWN = IIf(IsNull(![town]), "", ![town])
                      arrSummaryData(intRecordCount).str14county = IIf(IsNull(![county]), "", ![county])
                      arrSummaryData(intRecordCount).STR15POSTCODE = IIf(IsNull(![postcode]), "", ![postcode])
                      arrSummaryData(intRecordCount).str16CoTel = IIf(IsNull(![Tel:]), "", ![Tel:])
                      arrSummaryData(intRecordCount).str17CoMobile = IIf(IsNull(![mobile]), "", ![mobile])
                      arrSummaryData(intRecordCount).str18CoEmail = IIf(IsNull(!email), "", !email)
                      arrSummaryData(intRecordCount).cur19CostPromoter = IIf(IsNull(![Promoter Fee]), "", ![Promoter Fee])
                      arrSummaryData(intRecordCount).str20Extra = IIf(IsNull(![extraeventinfo]), "", ![extraeventinfo])
                      arrSummaryData(intRecordCount).str21Contractnotes = IIf(IsNull(![contractnotes]), "", ![contractnotes])
                  '=============================================================================================
                  
               
                     .MoveNext
                     intRecordCount = intRecordCount + 1
                 Loop
                .Close
            End With
         
         For i = LBound(arrSummaryData) To UBound(arrSummaryData)
           ' If Len(arrSummaryData(i).strEmail) <> 0 Then
                 Call DoRemote(arrSummaryData(i).str1PromoterName, arrSummaryData(i).str2Email, arrSummaryData(i).str3showname, arrSummaryData(i).dtDatefield, arrSummaryData(i).str4time, arrSummaryData(i).str5Venue, arrSummaryData(i).cur6AdultTP, arrSummaryData(i).cur7ChildTP, arrSummaryData(i).cur8FamilyTP, arrSummaryData(i).cur9otherTP, arrSummaryData(i).str10EventID, arrSummaryData(1).str11ADDRESS1, arrSummaryData(1).str12ADDRESS2, arrSummaryData(1).str13TOWN, arrSummaryData(1).str14county, arrSummaryData(1).STR15POSTCODE, arrSummaryData(1).str16CoTel, arrSummaryData(1).str17CoMobile, arrSummaryData(1).str18CoEmail, arrSummaryData(i).cur19CostPromoter, arrSummaryData(i).str20Extra, arrSummaryData(i).str21Contractnotes)

I hope I have posted enough info and in the correct format. If someone could see why i am getting the TOO FEW PARAMETERS error, and given that i have to add a lot more fields, is there a quicker/easier way to code what i am doing?

many thanks.

 

[NigelShaw]

Hi

I can help further tonight but in the meantime-

Look through my posts and you will find a good email example.

The error you are getting is because a parameter is expected obviously lol. You need QueryDefs. I have a good fix for this that I'll post later along with my email code if I can find it!

Cheers

Nidge

 

[iankerry]

ok thanks will do some searching on QueryDefs.

cheers, appreciated. Have been working on it all day now and nothing i try works! And i fooloshly promised the admin assistant i would have this button done for her by the 4th...

Ian

 

[NigelShaw]

Lol

If you still have trouble you can email the db to me for a closer look. I can fudge the SQL call with dummies.

Cheers

N

 

[iankerry]

Thats really kind of you, thanks. Only prob is that i am working on a front end access, connected to SQL server, so you won't get any data. Though having said that i could import some data into a copy of the dbase i am working on?

are you really up for that?

 

[spikepl]

You are using Time and Name as field names in your db. That is not recommended: http://allenbrowne.com/AppIssueBadWord.html#T

AS to what exactly causes the issue you can find yourself by deconstructing the query until it runs OK. Simply remove fields and joins one by one.

Prior to all that, check what currentID holds.

 

[iankerry]

Hi

Thanks for your reponse.

I appreciate that i am using some reserved field names. These were inherited and now form the basis on hundreds of reports AND run the website, and it gets expensive to get our WEB guys to make changes - so i am having to live with it. However up till now it hasn't caused any real problems.

I started stripping the SQL code back to basics at lunchtime, and i have just come acorss a field that seems to be causing some error. it is an email field, when i take it out my code gets further at least.

So will persevere. I cant help thinking though that i am doing this a long handed way. And as soon as i start adding fields, i seem to run into errors.
oh well, at least it is progress. thanks. ian

 

[spikepl]

Other stuff:

1. Check the GetRows method - that fills an array with the recordset
2. Instead of your IIF, use the NZ function
3. Why do you need the array in the first place? Normally one would fire off a mail while on the relevant record of the recordset

 

[iankerry]

Hi Spikepl,

thanks for your response. Much appreciated.

Having looked at the Nz and the getrows features, these would have been a much better way to start, though i havent used them before.

The arrays - i think my mistake was (not being a proficient programme at all) was that i borrowed the code from another part of our database which searched through lots of records to produce an email, and when i tried to take out aspects of the arrays that i thought i didnt need, it all went pearshaped.

So, when i have some time (!) i will have to relook at the whole thing and start again...

thanks again for your valuable advice.

ian