Re-Number a table

[JDR]

I have a table that is part of a report that has a seq. # field.

Periodically I need to renumber the rows in the table due to changes and
groupings. I'm sure it can be done with a macro but am unsure how.

Any ideas?

Thanks

Jeff...

 

[Steve Schapel]

Jeff,

This sounds like it might be a job for an Update Query. If you need
more help with this, I think you will need to give more details, maybe
with examples of what you want to do with this Seq field.

 

[JDR]

Thanks!

I have 2 tables, Attorneys and Lawfirms and what I need to do is print
reports one of all the attorneys alphabetically and another of Lawfirms
alphabetically.

Where the sequence number comes in is that when the Attorney list is
printed, rather than print the Lawfirm name I want to print a # (the
sequence number) which tells the person looking at the Attorney list the #
of the Lawfirm from the Lawfirm report. On caveat is that there may be more
than one record for the Lawfirm because of more than one location but the
sequence number would be the same on both.

I hope this makes sense!

Thanks

Jeff...

 

[Steve Schapel]

Jeff

This would be difficult to do with macros, in fact at the moment I can't
think of a way. I would assume at the moment your Lawfirms table has a
Primary Key field, LawfirmID or some such, and that this is the basis of
the relationship with the Attorneys table, which has this same field as
a foreign key? And that this is *not* the SeqNo? I would recommend
that you leave it this way. So where does the SeqNo come from?... Is it
an actual field in the table, or is it an unbound "running sum" control
on the report itself? I think to achieve your purpose, it should be
added as a field in the Lawfirms table, and this field included in the
queries that your reports are based on. And then, I think you will need
to write a VBA procedure to refresh the value of this field in the
Lawfirms table. Here is an off-the-top-of-the-head example:
Private Sub RefreshSeq()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT SeqNo FROM Lawfirms ORDER
BY Lawfirm")
Do Until rst.EOF
.Edit
!SeqNo = .AbsolutePosition + 1
.Update
.MoveNext
Loop
Set rst = Nothing
End Sub

 

[Steve Schapel]

Jeff,

I did not see your attachments, because I have my newsreader set up to
not receive them. But in any case, I think I understood what you meant,
and handling the scenario you described was the precise purpose of my
previous suggestion. Did you try it? What happened?

Having said that, I realise I didn't cater to the situation of you
having more than one Lawfirm record with the same law firm. In fact,
you shouldn't really have this, and it is a flaw in your table design.
But you could still cope with it via a modification of the code I
suggested before, for example...
Private Sub RefreshSeq()
Dim rstAll As DAO.Recordset
Dim rstUnique As DAO.Recordset
Set rstAll = CurrentDb.OpenRecordset("SELECT LawFirm, SeqNo FROM
Lawfirms")
Set rstUnique = CurrentDb.OpenRecordset("SELECT DISTINCT LawFirm
FROM Lawfirms ORDER BY LawFirm")
With rstAll
Do Until .EOF
rstUnique.FindFirst "LawFirm ='" & !LawFirm & "'"
.Edit
!SeqNo = rstUnique.AbsolutePosition + 1
.Update
.MoveNext
Loop
.Close
End With
rstUnique.Close
Set rstAll = Nothing
Set rstUnique = Nothing
End Sub

However, I note that based on your example, the sequence numbers do not
reflect the alphabetical order of the Law firm name, which is the other
assumption I made, so we would need to sort this out too.

 

[JDR]

Thanks Steve and no I haven't tried this yet but will shortly.

I know what you mean by a flaw in my DB design, which I got a little sloppy
since it was a fairly small DB and I didn't take the appropriate time to
analyze.

I'll let you know my results.

Thanks

Jeff...

 

[JDR]

Not to be stupid, but I've never used VB in Access and am unsure where I set
the code suggested and then to run the process.

Thanks

Jeff...

 

[Steve Schapel]

Jeff,

Nothing stupid about your question. I put it as an independent
subroutine because I did not know when and how you would want it to
happen. But I guess the simplest approach would be to assign the code
directly to a database Event. For example, you could have a command
button on the form where you add new Lawfirms, and then after you have
added or deleted a law firm, you could click the button and the SeqNo
will refresh. In this case, you could go in the design view of the
form, to the On Click property of the command button, click the [...] at
the right of the property box, and enter the code I gave in there,
except the line Private Sub RefreshSeq() will be replaced by Private Sub
YourButton_Click(). Or else maybe on a command button on a form that
you use to launch your reports?

By the way, I have also realised that I have made an assumption that the
SeqNo will be a Number data type, which I think is definitely the
easiest to work with. If you want it to come out on your report as you
indicated, i.e. like (L-4) then you can use the Format property of the
textbox on the report, set to "(L-"0)

 

[JDR]

When I click on the button I've assigned to a form I get the following
error:

An alert button with:
Compile Error:
User-defined type not found

It displays the VB code and the line

Dim rstAll As DAO.Recordset

is highlighted

What am I doing wrong?

Thanks

Jeff...

Jeff,

Nothing stupid about your question. I put it as an independent
subroutine because I did not know when and how you would want it to
happen. But I guess the simplest approach would be to assign the code
directly to a database Event. For example, you could have a command
button on the form where you add new Lawfirms, and then after you have
added or deleted a law firm, you could click the button and the SeqNo
will refresh. In this case, you could go in the design view of the
form, to the On Click property of the command button, click the [...] at
the right of the property box, and enter the code I gave in there,
except the line Private Sub RefreshSeq() will be replaced by Private Sub
YourButton_Click(). Or else maybe on a command button on a form that
you use to launch your reports?

By the way, I have also realised that I have made an assumption that the
SeqNo will be a Number data type, which I think is definitely the
easiest to work with. If you want it to come out on your report as you
indicated, i.e. like (L-4) then you can use the Format property of the
textbox on the report, set to "(L-"0)

