Code Saving Records in Reverse Order
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!TimeSpentOnInterac
'c_inter.Update
ClientInteraction.cmd_Save
ClientInteraction.Data1.Re
End Sub
--------------------------
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!TimeSpentOnInterac
'c_inter.Update
ClientInteraction.cmd_Save
ClientInteraction.Data1.Re
End Sub
ASKER
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
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
I'm not sure that you need the
c_inter!Number_Client = txtNumber.Text
so comment it out.
c_inter!Number_Client = txtNumber.Text
so comment it out.
ASKER
Edited text of question.
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Otherwise I don't see how you could be adding two records . . . .
Wes
Wes
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.
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.
sorry its "Select From"
and this can be used with databound controls.
and this can be used with databound controls.
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
diek: You've has some input from us, now what about some more details?
Wes
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?
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?
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?
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?
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,op
myrec.open sqlstring,myconn
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,op
myrec.open sqlstring,myconn
ASKER
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
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
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.
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
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
ASKER
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.
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.
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
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
ASKER
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
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
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
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
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
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
ASKER
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.
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.
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
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
ASKER
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)
--------------------------
1 87 added 4th
2 014 added 3rd
3 524 added 2nd
4 14 added 1st
--------------------------
1 87 added 4th
2 014 added 3rd
3 524 added 2nd
4 14 added 1st
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
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
ASKER
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
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
ASKER
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.
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.
Happy to help.
W
W
''''** 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!TimeSpentOnInterac
c_inter.Update
ClientInteraction.cmd_Save
ClientInteraction.Data1.Re