Solved

Code Saving Records in Reverse Order

Posted on 2000-03-01
28
161 Views
Last Modified: 2010-05-02
The following code saves a new record to a table. the primary key " Client_Interaction" is autonumber(not coded), in an access db table. c_inter is the recordset. So a new record is added but it adds it at Client_Interaction at 1, as opposed to say adding a Client_Interaction # 5, with the fifth record. I'm at the point where I want to flatten my keyboard and monitor. Please help. Thank you.
---------------------------------------

Private Sub cmd_Save_Click()
 
Data1.ReadOnly = False
   
   'c_inter.MoveLast
    c_inter.AddNew

    c_inter!Number_Client = txtNumber.Text
    c_inter!TypeOfInquiry = cmboTypeOfInquiry.Text
    c_inter!Status = cmboStatus.Text
    c_inter!Date_Interaction = txt_DateInteraction.Text
    c_inter!DateEntered = txt_DateEntered.Text
    c_inter!Note = txtNotes.Text
    c_inter!InteractionAuthor = cmbo_Author.Text
    c_inter!TimeSpentOnInteraction = Input_TimeSpentInteraction.Text

    'c_inter.Update
    ClientInteraction.cmd_Save.Enabled = False

    ClientInteraction.Data1.Recordset.Close

End Sub
0
Comment
Question by:diek_nf
  • 11
  • 10
  • 5
  • +1
28 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 2573057
c_inter.AddNew

''''**                       c_inter!Number_Client = txtNumber.Text
                       c_inter!TypeOfInquiry = cmboTypeOfInquiry.Text
                       c_inter!Status = cmboStatus.Text
                       c_inter!Date_Interaction = txt_DateInteraction.Text
                       c_inter!DateEntered = txt_DateEntered.Text
                       c_inter!Note = txtNotes.Text
                       c_inter!InteractionAuthor = cmbo_Author.Text
                       c_inter!TimeSpentOnInteraction =                        Input_TimeSpentInteraction.Text

                       c_inter.Update
                       ClientInteraction.cmd_Save.Enabled = False

                       ClientInteraction.Data1.Recordset.Close
0
 

Author Comment

by:diek_nf
ID: 2573227
deighton,
Your "''''** " was displayed oddly by the browser. So I am not certain what it means.
                     
When I remove the comment from c_inter.Update it places a record at the BOF and EOF.

I can't believe I am having such a hard time sorting this out. Thanks, I truly appreciate your time and effort.

diek_k
0
 
LVL 18

Expert Comment

by:deighton
ID: 2573262
I'm not sure that you need the

c_inter!Number_Client = txtNumber.Text

so comment it out.
0
 

Author Comment

by:diek_nf
ID: 2573303
Edited text of question.
0
 

Author Comment

by:diek_nf
ID: 2573315
deighton,
I apologize the primary key is not Number_Client, it is a foreign key. The prim key Client_Interactions is not in the save button. That update defintely adds two record. My boss is loosing his patient ways
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2573538
The records aren't really in any "order".  If you add a record and then look at the table or build a recordset out of it, the records will be ordered by whatever indices are on the table.  How is the table indexed?

How is your autonumber generated?

If the records aren't in the order you want, can't you just reorder them on whatever form you're opening up to view them?

It's possible that I don't understand the question at all . . .But it seems like this shouldn't be hard to figure out.

Wes
0
 
LVL 4

Accepted Solution

by:
wesleystewart earned 50 total points
ID: 2573547
Is your form bound to these records?
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2573554
Otherwise I don't see how you could be adding two records . . . .

Wes
0
 

Expert Comment

by:ce34ll
ID: 2573593
I don't see why the added position of the record is important. Maybe I am missing something? If you want your record to be in the last position then read it into the last position. When you create your recordset do like this.

dim db as database
dim rs as recordset

set db = opendatabase(mydatabase)
set rs = db.execute "Select * for [your table name goes here] order by Client_Interaction"

This will create your recordset and sort the records by the autonumber which will by logic put the last added record in the last position.
0
 

Expert Comment

by:ce34ll
ID: 2573600
sorry its "Select From"

and this can be used with databound controls.
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2573612
Are you sure that's really the alsolute best solution to the problem?  Something that tentative should probably be posted as a comment.

