Run queries with VBA (1 Viewer)

[Ironis]

I want to run queries in VBA. The queries are used for calculations, but I can't find the right command to run a query. The queries are in the DB. Or do I have to type the queries in the code?
Can ne1 help plz?

 

[Pat Hartman]

You can use the Execute method to run action queries. If you want to process a recordset in code, use the OpenRecordset Method.

 

[Ironis]

The queries are used for some calculation like:
if Query1 + Query2 + txtField > 6 then
.....

But I don't know how to call these queries using the code.
It might be an option to put the queries in the code, which I did, but now is the problem the queries need to be run. Is there some kind of SQL command, which saves the query outcome? Maybe something like:
qrySwim9AML = SQL("SELECT FROM .......;")

 

[simongallop]

Docmd.OpenQuery "QueryName" will run the query.

 

[Mile-O]

Also, if using action queries when running DoCmd.OpenQuery <queryname>

then, to avoid the message box that states you are "ABOUT TO RUN A DELETE, APPEND, WHATEVER QUERY" use this:

With DoCmd
     .SetWarnings False
     .OpenQuery <queryname>
     .SetWarnings True
End With

 

[Ironis]

I tried your code, a bit tweaked though, but theres still an error..

Here's the code:

Public Sub Planning()
    
    DoCmd.SetWarnings False
    If cmbResProgID = 2 Or 3 Then
        Dim QryMore6 As QueryDef
        
        QryMore6 = DoCmd.OpenQuery(qryCodeSwim9AML) + DoCmd.OpenQuery(qryCodeSwim9AMT)
        
        If QryMore6 > 6 Then
            QueryEMore6       'Private sub with calculations'
        Else
            QueryELess6       'Private sub with calculations'
        End If
    Else
    End If

    DoCmd.SetWarnings True

End Sub

Heres the error on "qryCodeSwim9AML"

Compile error:
Variable not defined

When I define the queries like this:

Dim qryCodeSwim9AML, qryCodeSwim9AMT

I'm getting another error on ".OpenQuery"
Compile Error:
Expectred Function or variable

I tried to remove the QueryDef property, but thatr doesn't work.
The code should read the queries from the DB, butr when I try in a new private sub, the code doesn't find the queries as well (in the Ctrl-Space menu, and when its run..)

 

[simongallop]

You can't add queries together like that. Queries can return one record or a million. how are you to add a million records against 30 records? Either you select a record and add it to another record, or you select the sum of a field and add it to another.

Can you carry out this action manualy? If so what do you do?

If not then EXACTLY what are you trying to do? ie let us know which record you are using etc. Otherwise I doubt if there is much we can do

 

[Ironis]

Oke, this is the second type I'm typing this, but well, waht the heck

Oke heres how it should work:

People fill in a reservation on the form frmReservation. On the after_update event of the date field, the code should run, and check wether the program isn't already full.

This is why i want to use the queries. The outcome of eacht query is a calculation, on how many people have reservated for a certain program on a certain date. So the outcome is only one field, one record, a number between null and 30

I figured I might use the queries in a piece of code, wich uses the queries for calculations to determine wether a program is full or not.

example:
People want to book for program A. So now the system should check if it's possible.

The system is checking first if program B doesn't have too many bookings. Becouse there are 5 dolphins, and for program A is 1 dolphin per 6 people, and for program B there are 2 dolphins needed for 6 people.
So when more then 6 people have booked for program B, there can be only 6 for program A.
If there are less then 6 people for program B, then there can be 18 people for program A.

The system is devided in 'local' programs and 'tourist' programs. This is becouse of the difference in price for tourists and for local people.

So i made the following statement:

If programBlocal + ProgramBtourist > 6 then 'there shouldnt be more then 6 
     people for program A'
CheckProgramAMore6    'Program A can only have 6 or less reservations'
else
CheckProgramALess6     'Program A can have 0 - 18 reservations'
end if

So when there are more then 6 for program B there should be 6 or less for program A:

Private Sub CheckProgramAMore6()

If programAlocal + ProgramATourist > 6 then 'theres an arror, 
      this is not possible'
MsgBox "This reservation is not possible. There are x program A en x program b. 
       Please try another time / date"
else 'reservation is verified'
docmd.save
end if
end sub

The other one:

Private Sub CheckProgramALess6()

If programAlocal + ProgramATourist > 18 then 'theres an arror,
       the maximum is 18'
MsgBox "This reservation is not possible. There are x program A en x program b. 
       Please try another time / date"
else 'reservation is verified'
docmd.save
end if
end sub

This is how it should work. This is only the programs with ID 2 and 3. The rest is coming in an elseif statement

(If resProgID = 2 or 3 then
.......
Elseif resProgID = 4 or 5 then
....
etc)

So this is where I want to use the queries for. Theres a total in the queries, 1 record with 1 field = one value.
I figured it should be possible to add the outcome of multiple queries.

I hope this is enough information..

Manually you could do this by executing the query and writing down the outcome. Then run the other query and also write that number down, add those to eachother and then the rest that has to be done..

 

[WayneRyan]

Ironis:

I can't quite follow your logic, but if the user is looking at an
Access form Then:

As they fill in the fields for the desired activities such as
DATE and TYPE then on an After-Update event use the
DCOUNT or DSUM functions to get your desired numbers.

Look into these functions and let us know.

hth,
Wayne

 

[Ironis]

k, i will have a look at it. It will be after tomorrow, becouse of a busy cruiseday. I will look into it at tuesday and will let you know. I don't know these functions, so I hope the help-file provides enough info on it.

Thanks

 

[Ironis]

Oke, I have used the Dsum function, and it works, but the msgbox doensn't work properly yet.
I will find out how to fix that, becouse it is probably a n00by thingy whats wrong with that..

Thanks for all the help you guys!

Dennis (Ironis)