ADO DataCombo timing??? 
Author Message
 ADO DataCombo timing???

I have a form with an AdoDC control on it pointing to a Access97 db
(Jet3.51) with a SQL query in the recordsource property looking to see if
there are any records in a given table that have a blank in a field called
OrderPosted.

In some books I've read, it seemed that once the form was loaded, I could
check and see if the RecordCount property of the RecordSet of the AdoDC was
greater than zero, but I had to use an AdoDC.Refresh in order to do so.
Also, I had an access to the same database (Via Dim Database, recordset,
OpenTable) in the Form()Load proc down below this refresh.  The refresh
worked, but the Dim DB access below failed with DB in use.  I moved the
Refresh code into the Form()Activate proc and everything worked fine.  So I
guess I have a couple of questions.

1.  When does an AdoDC control actually execute the SQL query in the
recordsource property (actually getting data).

2. Why did I have to refresh the AdoDC?

3. Why did it work OK in Activate, but not Form()Load?

Thanks,

Hank



Wed, 31 Mar 2004 10:04:34 GMT  
 ADO DataCombo timing???
Hi Hank,

I'm not sure why you had to used the Refesh method unless you or someone else
are in some way modifying the underlying data and thus would want to see the
most current data.  

A more efficient method for determining if the number of records in a RecordSet
(RS) Object is greater than zero is the use the the EOF and BOF properties of
the RS Object.  

If the RS is empty (zero records) then BOTH the EOF & the BOF properties are
TRUE.  This should save a tremendous amount ot time and code.  Let me know if
it's viable solution to your question.

Don't Forget the ADO DataControl is a compromise between ease of use and
power/efficiency.  Hope I've helped some.

Terence



Wed, 31 Mar 2004 13:00:53 GMT  
 ADO DataCombo timing???
Terrence;

Thanks for the reply.  No, no one else would be modifying the data (local
single user db) and I don't
care about any updated data, even though there shouldn't be any at this
point.  Let me try and give
you a quick simple explanation of what I was trying to do with this.

In my db, I have an Inventory table.  When merchandise comes in a shipment,
the user would fill in
the order summary (Total, Freight, Product amt etc) on the frmInvOrd and
then go to the detail item
screen to key details on each item via the frmInvOrdDetails screen.  I don't
want to post the items
into Inventory until they are done and the total of items matches the
summary data they keyed in.  If
they elect to finish the order later, then I have a blank Post flag on the
InvOrd record (which won't
get set until whole order is posted to Inventory).

So if they come back in to do a new order, I wanted to check the InvOrd
table to see if any recs
still had blank Post flag and warn them to finish the other orders.  That
was the reason for the AdoDC
control to Select any records with blank Post and check to see if any recs
were returned.  I wanted to
do this right when the form loads, but without the Refresh method, I get a
"Run Time error 91,
Object variable or With Block variable not set".  Also, I have to place this
code in the Form_Activate
section.  When I place the same code at the beginning of the Form_Load, I
get "Run Time 364,
Object was unloaded" and the Debug lands me in my first main menu form for
"menuInvOrd" Click
procedure, like it was trying to load it again, but it shouldn't be back
there, I would think.

Below I have copied the two sections from my frmInvOrd.  The DIM database is
the same that I
use in my ConnectString for the Ado Data Combo, but a different table
(Inventory) is in the SQL.

Private Sub Form_Activate()
    adoChkPost.Refresh
    If adoChkPost.Recordset.RecordCount > 0 Then
        Unload frmInvOrd
        Load frmInvOrdCont
        frmInvOrdCont.Show
        MsgBox "You must finish previous orders!"
        Exit Sub
    End If
End Sub

Private Sub Form_Load()
    cmbShipVia.AddItem "UPS"
    cmbShipVia.AddItem "RPS"
    cmbShipVia.AddItem "Regular Mail"

    CmbInvOrdSup.AddItem "** Add Supplier **"
    Dim inventory As Database
    Set inventory = OpenDatabase("c:\hank\devnew\carlot\invntry.mdb")
    Dim supli As Table
    Set supli = inventory.OpenTable("suppliers")
    supli.MoveFirst
SupliLoop:
    If supli.EOF Then GoTo SupliCont
    CmbInvOrdSup.AddItem (supli!SupplierName)
    supli.MoveNext
    GoTo SupliLoop
SupliCont:
End Sub

I know this is a lot of info, but for me, it is hard to debug a problem for
someone if I can't get a
good idea of what they are really trying to do.  I really appreciate your
help.  I can only hope
that as I get better, I can, in turn, help someone else out here. Thanks.

Hank


Quote:
> Hi Hank,

> I'm not sure why you had to used the Refesh method unless you or someone
else
> are in some way modifying the underlying data and thus would want to see
the
> most current data.

> A more efficient method for determining if the number of records in a
RecordSet
> (RS) Object is greater than zero is the use the the EOF and BOF properties
of
> the RS Object.

> If the RS is empty (zero records) then BOTH the EOF & the BOF properties
are
> TRUE.  This should save a tremendous amount ot time and code.  Let me know
if
> it's viable solution to your question.

> Don't Forget the ADO DataControl is a compromise between ease of use and
> power/efficiency.  Hope I've helped some.

> Terence



Wed, 31 Mar 2004 17:45:31 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Can UserForms work with ADO DataCombo?

2. ADO DataCombo

3. Navigating through Recordsets using DataCombo (ADO)

4. ADO DataCombo VB6.0

5. ADO Recordsets and DataCombos

6. Moving ADO dataset with datacombo

7. ADO + DataCombo

8. VB, ADO and datacombo controls

9. ADO recordset position and datacombo ctrl.

10. ADO & DataCombo Filtered Query - Help please

11. ADO + DataCombo

12. ADO: DataList and DataCombo and Datamembers

 

 
Powered by phpBB® Forum Software