Record Pointer

[FCCC]

Hello,

I need help on telling a record pointer to go the next record. My code below doesn't go to the next record and I don't know why??? Is there any other command???

Private Sub Form_Load()

recno = DCount("caindex", "dbo_PMATEST&quot
recntr = 0
DoCmd.RunCommand acCmdRecordsGoToFirst

Do While Not recntr = recno
caindex = recntr + 1
DoCmd.RunCommand acCmdRecordsGoToNext
Loop

End Sub

Any help will be very much appreciated.

 

[FoxProProgrammer]

Hi FCCC,

Are you trying to store a number in dbo_PMATEST.caindex sequentially from 1 to the number of records? If so, your code won't work. Please explain what you are trying to do and I'll suggest some code that will work.

Thanks, dz
dzaccess@yahoo.com

 

[FCCC]

Yes, I want to re-calculate my sequence number in caindex field of dbo_PMATEST everytime the form is loaded. Reason being was I am currently using a linked SQL Tables and if there are duplicate entries and I will delete one entry, I get "A single-row update/delete affected more than one row of a linked table" error and it will delete all duplicate entries. I don't want to do that, so I thought that to get around with this error, I created caindex as the key field on the SQL Table and just renumber it everytime the form is loaded. In this case I can delete only one record even they are duplicated.

Thank you in advance for you help.

 

[allanon]

Why don't you just place a non intelligent identity field on the table? This will ensure that every record is unique.

 

[FoxProProgrammer]

I understand what you want to do but not why. Why don't you just make caindex AutoNumber? Or is there already an AutoNumber field in that table? If there is, how could you have duplicate records? Or are you deleting a record in the parent table that is causing all child records in a related table to be deleted? If that's the case, why would you not want to delete all child records if the parent record is deleted? You would end up with unrelated records in the child table? I can suggest some code to do what you want, but I just thought that you might consider these alternatives first. If you click on the relationship, what is the setting of Cascade Update and Cascade Delete? dz
dzaccess@yahoo.com

 

[FCCC]

Linked table properties cannot be Modified in Access. All my tables are SQL and they resides in our NT Server so I have to linked it to my MS Access to be able to manipulate data. No, there is no auto number, I have another code that puts the number on caindex everytime I enter an item. Also, there is no child record. I am using a single table.

 

[FCCC]

How can I put a Non Intelligent Identity field on the table? That would be an alternative!

 

[allanon]

This is how much of our access is done as well. I would ask your dba to place an identity field on the sql table. They will probably complain about it BUT it is my experience that a dba would rather do a little work to have a table working right then have to clean up data later because of corruption do to poor table design.

 

[FCCC]

Allanon, OK, I will try to do that... thanks for your suggestion.

 

[FCCC]

I got it to work. I am posting the code for those that will have the same problem as mine:

Private Sub Form_Load()
recno = DCount("caindex", "dbo_PMATEST&quot
recntr = 0
DoCmd.GoToRecord , , acFirst
Do While recntr <> recno
caindex = recntr + 1
recntr = recntr + 1
DoCmd.GoToRecord , , acNext
Loop

End Sub

Thanks to allanon and dz for the ideas.