Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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
'======================
0
opsplanner
Asked:
opsplanner
  • 3
  • 2
1 Solution
 
Arthur_WoodCommented:
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
0
 
opsplannerAuthor Commented:
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?
0
 
Arthur_WoodCommented:
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
0
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.

 
Arthur_WoodCommented:
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.

AW
0
 
CleanupPingCommented:
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.
0
 
opsplannerAuthor Commented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now