Refresh speed

[RichardH]

I'm having a problem opening a form that relies on a query as a control source. I keep getting the error saying that the query doesn't exist, so I wrote the following code to see if it actually does...

For Each Qry In thisdb.AllQueries
If Qry.Name = "qryResults" Then
If Qry.IsLoaded Then
DoCmd.Close acQuery, "qryResults", acSaveNo
End If
MsgBox "found it"
End If
Next

DoCmd.OpenForm "frmViewResults2"


It brings up the msgbox so it definately finds it, however the form still won't open. Is there a way I can refresh things so access realises the query exists and opens the form?

thanks for any help

 

[ghudson]

Why not make the control source the SQL instead of the stored query? You can easily copy the SQL of your query and paste it as the control source of your form.

 

[RichardH]

Thanks for the reply.
I can't use the SQL because the query is built up dynamically depending on what they choose on a previous screen.
If I go in and manually look at it, it's fine, but it doesn't seem to see it during runtime.

 

[ghudson]

Thanks for the reply.
I can't use the SQL because the query is built up dynamically depending on what they choose on a previous screen.

Why not? Just reference the values [fields] in that form to your other forms SQL.

 

[RichardH]

I would but they're multi-select list boxes. No matter how I do it I'm still confused as to why this code would work in debug but returns an error in runtime. It definately finds the form, and when it opens it during debug it requeries it on Form Load. I've tried putting a delay in to see if it's a refresh speed issue but it still says it can't find the form in runtime. It's getting really frustrating.

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
If obj.Name = "frmViewResults" Then
If obj.IsLoaded = True Then
DoCmd.Close acForm, "frmViewResults"
End If
End If
Next obj

DoCmd.Close acForm, "frmViewMain", acSaveNo
DoCmd.OpenForm "frmViewResults"

 

[ghudson]

Try storing the values as a global string.

Global gsValue1 As String
Global gsValue2 As String

gsValue1 = "whatever1"
gsValue2 = "whatever2"

 

[RichardH]

Again, I would but it displays them as continuous forms so I wouldn't know how many to create.
I appreciate your help on this, and I apologise if my database seems a bit wierd but it's my first attempt so I'm learning what not to do as I go along. Once I have delivered this as a working v1.0 I get to spend some time doing it right. Just keeps my boss quiet for a bit.
Does anyone know why it works in debug but not runtime?