diek:  You've has some input from us, now what about some more details?

Wes
0
 

Expert Comment

by:ce34ll
ID: 2573686
Why should I post this as a comment when It will solve the problem?

Try it yourself with a recordset and you will see.

I mean who cares what the position in the database base is unless your are going to allow your end users the right to open the database directly. I don't think anyone would do that. If you sort the recordset your have the view just the way you want it. Plus the recordset is updatable. So newly added records will be in the last position. Is there a part of this problem I did not answer?
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2573719
Since your solution is obviously the absolute best resolution to the issue and is guaranteed to fix the questioner's problem with no further input or questions, then by all means, I'm glad you posted it as an answer.

Of course this locks the question and moves it to the locked question area where no one else will see it as a new question and add their input.

But I don't think you've solved the problem.  Your code snippet in in DAO?  What if our questioner is using ADO?  What if they don't understand what you're talking about?  
0
 

Expert Comment

by:ce34ll
ID: 2573793
the code can be used with DAO or ADO. With ADO the opening would be a little diff. but would look like this.

Dim MyConn as ADODB.Connection
Dim MyRec as ADODB.Recordset

set myconn = new ADODB.Connection
set myrec = new ADODB.Recordset

myconn.connectionstring = userconnectionstring

myconn.open ,login,password,options,options

myrec.open sqlstring,myconn
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:diek_nf
ID: 2573914
I love this site. Anyways. I guess I got a bit caught up in the order. It just seemed illogical. No the form is not bound to these records.

If you have read any of my other questions you'll recall I have just switched back to vb from java, and I am new to VB6.

As far as SELECT, that's not really the problem. And sorry ce34ll I can't see have how SELECT will affect how data and records are saved. I am puzzled as to why when I AddNew it inserts the record at BOF, it seems wierd.

For everyone who feels that end users will never see the db, think again. My client wants read premission to the db, so I had to create and set up a linked db. And nothing I do is going to change their mind. Using terminal server and the security features in access has presented some challenges.

Anyways I am up to my neck, I will test everything I can and I assure everyone that I truly appreciate their assistance. I'll be back.

ps when commenting on comments could you please identify who you are referring to, so I can follow the flow of our discussion. Thank you
0
 

Expert Comment

by:ce34ll
ID: 2573931
If you do not know enough about VB to understand how the Select statement controls your recordset you should be a book. Creating a linked db is not the same as allowing the user direct access to the database.
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2573985
diek:

Here's the key:  Records are added and displayed according to the index at work on the table.  Unless you change the order through a query or a select statement like ce34ll suggests, the records will be ordered according to the index.

When you "view" the records they are again ordered by the index unless you specify a different order.  In the binary file the records are not stored in any particular order.  They are only displayed that way.

Even if your client wants all kinds of permissions to the database, they can still only view the data via forms that you develop.  In forms and queries you have complete control over how the records are displayed.

If your client insists on access to the mdb file, they just need to know that unless you have been given specific criteria on how to order records, they will be not be displayed in any particular order.

Wes
0
 

Author Comment

by:diek_nf
ID: 2574003
ce34ll,
Yikes! Lighten up. If you have nothing nice to.... Maybe you should go read a book.
Point 1.
I understand perfectly how displaying data and loading data into vb is affected by sql statements. In "VB 6 Database How-To's" section, "Add and Delete Record", demonstrates that the actual save is dependent upon "addnew" and "update". SELECT is certainly not required to save records. Unless your are importing data from one table into another in some complicated sql statement.

A record when saved(depending if fields can be null) requires all fields to be saved into the record in the table. How would "select a b c from table where 2= e, affect how a record is saved in vb and access. If someone else could back me up I am almost certain that SELECT is primarily for viewing specific data. I think wesleystewart agrees.

Point 2
If you have admin rights to on a linked table, yes you can change the data. Go play with your Workgroup Administrator and Security tools and find out.
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2574034
diek:

I think the point is that you, the developer, can't be responsible for how data looks if your users don't view it through the predefined interface you build for them.

Select statements are for selecting data, egardless of whether you are going to view it, edit it, etc.  That's just how you decide which records you want to deal with.

Wes
0
 

Author Comment

by:diek_nf
ID: 2574124
Maybe this will help.

