How do i add a record to a table at a specific position

[Hitower]

I am having trouble with finding the right command for adding a new record to a table at a specific position.

Dim rs As DAO.Recordset
Dim a As Integer
Set rs = Me.RecordsetClone
rs.MoveLast
a = CLng(rs.Fields(colnum).Value) + 1

i want to put the record at position a
as u can see the trouble is not finding the location i need to put it in but getting it there !! Can anyone plz help me

All help is welcome tnx

 

[gusbrunston]

Hi, Hi:

Not sure I understand your problem. Once you add a record it will be included in your table. The next time you open your table in datasheet view you will find your record in the proper place according to the order in which the table is sorted. If not, you didn't get the record entered in the first place.

If you're using a form based on a query to display records from the table, then you need to requery after you add the record in order for it to display in the proper order/sort along with its brother and sister records. If for some reason your new record doesn't meet the criteria of the query then it won't display, but that's another problem.

Lemme know if I've not understood your question.

Cheers, Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[Hitower]

Tnx Gus,

But for some strange reason the program doesnt add a new record to the table. it just adds a new number in the form but when i want to saven the form it says that the specific number does not ecxist ?! so i am guessing it just didnt add the number. I think the top part of your reply is about what my problem is.

Set rs = Me.RecordsetClone
rs.MoveLast
a = CLng(rs.Fields("colnum&quot.Value) + 1
Me.Record_index.Value = a
DoCmd.GoToRecord acActiveDataObject, , acNewRec

what i want the program to do is just simply create a new record. seeing the record_index numbers are unique i want the record to be placed at a new unique number "a".

I hope this explanation is a bit better

 

[gusbrunston]

I dunno...

I know next to nothing about ADO (ActiveDataObject) code. However, in your first post you Dim rs As DAO.Recordset and in your last post you DoCmd.GoToRecord acActiveDataObject, , acNewRec. Can you mix DAO and ADO like that?

Perhaps you have a 'required' field in the table and the record will not be saved until that field contains a value that conforms to the validation requirements of the field.

Are the controls on your data entry form bound to fields in the table? or to a query?

Have you checked the settings in the form property sheet -> data -> permit edits, permit additions, permit deletions, etc.? Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[Hitower]

No the controls arent bound , they are independant. But its the command which u mentioned :

DoCmd.GoToRecord acActiveDataObject, , acNewRec

that i aint sure of. are there other commands to be used to create a new record ? Or maybe u know how to use that command properly ?

I have checked the permits and they are all correct, its allowed to perform all actions.

Tnx Hitower

 

[gusbrunston]

Dear Hi:
If the controls aren't bound to the table's fields, how do you expect to get values into those fields other than by entering them directly into the table in datasheet view? Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[Laois]

Gus is right, if the controls are not bound then you would have to write code to enter the contents of each control into th correct field in the table. You can not use the "DoCmd.GoToRecord acActiveDataObject, , acNewRec" on a form that is not bound to a table. By the way you just need to use "DoCmd.GoToRecord , , acNewRec" to add a new record. Unless you have have a reason not to, you should bind the form to the table and then bind each control on the form to the correct field in the table. Also it might be easier to make the colnum field an AutoNumber. That way you don't have to find the last record to get the value and then increment it, the AutoNumber will take care of that for you. I think this would make things alot simpler but you might have other reasons for trying to do it the way you are.

Hope this helps!

 

[Hitower]

Tnx for all the help guys i found out what i did wrong. I have 1 fiels which i call Record_index which is undependent and i made another 1 which is called col_num which is dependent. Col_num writes the location i want to the tables and record_index shows it to me i made Col_num a hidden 1.

At the start i only had Record_index which was undependent u guys where rite that doesnt work at all !!

this is the code now :

Private Sub Record_toevoegen_Click()
On Error GoTo Err_Record_toevoegen_Click
Dim rs As DAO.Recordset
Dim a As Integer

Set rs = Me.RecordsetClone

rs.MoveLast
a = CLng(rs.Fields("colnum&quot.Value) + 1
DoCmd.GoToRecord , , acNewRec
Me.col_num.Value = a
Me.Record_index.Value = Me.col_num.Value

Exit_Record_toevoegen_Click:
Exit Sub

Err_Record_toevoegen_Click:
MsgBox Err.Description
Resume Exit_Record_toevoegen_Click

End Sub



Tnx again for the help .., i appreciate it m8's
cheers