Avatar billede kimsand Nybegynder
12. februar 2015 - 09:33 Der er 20 kommentarer og
1 løsning

Kan ikke bruge db.openrecordset

Jeg har lavet et lille program som jeg bruger til at holde styr på hvem i vores organisation der har hvilke programmer, og når der så kommer opdatering så kan jeg lave en automatisk mail med vedhæftning af det rigtige program til de medlemmer der står på listen.

Det nedenstående forbereder data til mail og opretter til sidst mailen med emailadresser, programnavn og vedhæftet program.

Det eneste jeg mangler er at lave maillisten, og det gør jeg ved at lave en db.openrecordset(QUERY) og så gå hver record igennem.

Den Query er helt ren, og indeholder kun 1 felt, men den er lavet på baggrund af en anden Query med masser af kriterier, hvoraf nogle er valgt af brugeren.

Problemet er bare at jeg aldrig kommer så langt :) Mit program fejler ved Set rs=d.OpenRecordset("F_brugernavne", dbOpenDynaset), hvor jeg får fejlen "run-time error '3061' Der er for få parametre. Der var ventet 1.

Jeg har været rigtig mange steder på nettet, og det eneste jeg har kunne finde er at den fejl kan opstå hvis man bruger en Query med masser af kriterier som basis. Det gør jeg ikke, da jeg jo bruger en Query som kun har 1 felt(brugernavnene), uden nogen kriterier.

Er der nogen der kan guide mig til en løsning ?

Dim mailliste As String
Dim appnavn As String
Dim sti As String
Dim oOutlook As Object
Dim oEmailItem As Object
Const olmailitem = 0
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim x As Integer
Dim y As Integer

mailliste = ""
'Check om der er nogen i den valgte distributionsliste
Set db = CurrentDb()
Set rs = db.OpenRecordset("F_brugernavne", dbOpenDynaset)
'Set rs = db.OpenRecordset("F_distributionsliste", dbOpenDynaset)
If rs.RecordCount = 0 Then
    p = MsgBox("Der ikke nogen i den valgte distributionsliste, vælg en anden", vbOKOnly)
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
Else
    rs.MoveFirst
    With rs
    Do While Not .EOF
        mailliste = mailliste + .Fields(brugernavn)& "@frederiksberg.dk;"
        .MoveNext
    Loop
    End With
    Set rs = Nothing
    Set db = Nothing
End If
'slut

'hent sti
sti = Forms!FM_vedligehold_distributionslister!FM_distributionsliste!sti
'hent appnavn
appnavn = DLookup("[navn]", "DT_app", "[IDapp] = " & Forms!FM_vedligehold_distributionslister!appid)
'slut

Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.createitem(olmailitem)
 
With oEmailItem
    .Attachments.Add sti
    .to = mailliste
    .Subject = "Opdatering til " & appnavn
    .Body = "her er en opdatering til " & appnavn & vbCrLf & vbCrLf & "Husk at trække den vedhæftede fil ud på skrivebordet og sig jatak til at overskrive"
    .Display
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
Avatar billede terry Ekspert
12. februar 2015 - 10:37 #1
The error indicates you need the parameter.

So you could start by altering the query so no parameter is required. Just to eliminate that possibility.

Then if it works you need to alter your code so that you also include the parameter.
Example.
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qfd = dbs.QueryDefs("qryMyParameterQuery")

'Supply the parameter value
qdf.Parameters("EnterStartDate") = Date
qdf.Parameters("EnterEndDate") = Date + 7

'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()
Avatar billede kimsand Nybegynder
12. februar 2015 - 11:18 #2
Hey Terry :)

But that particular Query has no parameter.

I have a "fortløbende form" with a query as source. That Query is run when the form loads. The Query is dependant of 2 criteria which is chosen by the user.

The above form is inside the main form, which has a button to create an email. All of the above code is in the onclick for the button.

This means that the recordset has been created at the point when all the code is run.

BUT to counter that its a problem to open a recordset with parameters/criteria in VBA, i chose to run another Query which is based on the already established recordset, and it is that Query that i try and open in the code.

