Link to home
Start Free TrialLog in
Avatar of opsplanner
opsplanner

asked on

MS Access and VB Basic

I am using VB6/(DAO data object)/MSAccess2000.
In my app the user can add/delete, move next/previous through records.

I am using the following code to add a new record:

'Set dim's
Dim db As Database
Dim rs As Recordset

'Open database and recordset
DBPath = App.path & "\XCollections.mdb"
Set db = OpenDatabase(DBPath)
Set rs = db.OpenRecordset("observation")
Data1.Refresh

PROBLEM: When I use the code below, the intent is that the new record will be added as the last record in the database.  But, insted it inserts the record 5 records BEFORE the EOF.  It places the record here regardless of which record is displayed.
'======================
'I use this code to add a new record:
.MoveLast
.AddNew
.Fields("unsolicited_obs") = gObservationChoice
.Update
'======================
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

the 'location' of a record has almost no real meaning.  The fact that you see the new record '5 records BEFORE the EOF' means nothing, as the controlling factor may be such underlying things as what INDEXES are defined on the recordset (or actually the underlying table).  What is the Primary Key of the table in question, and how is that key being assigned to new records?

AW
Avatar of opsplanner
opsplanner

ASKER

OK, that makes sense.  Is the only way to get the records to always appear in the order they were created, require one to say, set up an sequencial record number and use it as the record key?
as I said before, WHAT is the Primary Key of the Table in question?

There is NOTHING that inherently identifies the ORDER in which records are added to a table, unless you are using a Primary Key that is Defined as type AUTONUMBER which is AUTOMATICALLY incremented to the next value as each record is addedd to the Table, or else if you have field, defined in the Table, which is automatically assigned the Date and Time that the record was added to the table (there is NO such AUTOMATIC field in Access - you would need to define such a field, and define it's default value to the Now() - so that The Date and Time would be assigned when a record was first created.

A relational Table is nothing more that a collection of records, that have no inherenet ORDER.  The order is defined by either the Primary Key, or by the Sort Oreder that is 'externally' applied to the table when you choose to display the records. (and the Table can be SORTED on ANY ONE field, or ANY COMBINATION of fields).

AW
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
opsplanner:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
I have followed the instructions...quite frankly this whole process is very confusing.
I have attempted twice to close this out to no avail.  I am probably too thick headed so would you please provide me with the method for closing