capturing record ID

[BeffyMar]

I know that this is probably a stupid question but here it goes... I want to capture the actual record number and contain it in a field... I tried the autonumber method but this isnt accurate because if a record is deleted then it throws the numbers off...I am desperate here... I think that I am completely overlooking something that is so basic... any help would be greatly appreciated...<br><br><br>Thanks in advance,<br>Mary Beth

 

[elizabeth]

Access doesn't contain a record number. What were you hoiping to do with a record number? Maybe there is a different way.

 

[BeffyMar]

Sorry... maybe I need to be more specific.&nbsp;&nbsp;At the bottom of each form there is a data control that allows you to scan through the records.&nbsp;&nbsp;It contains a bar with a number in it according to what record you are viewing ( ex.1, 2, 3).&nbsp;&nbsp;I need to capture this number to use on reports so that is someone says they need info on record 7, the user can go directly to that record by entering a 7 in the bottom bar.&nbsp;&nbsp;I hope this helps clarify what I am wanting to do.<br><br>Thank you again,<br>Mary Beth Wallace

 

[aklein]

Every time the report is run, it will be valid, as far as the current &quot;record numbers&quot; go.&nbsp;&nbsp;Do you want to maintain all records forever?&nbsp;&nbsp;If so, remember that the larger the table, the slower any operation on it will be.

 

[elizabeth]

This number is not reliable as it may change. For instance if the report were to show this as number 7, then you deleted record #6, record #7 would show up in the data control as #6. You want to use some other way to uniquely identify a record. That is the function of the Primary Key. Does your table have a Primary Key? If not, assign one. If there is nothing unique in the record itself (such as Social Security #), create a new field and set the data type to Autonumber. Then display that PK on your form and your report, and use the Find button with your form to find that record.

 

[BeffyMar]

I realize that this seems like an unordinary situation.&nbsp;&nbsp;They want the number to change.&nbsp;&nbsp;If record 6 is deleted then they want record # 7 to become ID# 6.&nbsp;&nbsp;I know that this really doesnt make any practical sense, but this is what the company wants.&nbsp;&nbsp;<br><br>As for maintaining all records forever... the answer is no.&nbsp;&nbsp;When records are removed, they want all the ID's to be renumbered.&nbsp;&nbsp;Like I said though, this really doesnt make sense from a developer's stand point.<br><br>Thank you again,<br>Mary Beth

 

[samara]

I'm thinking of another approach, what about create a field in your table called &quot;RecordID&quot; and put it in your form..OK, then...<br>1) in the general section of your form put..<br>&nbsp;&nbsp;dim myRS as Recordset<br><br>2) in Load event of your form put..<br>&nbsp;&nbsp;set myRS = me.RecordsetClone<br><br>3) in Current event of your form put..<br>&nbsp;&nbsp;me.RecordID = myRS.AbsolutePosition + 1<br><br>Hope this would help YOU, and feel free to tell me about any errors... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[samara]

my previous post assume you have to move through-out every record of your form, and of course it's so hard if your form contains many records, So I improve my function as follow...<br><br>Function setRecordID()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim mydb As DAO.Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim myRS As DAO.Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set mydb = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set myRS = mydb.OpenRecordset(&quot;Table1&quot;, dbOpenDynaset)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;With myRS<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do While Not .EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Fields(&quot;RecordID&quot = .AbsolutePosition + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>End Function<br><br>Notes:<br>1) i'm using Access 2000, so i used the &quot;DAO.&quot; if you use Access 97 you could omit it.<br>2) i used Dynaset type of Recordset cause it support using local, link tables, queries and SQL statements.<br><br>i tried it out and it just worked so fine, hope this would help you TOO ...<br><br>the only disadvatage of it that you have to rerun this funtion after every edit with your Recordset&nbsp;&nbsp;) <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[BeffyMar]

Thank you samara... I will try this first thing tomorrow when I get to work... after viewing your code, I think that this may be the answer to what I am looking for...I will let you know...<br><br>Thanks again,<br>Mary Beth

 

[BeffyMar]

Samara,<br>For some reason this isnt working for me.&nbsp;&nbsp;I dont know if it is because I am calling the function at the wrong place.&nbsp;&nbsp;It did manage to totally mess all of my numbers up hehehe.&nbsp;&nbsp;I am sure that I am doing something wrong.&nbsp;&nbsp;By the way, I called the function from both the current event and the load event.&nbsp;&nbsp;Neither worked correctly<br><br>Thanks again,<br>Mary Beth

 

[BeffyMar]

Samara... got it... It worked... I have to just call it onOpen and onClose.<br><br><br>When I did it onCurrent, wherever I was within the records became #1.&nbsp;&nbsp;<br><br>Thank you so much.<br><br>Mary Beth