F_brugernavne:
SELECT F_distributionsliste.brugernavn
FROM F_distributionsliste;

This Query has no parameters/criteria, and only selects the brugernavn from the already created recordset.

Why is this still a problem then ?
Avatar billede kimsand Nybegynder
12. februar 2015 - 11:19 #3
Slightly adjused code.


'Check om der er nogen i den valgte distributionsliste
Set db = CurrentDb()
'DoCmd.OpenForm "FM_brugernavne", , , , , acWindowNormal
Set rs = db.OpenRecordset("F_brugernavne", dbOpenDynaset, dbReadOnly)
'Set rs = db.OpenRecordset("F_distributionsliste", dbOpenDynaset)
If rs.RecordCount = 0 Then
    p = MsgBox("Der ikke nogen i den valgte distributionsliste, vælg en anden", vbOKOnly)
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
Else
    With rs
    .MoveFirst
    mailliste = ""
    email = ""
    Do While Not .EOF
        email = .Fields(brugernavn) & "@frederiksberg.dk;"
        mailliste = mailliste & email
        .MoveNext
    Loop
    End With
    Set rs = Nothing
    Set db = Nothing
End If
'slut

'hent sti
sti = Forms!FM_vedligehold_distributionslister!FM_distributionsliste!sti
'hent appnavn
appnavn = DLookup("[navn]", "DT_app", "[IDapp] = " & Forms!FM_vedligehold_distributionslister!appid)
'slut

Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.createitem(olmailitem)
 
With oEmailItem
    .Attachments.Add sti
    .to = mailliste
    .Subject = "Opdatering til " & appnavn
    .Body = "her er en opdatering til " & appnavn & vbCrLf & vbCrLf & "Husk at trække den vedhæftede fil ud på skrivebordet og sig jatak til at overskrive"
    .Display
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
'DoCmd.Close acForm, "FM_brugernavne"
'DoCmd.SendObject , sti, , mailliste, , , "Opdatering til " & appnavn, "her er en opdatering til " & appnavn, True, False
End Sub
Avatar billede terry Ekspert
12. februar 2015 - 11:33 #4
any chance of seeing dB, or parts giving problem?

ekspertenATsanthell.dk
AT = @
Avatar billede terry Ekspert
12. februar 2015 - 11:37 #5
Try this
Place a breakpoint on line before
Set rs = db.OpenRecordset("F_brugernavne", dbOpenDynaset, dbReadOnly)

then run code.

Now when you hit the breakpoint go to the query F_distributionsliste (in All Access Objects) and run it.

What happens?
Avatar billede kimsand Nybegynder
12. februar 2015 - 11:44 #6
The same happens.

Ive tried to run both the F_distributionsliste and the F-brugernavne seperately and together and then F8 step through the code.
Avatar billede terry Ekspert
12. februar 2015 - 12:04 #7
The same happens.
You get prompted for parameter, or an error?

So it sounds as though you need to supply parameter.

If I understand you correctly, the parameter has been given when you open the form. When you try opening the query again in code, the parameter has to be given again because its another "copy" of the query you are opening, not the one in the form.
Avatar billede kimsand Nybegynder
12. februar 2015 - 13:18 #8
I get the same error..

The query gets the parameters from the main form (2 dropdown menus), so i can run the query and get the right recordset as long as the form is running, and ive filled both dropdowns.

SO the parameters is always supplied when the query is run. ALWAYS!
Avatar billede terry Ekspert
12. februar 2015 - 13:51 #9
is there any chance of seeing dB?
email above
Avatar billede kimsand Nybegynder
12. februar 2015 - 13:56 #10
yes i can send you this one as there is no sensitive data..

Just gotta figure out how to send the Tables along with it as they are links to a backend.
Avatar billede kimsand Nybegynder
12. februar 2015 - 14:20 #11
DB send :) ty youre awesome...

ive made a test, and even a query with no parameters at all works, not even a query not based on a query with parameters Works.