I created a simple program with a combobox and txtbox and a save button that saves a record to a database each time the command button is clicked. The table has 3 fields, two store the the data saved from the form, the 3rd auto increments and acts as the p key. When I save it adds the records in order they are created. Again I am perplexed as why the other code won't do the same thing, add in numerical order, as they are added.

I am also very perplexed to understand why "c_inter.Update" generates two new records in the code in my original question. It doesn't in the code below. This is why I think something is really screwed up.
=====================================
Dim db1 As Database
Dim cust As Recordset
----------------------
Private Sub Form_Load()
Set db1 = OpenDatabase(C:\Pork\bacon.mdb")
Set cust = db1.OpenRecordset("tea")
cust.MoveFirst
Do Until cust.EOF = True
Combo1.AddItem cust!Name
cust.MoveNext
Loop
End Sub
-----------------------

Private Sub Command1_Click()
cust.AddNew
cust!Name = Combo1
cust!salary = Text2
cust.Update
Combo1.AddItem Combo1
End Sub
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2574167
diek:

Sorry, friend, but you honestly don't understand at all what is going on.  You code does not add the records in the order they are created.  It displays them in the order they are created.

No go into your database, and add an index to the salary field.

Go back to your app.  Now it shows them in order by salary.

Records are not input or added in any order.  They are displayed in order, and it is over this display order that you must exert some control.

Imagine this:

All the records you add are piled in a bucket.  When you view them, you don't view the bucket all at once.  The app picks them out of the bucket for you according to whatever criteria you provide.  Even if you don't provide a criteria, it displays them according to something.  It might even be the order they were entered, but that is only until it finds a reason to sort them another way.  A table is simply a set of records, just like you might go into a bar and line up all the patrons.  You don't necessarily line them up according to who came to the bar first, but maybe by gender, height, weight, etc.

Wes
0
 

Author Comment

by:diek_nf
ID: 2574297
Wes,
I just did exactly what you said and it still adds them in the order they were created. If you want the project I'll e-mail it to you. It only tiny. So lets get a philosophical. I understand that once a record is entered saved and stored then they are in the bucket, but to say "Records are not input or added in any order". They are added one at a time with the save, the autonumber should be a reflection of this. I enter a record the auto increment increments by 1. So 1,2,3 records. I understand(maybe I'm not making this clear)that records are viewed based upon how I request them. Anyways, I got to catch my ride.
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2574319
Autonumber field . . . .

Yes indeed, the autonumber field should be incremented with each record.

So are you saying your autonumber isn't working?  That would be a different story.

If you're using the autonumber thing in Access, it is pretty foolproof.  It starts with a number and all new records receive that number, incremented by 1.

If that isn't working, you really have a problem.

Wes
0
 

Author Comment

by:diek_nf
ID: 2575101
My vb code produces the following records, I have reduced the field names to somewhat clarify. The prim key is client_interactions, it is autonumbered, set to increment, indexed(no dups)
-------------------------------------------------------------------------------- client_interactions          Client_Num     Note                      
1            87              added 4th
2            014            added 3rd  
3            524            added 2nd
4            14            added 1st
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2576333
As far as I can tell, what you're describing is impossible. Record 4 would have to come AFTER record 3.  How can it assign a 4 to the first record?  It doesn't know how many records you are entering, so it couldn't generate records like that.  I must admit I am baffled.

Are you looking at this from access or from a bound form in VB?  I would suspect that somewhere a field reference is incorrect and so you aren't actually viewing the autonumber field.

Here's a test:

Empty the table
Add a record through your VB interface
Close your VB app
Go into Access, look at the record and its autonumber value
Close Access, open the VB app
Add another record
Close the vb app
Go back to Access and look at the table
There should be two records, and the first record you added should still have the same autonumber value it had before

Wes
0
 

Author Comment

by:diek_nf
ID: 2576422
Wes,
Thank you that sounds like an excellent test. O when I add a record using access, I did this as a test, it autoincrements properly. I'll be back.
diek
0
 

Author Comment

by:diek_nf
ID: 2577690
Wes,
This was the prob. My textboxes were linked to a datasource. Everything is working perfectly now that I've removed the data source for the saved objects. Thank you.
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2577786
Happy to help.

W
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now