Here's My Problem... "Refresh ADO" (RESOLVED)

[epixelman]

I use VB6 on WinXP - Using Access97 mdb.

SEE SCREENSHOT BELOW!

Here is what I have run into. I have learned ADO (code only - no controls) fairly quickly, but I have run into a snag...

1) I open a db connection-recordset on Form_Load
2) It fills in a datagrid
3) All my Test buttons (plain 3D grey ones) all work fine.

GREAT UP TO NOW!

THEN I ADDED A FILTER! (not so good)

On the right "LEDGER TOTALS" I filter totals from the db table (not the datagrid) to get my amounts... works fine!

BUT...

When I try to Add a record I get the ADO error.

Here is the code for the filter...

Dim Inc As Integer, IncBal As Currency
IncBal = "0.00"
DoEvents
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "SELECT * FROM Register", db
adoPrimaryRS.Filter = "Type = 'Income'"
For Inc = 1 To adoPrimaryRS.RecordCount
    adoPrimaryRS.AbsolutePosition = Inc
    IncBal = IncBal + adoPrimaryRS.Fields("Credit").Value
Next Inc
lbIncome.Caption = Format(IncBal, "0.00")
adoPrimaryRS.Filter = adFilterNone

I have 3 of these block in my procedure. The reason I know this is the problem is because I disabled the procedure and I could Add a record fine.

Then to prove my point I enabled the procedure again. But before clicking the Add button, I clicked my Refresh button... WORKS FINE!

This is what I need. I need a way to refresh the main data connection from with in the above procedure.

I can not use the "Refresh" button because it will undo any sorts, filters, finds and column widths that were edited.

So basically I have to give my data connection back to my app on the main form.

Any Ideas?

Thanks

The Screenshot below has been made smaller to save size. Grey buttons on top are for testing purposes.

[epixelman]

oh... and by the way.

The above procedure is called everytime I change or update a record.

I need to refresh the dataconnection withour refreshing the datagrid.

[MartinLiss]

Please don't put "unresolved" in the subject. It takes away from the ability to distinguish resolved threads from unresolved ones.

[epixelman]

Oops..... sorry

[randem]

epixelman


I am not getting it, what wrong with

"SELECT Sum(Credit) FROM Register"

and getting the total from the SumOfCredit field to get the total? What you are doing seems like a lot of code to get the same results.

[epixelman]

Not sure what your asking?
If you think I can do it better then could you post the entire code block of what you mean?

I'm not having a problem with getting my totals, but with adding records after this procedure has been run.

This procedure (code above) is meant only to read the data from the table and display it in the Ledger labels.

Do I really need to RE-OPEN the db just to read?

The REGISTER table is already opened. It is opened up when the Form_Load even happens and it is never closed until the app is completly closed ("End"). BTW my db is a Data SHAPE recordset.

If you look at the screenshot (above), you will notice that the entire REGISTER table is opened and loaded into a datagrid. But the Ledger Totals are updated everytime I make a change to a record.

Am I going about this all wrong?

[brucevde]

Sorry, I am not totally clear as to what you are doing.

You say you open a recordset for the grid in Form_Load. I am assuming you are setting this to your adoPrimaryRs variable.

The following line executes when you need to do the Filter. It uses the adoPrimaryRS variable as well

adoPrimaryRS.Open "SELECT * FROM Register", db

However, it opens a ReadOnly cursor (the default) and overwrites the recordset opened in Form_Load (again just an assumption).

[epixelman]

Yes...

When I first start the app, it opens the database and loads the entire REGISTER table into the datagrid.

Private Sub Form_Load()
 
'set the database
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "\moneymatters.mdb;"
 
'set the recordset
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "SHAPE {select RecNbr,Date,Month,Type,Acct,Cleared,TransID,Category,Description,Debit,Credit,Gross,SplitAmt " & _
    "from Register Order by RecNbr} AS ParentCMD APPEND " & _
    "({select RecNbr,Date,Month,Type,Acct,Cleared,TransID,Category,Description,Debit,Credit,Gross,SplitAmt " & _
    "FROM Register ORDER BY RecNbr} AS ChildCMD RELATE RecNbr TO RecNbr) " & _
    "AS ChildCMD", db, adOpenDynamic, adLockOptimistic
  
