openrecordset troubles (1 Viewer)

[MartijnR]

In Access2000, I have a database which works almost fine. In a form, there is a combobox which determines the result of a query. When I set the combobox value and manually open the query, the correct results are displayed. But ofcourse, manually isn't what I want.

There is also a button on this form which :

1) checks the settings of several controls on the form (a.o. the combobox)
2) according to those settings, generate a pricelist
3) " " , do something with the pricelist (send to monitor, printer, mail, etc)

- The combobox displays the name of a e-mailgroup (sets the value for a condition in the query)

- The query contains an autonr field and a textfield

- The records returned by the query should be ennummerated (?? don't know if this is the word I'm looking

for) the values should be added after the previous value seperated with a ; (to use it for addressing in a mailclient)

Now this last part is where I'm stuck. I keep getting errormessages, for example -- 3061 Too few parameters. Expected 1. --- and I cannot find the explanations of the errornumbers in the helpfile.

In the past (I have posted a similar cry for help before) the references were mentioned as a source of the error, but I found nothing special here. The ones that are checked :
- Visual Basic For Applications
- Microsoft Access 9.0 Object Library
- OLE Automation
- Microsoft DAO 3.6 Object Library

The main problem seems to be that I cannot/don't know how to use a recordset in VBA. Maybe I'm using the wrong declarations? I've checked several documentations for example code but I allways get some error that the code isn't right *sigh*

 

[fuzzygeek]

Error 3061

Post the code.

The following two links describe the error you're getting

http://support.microsoft.com/default.aspx?scid=kb;en-us;198462

http://support.microsoft.com/default.aspx?scid=kb;en-us;210244

The following white paper talks about ADO and DAO coding and shows a lot of examples how to do recordsets and etc in both.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

 

[MartijnR]

error 3061 is just one of the errornrs I get, it was an example, sorry.

here's the complete code (I know it's not as it should be on some places, but never bother to rewrite as my access skills progressed).

the part between the ********** isn the part I'm talking about. There are some '' in the code now for testing and not have the rest of the code executed

Private Sub OK_knop_Click()
On Error GoTo Error
Dim LandFilter As String
Dim klein As String
Dim groot As String
Dim DoubleQuote As String
Dim CustomError As String
CustomError = ""
DoubleQuote = Chr(34)
klein = "1"
groot = "9"
LandFilter = ""
DoCmd.GoToRecord , , acFirst
Do While klein < groot
If Check = True Then
If klein = "2" Then
LandFilter = LandFilter & " OR [Land] = " & DoubleQuote & LAND & DoubleQuote
End If
If klein = "1" Then
LandFilter = "[Land] = " & DoubleQuote & LAND & DoubleQuote
klein = "2"
End If
End If
Check = True
DoCmd.GoToRecord , , acNext, 1
Loop

GotFilter:
Dim RapportNaam As String
Dim Format As String
RapportNaam = "rapPrijslijst"
Select Case UitvoerNaar
Case 1
DoCmd.OpenReport RapportNaam, acViewPreview, , LandFilter
Case 2
DoCmd.OpenReport RapportNaam, acNormal, , LandFilter
Case 3
Dim FileNaam As String
Dim Extensie As String
DoCmd.Hourglass 1
FileNaam = "C:\HollandCichlids\Lijsten\HC Pricelist " & Date
Select Case Bestandstype
Case 1
Extensie = ".XLS"
Format = acFormatXLS
Case 2
Extensie = ".RTF"
Format = acFormatRTF
End Select
DoCmd.OpenReport RapportNaam, acViewPreview, , LandFilter
Target = FileNaam & Extensie
DoCmd.OutputTo acReport, RapportNaam, Format, Target
DoCmd.Close acReport, RapportNaam
DoCmd.Hourglass 0
Case 4
Dim Subject As String
Dim MessageText As String
Subject = "Pricelist per " & Date
MessageText = "Dear Client," & Chr(13) _
& Chr(13) _
& "Attached to this mail you will find our latest pricelist." & Chr(13) _
& Chr(13) _
& "Best regards," & Chr(13) _
& Chr(13) _
& "Bert Ruijsbroek"
Select Case Adressenboek
Dim ToAdres As String
Dim BCCAdres As String
Case 1
ToAdres = Me.KlantEmail
BCCAdres = ""
Case 2
ToAdres = ""
'*********************************************************************************************************
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set db = CurrentDb()

Set rs = db.OpenRecordset("qryMailgroepEmailPrijslijst", dbOpenDynaset)




rs.Close
db.Close

''Set qryDb = CurrentDb.OpenRecordset("qryMailgroepEmailPrijslijst")

'*********************************************************************************************************
Case 3
ToAdres = Me.emailadres
BCCAdres = ""
End Select
''If ToAdres = "" Then
'' CustomError = "1"
'' GoTo Error:
''End If
Select Case Bewerken
Dim EditMail As Boolean
Case 1
EditMail = True
Case 2
EditMail = False
End Select
Select Case Bestandstype
Case 1
Format = acFormatXLS
Case 2
Format = acFormatRTF
End Select
'' DoCmd.Hourglass 1
'' DoCmd.OpenReport RapportNaam, acViewPreview, , LandFilter
'' DoCmd.SendObject acReport, RapportNaam, Format, ToAdres, , , Subject, MessageText, EditMail
'' DoCmd.Close acReport, RapportNaam
'' DoCmd.Hourglass 0
'' Me.KlantEmail.Value = ""
'' Me.emailadres.Value = ""
'' Me.KlantGroep.Value = ""
End Select

Error:
' 0 = no error
' 2105 = you've reached the last record
' 2501 = cancel was pressed
If Err.Number = "0" Then
ElseIf Err.Number = "2105" Then
Resume GotFilter
ElseIf Err.Number = "2501" Then
Exit Sub
ElseIf Err.Number = "2448" Then
Resume
Else
MsgBox Err.Number & Err.Description
End If
If CustomError = "1" Then
MsgBox "Vul een Emailadres in"
Exit Sub
End If
End Sub

 

[fuzzygeek]

Recordset

Is qryMailgroepEmailPrijslijst a parameter query???

 

[MartijnR]

eureka

no, it isn't, but I 've found the code somewhere else on this forum

if finally works! (thanks Travis)

I thought DAO was the way to go and not ADO


Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim sSQL As String
Dim sMessage As String
Set cnn = CurrentProject.Connection

sSQL = "SELECT tblMailgroepLeden.MGId, tblKlanten.emailadres AS EMADR FROM " _
& "tblMailGroepen INNER JOIN (tblKlanten INNER JOIN tblMailgroepLeden ON " _
& "tblKlanten.klantenid = tblMailgroepLeden.KlantenId) ON tblMailGroepen.MGId " _
& "= tblMailgroepLeden.MGId WHERE " _
& "(((tblMailgroepLeden.MGId)=" & Me.KlantGroep & "));"

rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly
rst.MoveFirst
Do While Not rst.EOF
sMessage = sMessage & rst.Fields("emadr") & "; "
rst.MoveNext
Loop
rst.Close
ToAdres = ""
BCCAdres = sMessage

 

[RichMorrison]

Martijn,

I don't have time to examine your code. But I can assure you from my experience that DAO works well in Access 2000.

DAO is the preferred interface if you are using native Access tables.

RichM

 

[Freddy]

I just wanted to say you guys Rock!