kjøre Query med kriterier fra en modul (VBA)

Hvad vil du køre den imod ? En tabel ?

Og i vilket program ? Access / Excel / Word eller ???

Jeg vil kjøre en access-query (mot en access-tabell) fra en access-modul med 2 ulike kriterier fra variabler i modulen. OK?

Hvis du laver en funktion som afleverer den ønske værdi af en variabel, kan du bruge funtionen son kriterium for det pågældende felt.

Function MinFunction()
        MinFunction = MinVariabel
End Function

Well I could wait to see if the points we increased but as I'm not fixed on points then you can try this

You may need a reference to DAO to do this


Dim dB as Database
Dim qdf As QueryDef


On Error GoTo PROC_ERR
   
    Set db = CurrentDb
    Set qdf = db.QueryDefs("YourQuery")
   
    qdf.Parameters("P1") = 100
    qdf.Parameters("P2") = "Apples"
   
    qdf.Execute
       
    Set qdf = Nothing

Jeg uddyber lige fynbohans :)

foresp Q1: SELECT Postnr.* FROM Postnr WHERE PostNavn)=minvariabel();

VBA modul:

Sub kørForesp()
  DoCmd.OpenQuery "q1"
End Sub

Function minVariabel() As String
  minVariabel = "kolding"
End Function

Niels

.

.

Terry;

Dim dB As dao.Database
Dim qdf As dao.QueryDef

Const cstrN As String = "Test2"

 
    Set dB = CurrentDb
    Set qdf = dB.QueryDefs(cstrN)

    'qdf.Parameters("P1") = 100
    'qdf.Parameters("P2") = 11

     
    qdf.Execute
       
    Set qdf = Nothing

Denne er OK!

men når jeg aktiverer qdf.parameters...  får jeg "run-time error 3265 Angitt element finnes ikke i samlingen" . Er P1 = feltnavn?

Jeg vil at queryen skal kjøre med feltet PROID = 100 og INDID = 11.



 
    'qdf.Parameters("P1") = 100
    'qdf.Parameters("P2") = 11

have you created two parameters not just [P1] and [P2]!

Right click in the area wheher the tables are in your query and then choose parameters from the menu, then create two parameters.

In SQL view you will see someting like this.
PARAMETERS P1 Long, P2 Long;
SELECT .....

.

hows it going trymore?

??

sorry, i've been too busy, but i will have a look at the problem now!!

OK

and now thsi question has been open a whole month, lets get it closed!

OK!!!!!!!!!, my friend!!!

(the problem came to an end last week)

It was not necessary to right click in my action-query to define some parameters. I just wrote [parameters].[x] and [parameters].[y] in the criteria-field of current interest in the query. The values in x & y are on forehand collected from a form.

Solution:

temp_x = [Forms]![formA]![fieldA]
temp_y = [Forms]![formA]![fieldB]

Dim dB As dao.Database
Dim qdf As dao.QueryDef

Const cstrN As String = "queryA"

    Set dB = CurrentDb
    Set qdf = dB.QueryDefs(cstrN)
   
   
    qdf.Parameters("parameters.x") = temp_x
    qdf.Parameters("parameters.y") = temp_y
   
    qdf.Execute
       
    Set qdf = Nothing

Thanks for giving me tip of great value which lead me to work out the challange of my problem. I hope you still will give me a reply next time I initiate a new approach to a problem!  The points are yours!

Have a nice day

trymore

you need to accept then :o)
glad to hear yo got it working too.

.

Hei Terry.

Jeg klikker på "selv angive point fordeling" og legger inn point på deg og trykker accept. Er ikke det nok ?????

maynard

you need to mark the name of the person you are giving points to

I think eksperten has a little problem here :o)

Well I've tried several times to click on "selv angive point fordelinen, then stored the points in the field related to your name and then pushed the button to ACCEPTER

Well, I'll try to send an e-mail to eksperten!

trymore

Eller måske har Terry nået sit maximum af point her på Eksperten :o)

Niels

Good one there Niels :o)

trymore, I'll contact admin as this seems to be a general problem they have!

I think nih might be right - Terry has absoutely reached his maximum ;-)

Nah try making et new question with the correct points named "points to Terry 458943" - If you can´t close this one yourself (by anwering and accepting yourself) at least I can do it and return your points.

/Nikolaj /co-admin

anwering = answering

well if yo werent so close to my tail Nikolaj, I would just say forget the points :o)

.

and?

A mail has been forwarded few days ago to coadmin about my problem to close this case. I've increased the value point to 60 and tried to push the accepter-button again, and several times again tried the selv-angive-point-fordeling routine to accept. A new e-mail has been sendt to coadmin today. Well, I have opened a new question "Kjøre query med kriterier fra en modul VBA II"". Give it an answer terry and i will try to accept it. OK?

WohW!!! Well this case seems to be closed know! This was not the best KISS-routine i've seen for accepting an answer but it seems to be OK! (KISS=Keep It Stupid and Simple) Thank's terry.

thanks too :o)
Hope this problem with accepting answers gets solved soon!

Actually it´s not a good idea to write mails to coadmins - Use the "Anmeld misbrug" in the "funktioner" box out to the left. Enter your own name and the problem - It´s much easier for us.

When it comes to this specific problem it´s a wellknown bug and users can wait until it is ressolved -  Well don´t get optimistic this bug has been there for ages.

/Nikolaj