'Bind controls to recordset
'set the datasource of these controls to the database
Set TxtRecNbr.DataSource = adoPrimaryRS
Set TxtRegDate.DataSource = adoPrimaryRS
Set cbRegMonth.DataSource = adoPrimaryRS
  
'Bind recordset to datagrid
Set grdDataGrid.DataSource = adoPrimaryRS.DataSource
 
'now set the column widths
SetRegGridWidth
 
End Sub

The databse is never closed until I exit the app.

I can Add, del, update etc... WORSK FINE.

But I added the procedure (below) to just read the "already" opened db into the lables on the LEDGER TOTALS section of app. (see scrnshot)

Public Sub CalcLedger()
 
'lbIncome
Dim Inc As Integer, IncBal As Currency
IncBal = "0.00"
DoEvents
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "SELECT * FROM Register", db
adoPrimaryRS.Filter = "Type = 'Income'"
For Inc = 1 To adoPrimaryRS.RecordCount
    adoPrimaryRS.AbsolutePosition = Inc
    IncBal = IncBal + adoPrimaryRS.Fields("Credit").Value
Next Inc
lbIncome.Caption = Format(IncBal, "0.00")
adoPrimaryRS.Filter = adFilterNone
 
'NOTE there is more to this procedure but this is one block. All the other blocks rae the same.
 
End Sub

I get the error (ADO error) because I think that when it filters (above code) the "already open" db gets messed up with it some how.

The above code IS NOT the databse connection (primaryADOrs) it is just a procedure I use to just read (filter) some info from the database to fill in the LEDGER info.

Why am I getting this error? This error will not let me add a new record unless I refresh the db connection.

The ERROR happens when I try to add a new record. The reading filter for the ledger works works fine. But the Error did not occure until I add this procedure. So somehow this procedure is messing up the db connetion or recordset.

[brucevde]

The CalcLedger procedure is using the same recordset variable as the Form_Load (adoPrimaryRS). It creates a new recordset (readonly) with a different query. You don't see anything wrong with that?

[randem]

epixelman


The whole block of code...

Set dbtable = New Recordset
dbtable.Open "SELECT Sum(Credit) FROM Register", db
lbIncome.Caption = Format(dbtable!SumOfCredit, "0.00")
set dbtable= Nothng

To add a record use the SQL Insert statement

[epixelman]

Originally posted by randem
epixelman


The whole block of code...

Set dbtable = New Recordset
dbtable.Open "SELECT Sum(Credit) FROM Register", db
lbIncome.Caption = Format(dbtable!SumOfCredit, "0.00")
set dbtable= Nothng

To add a record use the SQL Insert statement

Where's the "Income" Filter?

This is just totaling all Credit.
I have to select the the Credit if the TYPE field is "Income".

I have Refunds that use the Credit field. The TYPE field holds "Income, Expense, Refund and OpenBal"

So my filter should be...
If TYPE = Income Then Get the CREDIT value

Will your idea work with the added filter?

[epixelman]

Originally posted by brucevde
The CalcLedger procedure is using the same recordset variable as the Form_Load (adoPrimaryRS). It creates a new recordset (readonly) with a different query. You don't see anything wrong with that?

Ohhh....

I didn't realize you have to have a different variable for the Recordset.

Is that gonna effect the SHAPE function of ADO?

[epixelman]

Originally posted by brucevde
The CalcLedger procedure is using the same recordset variable as the Form_Load (adoPrimaryRS). It creates a new recordset (readonly) with a different query. You don't see anything wrong with that?

THAT IS IT! IT WORKS GREAT!!!

Thank You - Thank You!!

I just created an another variable for adoPrimaryRS.open and called it LedgerData.open

So does that mean I can have many recordsets open at once?

[brucevde]

So does that mean I can have many recordsets open at once?

Absolutely. However, don't go crazy. Only return the records you need. For instance, your Filter recordset is not neccessary, which is what randem was trying to show you.

Let the database do the work where possible.

strSQL = "SELECT Sum(Credit) As SumOfCredit FROM Register Where Type = 'Income'"
 
Set dbtable = New Recordset
 
dbtable.Open strSQL,db
 
lbIncome.Caption = Format(dbTable!SumOfCredit, "0.00")
 
dbTable.Close
set dbTable= Nothng

[epixelman]

Thanks!

Everyone has been a great help!

All is working fine!

[randem]

epixelman

With a little imagination....

"SELECT Sum(Credit) FROM Register where Type='Income'",