its a straight up query no fuzz, just 1 field... wont Work. Reeally strange
Avatar billede kimsand Nybegynder
12. februar 2015 - 14:22 #12
ahh.. not true... i just tried Again, and it worked with a single query.
Avatar billede kimsand Nybegynder
12. februar 2015 - 15:03 #13
hmm i could solve it by making a new tabel with the result of a query, and then just db.open that one :) gonna try that.
Avatar billede terry Ekspert
12. februar 2015 - 15:13 #14
I guess you could but that's not the best solution
Avatar billede terry Ekspert
12. februar 2015 - 15:21 #15
OK I have found win rar and now have dB open

Can you tell me hope you use program?
Avatar billede kimsand Nybegynder
12. februar 2015 - 15:27 #16
Well i didn't solve the programmatical problem, but i found another solution to the functionality..

Instead of opening a quety in vba that at some point in the chain had to have criteria/parameters, i just made a tablecreate query, which i run every time i press the button.

Then i open up the new table which is just no criteria/parameter at all at any point, and go through that one, to collect the email adresses :)

I don't think there is an answer to my programmatical problem, you have to some how define the criteria beforehand opening the query. I just couldnt get my head around what and how so i went searching for other solutions.

TY Terry for your effort.. throw me an answer, and ill reply
Avatar billede terry Ekspert
12. februar 2015 - 15:42 #17
I am sure there is an answer, just need to understand how your program is working.

I get no data when I open query F_brugernavne How do I get data to show.
Avatar billede terry Ekspert
12. februar 2015 - 15:48 #18
I would have liked to have found a solution but looks as though your happy with your own :-)
Avatar billede kimsand Nybegynder
13. februar 2015 - 08:18 #19
TY for your help terry :)

Somehow when i come in here and you pick up the thread, i find a solution, even if it doesnt come from you.

You are a tremendous help

Sincerely Kim Sandberg

p.s. you need to choose a distributionlist in the right dropdown, and populate it by pressing vælg knappen on some names on the left.

Then you can run F_brugernavne.

What i did was turning F_brugernavne into a create table query, and then open that in VBA, and it works quickly and like a charm.
Avatar billede terry Ekspert
13. februar 2015 - 11:55 #20
Thanks for the points Kim and the kind words :-)

Maybe I'll take a look at it again, as I am sure there is a solution rather than have to put data into new table
Avatar billede terry Ekspert
02. marts 2015 - 19:24 #21
I've made some changes so you don't need to use a temp table

Notice that the rs recordset gets its data from the forms recordset. Also, field name brugernavn is inside "" >> .Fields("brugernavn")




Dim mailliste As String
Dim appnavn As String
Dim sti As String
Dim oOutlook As Object
Dim oEmailItem As Object
Const olmailitem = 0
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim prm As DAO.Parameter
Dim x As Integer
Dim y As Integer


Set rs = Forms!FM_vedligehold_distributionslister!FM_distributionsliste.Form.Recordset  'db.OpenRecordset("F_brugernavne", dbOpenDynaset, dbReadOnly)
If rs.RecordCount = 0 Then
    p = MsgBox("Der ikke nogen i den valgte distributionsliste, vælg en anden", vbOKOnly)
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
Else
    With rs
    .MoveFirst
    mailliste = ""
    email = ""
    Do While Not .EOF
        mailliste = mailliste & .Fields("brugernavn") & "@frederiksberg.dk;"
        .MoveNext
    Loop
    End With
    Set rs = Nothing
    Set db = Nothing
End If
'slut

'hent sti
sti = Forms!FM_vedligehold_distributionslister!FM_distributionsliste!sti
'hent appnavn
appnavn = DLookup("[navn]", "DT_app", "[IDapp] = " & Forms!FM_vedligehold_distributionslister!appid)
'slut

Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.createitem(olmailitem)
 
With oEmailItem
    .Attachments.Add sti
    .to = mailliste
    .Subject = "Opdatering til " & appnavn
    .Body = "her er en opdatering til " & appnavn & vbCrLf & vbCrLf & "Husk at trække den vedhæftede fil ud på skrivebordet og sig jatak til at overskrive"
    .Display
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester