Visual Basic Not Waiting for Query to fully evaluate???

[Vince]

Hello all,

I am using Visual Basic to open a saved query and then save
information in the query to an array for later use. The problem is
that the same query shows different results when opened directly vs.
when opened by Visual Basic. It is as if Visual Basic is not letting
the query fully evaluate before processing records.

The query is a subtotal query that contains several criteria set up as
"where" in the group-by box. Most of the criteria are based on one
table, one criteria is based on a second joined table. When the query
is opened directly this last criteria is correctly evaluated and the
proper records are shown. When opened in VB it is as if this criteria
did not exist. The query otherwise shows correct information except
it includes records that should not be there based on the last
criteria.

Is there a way to force visual basic to wait until a query is fully
opened before executing code that uses the resulting records. Or is
there something else that I am missing.

Any help would be greatly appreciated.

Thanks,

Vince

Partial code Follows---------------------------------
Public aWork(10) as Integar

Sub LoadWorkArray(W orkQuery as string) 'WorkQuery = query name
Dim WorkLastRecord as Integar
Dim X as Integar

Set SLWork = New ADODB.Recordset

SLWork.Open WorkQuery, CurrentProject. Connection, adOpenKeyset,
adLockOptimisti c

WorkLastRecord = SLWork.RecordCo unt

ReDim aWork(WorkLastR ecord + 1)

With SLWork
.MoveFirst
For X = 1 To WorkLastRecord
aWork(X) = ![Attending Number]
.MoveNext
Next X
.Close
End With

End sub

[Mark]

I'm no expert but would it not be worth adding a line before you go to the
beginning of the recordset to go to the end of the recordset. I think this
would force the query to be fully executed before evaluating the values
returned from it.

Something like:

With SLWork
.MoveLast
.MoveFirst
For X = 1 To WorkLastRecord
aWork(X) = ![Attending Number]
.MoveNext
Next X
.Close
End With

Regards,

Mark

"Vince" <VA*********@ya hoo.comwrote in message
news:46******** *************** ***********@e10 g2000prf.google groups.com...

Hello all,

I am using Visual Basic to open a saved query and then save
information in the query to an array for later use. The problem is
that the same query shows different results when opened directly vs.
when opened by Visual Basic. It is as if Visual Basic is not letting
the query fully evaluate before processing records.

The query is a subtotal query that contains several criteria set up as
"where" in the group-by box. Most of the criteria are based on one
table, one criteria is based on a second joined table. When the query
is opened directly this last criteria is correctly evaluated and the
proper records are shown. When opened in VB it is as if this criteria
did not exist. The query otherwise shows correct information except
it includes records that should not be there based on the last
criteria.

Is there a way to force visual basic to wait until a query is fully
opened before executing code that uses the resulting records. Or is
there something else that I am missing.

Any help would be greatly appreciated.

Thanks,

Vince

Partial code Follows---------------------------------
Public aWork(10) as Integar

Sub LoadWorkArray(W orkQuery as string) 'WorkQuery = query name
Dim WorkLastRecord as Integar
Dim X as Integar

Set SLWork = New ADODB.Recordset

SLWork.Open WorkQuery, CurrentProject. Connection, adOpenKeyset,
adLockOptimisti c

WorkLastRecord = SLWork.RecordCo unt

ReDim aWork(WorkLastR ecord + 1)

With SLWork
.MoveFirst
For X = 1 To WorkLastRecord
aWork(X) = ![Attending Number]
.MoveNext
Next X
.Close
End With

End sub

[Vince]

On Dec 28, 6:14*pm, "Mark" <mreed1...@btin ternet.comwrote :

I'm no expert but would it not be worth adding a line before you go to the
beginning of the recordset to go to the end of the recordset. I think this
would force the query to be fully executed before evaluating the values
returned from it.

Something like:

With SLWork
* * .MoveLast
* * .MoveFirst
* * For X = 1 To WorkLastRecord
* * * * aWork(X) = ![Attending Number]
* * * *.MoveNext
* * Next X
* * .Close
*End With

Regards,

Mark

"Vince" <VArgenzi...@ya hoo.comwrote in message

news:46******** *************** ***********@e10 g2000prf.google groups.com...

Hello all,

I am using Visual Basic to open a saved query and then save
information in the query to an array for later use. *The problem is
that the same query shows different results when opened directly vs.
when opened by Visual Basic. *It is as if Visual Basic is not letting
the query fully evaluate before processing records.

The query is a subtotal query that contains several criteria set up as
"where" in the group-by box. *Most of the criteria are based on one
table, one criteria is based on a second joined table. *When the query
is opened directly this last criteria is correctly evaluated and the
proper records are shown. *When opened in VB it is as if this criteria
did not exist. *The query otherwise shows correct information except
it includes records that should not be there based on the last
criteria.

Is there a way to force visual basic to wait until a query is fully
opened before executing code that uses the resulting records. *Or is
there something else that I am missing.

Any help would be greatly appreciated.

Thanks,

Vince

Partial code Follows---------------------------------
Public aWork(10) as Integar

Sub LoadWorkArray(W orkQuery as string) *'WorkQuery = query name
Dim WorkLastRecord as Integar
Dim X as Integar

Set SLWork = New ADODB.Recordset

SLWork.Open WorkQuery, CurrentProject. Connection, adOpenKeyset,
adLockOptimisti c

WorkLastRecord = SLWork.RecordCo unt

ReDim aWork(WorkLastR ecord + 1)

With SLWork
* *.MoveFirst
* *For X = 1 To WorkLastRecord
* * * *aWork(X) = ![Attending Number]
* * * .MoveNext
* *Next X
* *.Close
End With

End sub- Hide quoted text -

- Show quoted text -

Hello Lyle,

You got it with using an "*" vs "%". The query was using a Not Like
"*string*" expression. Changed it to use % instead and now it works.
You do not know how long I have been looking at everything but the
string. While the query opens normally from Access the ADODB
apparently totally ignores an expression that uses an *.

Thanks for everybody's help.

Vince

[David W. Fenton]

Vince <VA*********@ya hoo.comwrote in
news:38******** *************** ***********@e4g 2000hsg.googleg roups.com
:

You got it with using an "*" vs "%". The query was using a Not
Like "*string*" expression. Changed it to use % instead and now
it works. You do not know how long I have been looking at
everything but the string. While the query opens normally from
Access the ADODB apparently totally ignores an expression that
uses an *.

Why are you using ADO? Makes no sense to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Vince]

On Dec 28 2007, 8:23*pm, "David W. Fenton"
<XXXuse...@dfen ton.com.invalid wrote:

Vince <VArgenzi...@ya hoo.comwrote innews:38****** *************** *************@e 4g2000hsg.googl egroups.com
:

You got it with using an "*" vs "%". *The query was using a Not
Like "*string*" expression. *Changed it to use % instead and now
it works. You do not know how long I have been looking at
everything but the string. *While the query opens normally from
Access the ADODB apparently totally ignores an expression that
uses an *.

Why are you using ADO? Makes no sense to me.

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/

Hi David,

It was a way of opening a query inside VB to populate an array. I
also tried opening the query as the DAO recordset type of object and
got the same results. I had done this in a number of other queries
but this is the first one I had a problem with. I would be open to a
better approach.

Thanks,

Vince

[Vince]

On Jan 1, 4:30*pm, Vince <VArgenzi...@ya hoo.comwrote:

On Dec 28 2007, 8:23*pm, "David W. Fenton"

<XXXuse...@dfen ton.com.invalid wrote:

Vince <VArgenzi...@ya hoo.comwrote innews:38****** *************** *************@e 4g2000hsg.googl egroups.com
:

You got it with using an "*" vs "%". *The query was using a Not
Like "*string*" expression. *Changed it to use % instead and now
it works. You do not know how long I have been looking at
everything but the string. *While the query opens normally from
Access the ADODB apparently totally ignores an expression that
uses an *.

Why are you using ADO? Makes no sense to me.

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/

Hi David,

It was a way of opening a query inside VB to populate an array. *I
also tried opening the query as the DAO recordset type of object and
got the same results. *I had done this in a number of other queries
but this is the first one I had a problem with. *I would be open to a
better approach.

Thanks,

Vince- Hide quoted text -

- Show quoted text -

Excuse me, I meant to say I had tried opening the recordset as its
default object type and got the same results.

[David W. Fenton]

Vince <VA*********@ya hoo.comwrote in
news:58******** *************** ***********@e50 g2000hsh.google groups.co
m:

Excuse me, I meant to say I had tried opening the recordset as
its default object type and got the same results.

The default object type depends on what references your database
has. Which references you get by default depends on which version of
Access you created the MDB with.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/