iankerry
Registered User.
- Local time
- Today, 13:42
- Joined
- Aug 10, 2005
- Messages
- 190
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
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.
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?
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.
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
Code:
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.
Code:
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?
Code:
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.