VB3/Access 2.0 Stupid Beginner Question

VB3/Access 2.0 Stupid Beginner Question

Post by James Shiel » Wed, 29 Nov 1995 04:00:00




>Please pardon the ignorance, I am wading in to the murky water of VB3 and
>Access 2.0 without a life preserver.
>I am creating an app that keeps track of "Index Card" type material.  Here is
>the problem.  I want to be able to pull up an EMPTY database, that can then
>have info added to it or be able to delete all records and start again.
>Sub Form_Load ()
>    Dim Count As Integer
>    Loading = True
>    Deleting = False
>    FromNew = False
>    dtaDataFinder.Refresh
>    Count = 0
>    'Count how many cards exist already'
>    Do While Not dtaDataFinder.Recordset.EOF
>        If Not IsNull(dtaDataFinder.Recordset(0)) Then
>            Count = Count + 1
>        End If
>        dtaDataFinder.Recordset.MoveNext
>    Loop
>    dtaDataFinder.Refresh

>    txtTotalCards.Text = Count
>    dtaTotalCards.Refresh
>    dtaTotalCards.Recordset.MoveFirst

^ This looks like trouble. MoveFirst on an empty recordset will result in
an error.

Quote:

>    'If I havenot found any cards then just load an empty form
>    If Count = 0 Then
>        txtCardNumber.Text = "0"
>        Exit Sub
>    Else
>            'If the count doesn't match what the system says I should
>            'have then use the counts numbers, and update the system.
>    If Val(txtTotalCardsforDatabase.Text) <> Count Then
>            Loading = False
>            txtTotalCardsforDatabase.Text = Count
>            Loading = True
>        End If
>    End If
>    dtaDataFinder.Recordset.MoveFirst

^ Aha! Here's another one!

Quote:>    txtCardNumber.Text = 1
>    Loading = False
>End Sub

First off, if you refresh a recordset, you don't need to do a movefirst
because the refreshed set will already be at the first record (or eof).
Second, never perform any "move" operation on a recordset without checking
for an empty dynaset. Checking for eof will tell you this.

You should also note that if you have controls bound to the data control
(and I suspect you do), moving through the table will be very slow as the
the bound controls have to be updated each time. You'd be better off
cloning the dynasets. Then you wouldn't have to do a MoveFirst afterwards.

However, all this may be unnecessary. It looks like you have a table for
the total number of records in your main table, which you are maintaining
by hand. You can do away with this table, and all the rigmarole of updating
it, quite simply.

Lets assume you data table is called "Cardfile" (and its promary key is
called "KeyField") and your totals table is called "Totals". Simply change
the datasource property of your totals table from "Totals" to "Select
Count(KeyField) From Cardfile". Change KeyField for your primary key and
"Cardfile" for your table. Then all you have to do when you want to update
the total is refresh the data control.

Hope this helps.

James Shields

 
 
 

1. VB3/Access 2.0 Stupid Beginner Question

At a glance I can see that you need an error handler for this line of
code when you have an empty table.

dtaTotalCards.Recordset.MoveFirst

An ON ERROR RESUME NEXT would do.

if err then
   err=0
end if

I wouldn't worry about counting the number of records in the table.  The
error statement would do this.

If you really need a count then you could do this with the following
statement:

SELECT table.field1, count(table.field1) AS aliasfield
FROM table
WHERE table.field2=value
GROUP BY table.field1

--
Alexander Medwedew
Computer Ventures, Inc.

http://tribeca.ios.com/~compvent/
CADVANCE LITE - Affordable CAD Software
http://tribeca.ios.com/~compvent/cadvlite.html

2. Re.: Informix license deamon

3. Accessing Access 2.0 database with VB3, ODBC 2.0 and Microsotf Jet Engine Compatibility Layer

4. Enterprise Manager

5. VB3.0 / Access 2.0 OLE Question

6. Red vertical "wawe" line on the icon of SQLserver in Enterprise manager.

7. Beginner Question: Converting Access 2.0 to SQL Server

8. ORACLE on Unix

9. Stupid Beginner Question?

10. Database Problems (Beginner's stupid questions)

11. (Stupid?) Question (Beginner)

12. Help: Problem reading Access 2.0 mdb from VB3 under Win95/Access 7.0

13. Bug/limitation with VB3.0 / Access 2.0 compatability layer