MS Access and VB Basic

Posted on 2003-03-05
Medium Priority
Last Modified: 2010-04-17
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")

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:
.Fields("unsolicited_obs") = gObservationChoice
Question by:opsplanner
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 44

Expert Comment

ID: 8073923
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?


Author Comment

ID: 8074308
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?
LVL 44

Expert Comment

ID: 8074381
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).

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 44

Accepted Solution

Arthur_Wood earned 200 total points
ID: 8074420
If there is NOT now a Primary Key defined for your table, then you can add a NEW field, with Whatever name you want, and define the Field TYpe as AUTONUMBER.

That field will  automatically start with 1 (though this can be changed) and will automtically increment by 1 (this can also be changed) with each new record, as it is added to the table.  The existing records will be assigned the values (starting with the initial value and increment), so that the NEXT new record will be automatically assigned the NEXT highest value, after the last record in the exiting table.


Expert Comment

ID: 9447338
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 
Post your closing recommendations!  No comment means you don't care.

Author Comment

ID: 9546601
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Simple Linear Regression

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question