querydef problem

[tboerner]

In the following code, I'm getting some inconsistent behavior. Sometimes, the Me.RecordSource says the qryOrganization is not found. This only happens sometimes. It appears to be a timing issue. I put in a loop between the 2 lines of code, that does nothing. This alleviates the problem, but does anyone have a better way?!

' Create QueryDef.
Set qryobjOrganization = appAccess.CreateQueryDef("qryOrganization", strSQL)

Me.RecordSource = "qryOrganization"

 

[JimHorton]

Have you tried querydefs.refresh for the currentdatabase object of appAccess' queydefs collection?
--Jim

 

[tboerner]

Like this?

' Create QueryDef.
Set qryobjOrganization = appAccess.CreateQueryDef("qryOrganization", strSQL)

appAccess.Querydef.refresh

Me.RecordSource = "qryOrganization"

 

[tboerner]

I did try what I just suggested. No help.

 

[Sawatzky]

dear tboerner,

just give it go, I am not sure whether it helps:

Set qryobjOrganization = appAccess.currentdb.CreateQueryDef("qryOrganization", strSQL)

and/or this:

me.recordsource=appAccess.currentdb.CreateQueryDef("qryOrganization", strSQL).name

I am very keen to know if this helps

regards astrid

 

[tboerner]

Astrid,

I did try:

Me.RecordSource = appAccess.CreateQueryDef("qryOrganization", strSQL).name

It doesn't solve the problem. There still seems to be a delay between when the Query is ready for use and when it is being assigned to the recordsource.

I did this and it works every time:

Set qryobjOrganization = appAccess.CreateQueryDef("qryOrganization", strSQL)

Dim a
a = 0
While a <= 10000000
a = a + 1
Wend

Me.RecordSource = &quot;qryOrganization&quot;

 

[Sawatzky]

try this:
Set qryobjOrganization = appAccess.CreateQueryDef(&quot;qryOrganization&quot;, strSQL)

' qryobjorganization.movelast uncomment this, if the followig line does not solve

while qryobjorganization.stillexecuting
wend

Me.RecordSource = &quot;qryOrganization&quot;

regards

 

[tboerner]

Astrid,

Neither of those worked. Movelast isn't available for a querydef. 'StillExecuting' appears as a possible method when you type 'qryobjOrganization', but when you run it, it says 'Operation is not supported.' That looked like a good idea.

I'm satisfied with my workaround. It has never failed, and if it does, they can just run again.

Thanks for your ideas.

 

[Sawatzky]

dear tboerner,
another try:

Dim qryobjorganization As QueryDef

Set qryobjorganization = Access.Application.CurrentDb.CreateQueryDef(&quot;qryOrganization&quot;, strsql)

While CurrentProject.Connection.State = adStateExecuting
Wend

Me.RecordSource = &quot;qryOrganization&quot;

End Sub

regards astrid

(I do not like when access laughs at me )