--
Steve Schapel, Microsoft Access MVP

Not to be stupid, but I've never used VB in Access and am unsure where I set
the code suggested and then to run the process.

Thanks

Jeff...

 

[Douglas J. Steele]

Sounds as though you're using Access 2000 or 2002, and you haven't set a
reference to DAO. (By default Access 2000 and 2002 use ADO)

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I click on the button I've assigned to a form I get the following
error:

An alert button with:
Compile Error:
User-defined type not found

It displays the VB code and the line

Dim rstAll As DAO.Recordset

is highlighted

What am I doing wrong?

Thanks

Jeff...

Jeff,

Nothing stupid about your question. I put it as an independent
subroutine because I did not know when and how you would want it to
happen. But I guess the simplest approach would be to assign the code
directly to a database Event. For example, you could have a command
button on the form where you add new Lawfirms, and then after you have
added or deleted a law firm, you could click the button and the SeqNo
will refresh. In this case, you could go in the design view of the
form, to the On Click property of the command button, click the [...] at
the right of the property box, and enter the code I gave in there,
except the line Private Sub RefreshSeq() will be replaced by Private Sub
YourButton_Click(). Or else maybe on a command button on a form that
you use to launch your reports?

By the way, I have also realised that I have made an assumption that the
SeqNo will be a Number data type, which I think is definitely the
easiest to work with. If you want it to come out on your report as you
indicated, i.e. like (L-4) then you can use the Format property of the
textbox on the report, set to "(L-"0)

--
Steve Schapel, Microsoft Access MVP

Not to be stupid, but I've never used VB in Access and am unsure where

I

set

 

[JDR]

It works!

You guys are great!

Thanks again!

Jeff...

Sounds as though you're using Access 2000 or 2002, and you haven't set a
reference to DAO. (By default Access 2000 and 2002 use ADO)

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I click on the button I've assigned to a form I get the following
error:

An alert button with:
Compile Error:
User-defined type not found

It displays the VB code and the line

Dim rstAll As DAO.Recordset

is highlighted

What am I doing wrong?

Thanks

Jeff...

Jeff,

Nothing stupid about your question. I put it as an independent
subroutine because I did not know when and how you would want it to
happen. But I guess the simplest approach would be to assign the code
directly to a database Event. For example, you could have a command
button on the form where you add new Lawfirms, and then after you have
added or deleted a law firm, you could click the button and the SeqNo
will refresh. In this case, you could go in the design view of the
form, to the On Click property of the command button, click the [...] at
the right of the property box, and enter the code I gave in there,
except the line Private Sub RefreshSeq() will be replaced by Private Sub
YourButton_Click(). Or else maybe on a command button on a form that
you use to launch your reports?

By the way, I have also realised that I have made an assumption that the
SeqNo will be a Number data type, which I think is definitely the
easiest to work with. If you want it to come out on your report as you
indicated, i.e. like (L-4) then you can use the Format property of the
textbox on the report, set to "(L-"0)

--
Steve Schapel, Microsoft Access MVP


JDR wrote:
Not to be stupid, but I've never used VB in Access and am unsure

where

I