Requery a Query (1 Viewer)

[ddrew]

How do I requery a query, without opening it and seeing it on screen.

 

[namliam]

What do you mean exactly??

You dont need to requery the query if it is not open... It must be open in the form? or as a seperate window.. otherwize requery-ing is not needed.

Me.Requery
or
Formname.Requery
will requery your form.

 

[ddrew]

I was trying to do as you suggested from a previous thread:

The query would be simple... much like you can make in the query designer
Select *
from yourTable
where DateAdd("M",10, [StartTime]) <= Now()

I guess your suggesting a create a hidden form!

 

[namliam]

?? Where did you get that quote??

LOL

This is a followup to another question you had???
What thread was that?
How does this query relate to a requery?

If a query is closed, i.e. not on a form or visible or open in code someplace it is automaticaly "requeried" when it is opened.

 

[ddrew]

OK , im not making myself clear. I have a continuous form that has a bunch of tinmings on it, one of which is a time reported. I want to be able to requery every second to check to see if any of the records had been open for over 10 minutes, (The database is emptied at the end of each day so there are only about 30 records at time in there). I dont want to do it on the main form as that messes with data input, when I asked previously about this you gave me a soloution, (as quoted) I misunderstood thinking that I needed to create a query to look for those times over 10 minutes, clearly Im not understanding

 

[namliam]

If you have followup questions on unclear answers I suggest you stick to that thread... then we can all see that one thing has to do with another.

A requery of your form (ie. me.Requery) will work, but will likely mess with your form anyway.
I think you should open your query in code, without actually touching the form to see if your criteria are met.

 

[ddrew]

I think you should open your query in code, without actually touching the form to see if your criteria are met.

Ah yes, thats what I 'think' I was getting at, Im not sure how to do that! I know how to write basic code, but not how to build a query in code. I think I assumed I had to build a query then run the query from a DoCmd. Whereas you mean actually write the query in the code

 

[namliam]

Currentdb.Openrecordset will open a query in code and in memory (i.e. hidden from the user)

Type that into the Module and hit F1 to read all about it... should atleast get you started. Post back more questions here.

 

[ddrew]

OK I created a Module called 10Mins
And put this code in:

 CurrentDB.Openrecordset Over10Mins, , , AsRecordset

I ran it but it didnt seem to do much1

 

[namliam]

lol...

Well... where are you loading this to? No where right??

Dim rs as DAO.Recordset  ' ***
set rs = Currentdb.Openrecordset ("Over10Mins")
Do while not rs.eof
    debug.print rs!YourColumnname
    rs.movenext
loop

Something like above will loop your query and show every <yourColumnName> in the Debug window (CTRL+G if it is not visible)

***
If you get an error on this line, you need to go in the menu to Tools>References
Find an entry called "Microsoft DAO ...." check the box in front of it and click OK.

 

[ddrew]

lol...

Dim rs as DAO.Recordset  ' ***
set rs = Currentdb.Openrecordset ("Over10Mins")
Do while not rs.eof
    debug.print rs!YourColumnname
    rs.movenext
loop

Im getting a Compile Error on the word "set", it says Invalid Outside Prodedure

 

[namliam]

If you get an error on this line, you need to go in the menu to Tools>References
Find an entry called "Microsoft DAO ...." check the box in front of it and click OK.

Try that....

 

[ddrew]

Yeah I did that already and the its checked!

 

[namliam]

Do you have the query saved as "Over10Mins" in your database?

 

[ddrew]

Yes I made a query called "Over10Mins and it has one coloum in it "Time_Reported"

 

[ddrew]

I assumed where you put rs!YourColumnname I had to put in the name of the coloum in the DB

 

[namliam]

Did you put it in a sub???

i.e.

Sub TestingNamliamsCode
    Dim rs as DAO.Recordset  ' ***
    set rs = Currentdb.Openrecordset ("Over10Mins")
    Do while not rs.eof
        debug.print rs!YourColumnname
        rs.movenext
    loop
end sub

 

[namliam]

I assumed where you put rs!YourColumnname I had to put in the name of the coloum in the DB

The column as it is in the query.... so yes probably the column name in the db/table.

 

[ddrew]

[

Sub TestingNamliamsCode
    Dim rs as DAO.Recordset  ' ***
    set rs = Currentdb.Openrecordset ("Over10Mins")
    Do while not rs.eof
        debug.print rs!YourColumnname
        rs.movenext
    loop
end sub

How would I go about doing this so that when a buton was pressed a message box apeared showing those entries that met the criteria?

 

[namliam]

Instead of printing it to the debug window store it in a text string
Dim myMessage as string

Then instead of debug
myMessage = myMessage & rs!YourColumnname & vbNewline

Then after the Loop
Msgbox myMessage