Adding a number to a record

[Guest]

Background: I am creating a catalogue for entries into a livestock show.
The numbering can only be done once all the entries are recieved - they then
need sorting into order (ie into the sections and classes within the section
and then eg into DOB of the exhibitor), after this they need a number
assigned to them which will be printed in a catalogue, and used for judging.

Up till now I have achieved this by creating the catalogue number when
creating a new table, then deleting all the records from the "real" catalogue
table and then copying all the records back ie -

SELECT Catalogue.ClassEntryID, Catalogue.EntryID, Catalogue.ClassId, (Select
Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid) AS CatalogueNo,
Catalogue.[Category Running order], Catalogue.[class running order] INTO
tempCatalogue
FROM Catalogue;

Is there any way that I can achieve this result without creating a new table
eg by using an update query? I tried simply using the same expression in an
update query:

UPDATE Catalogue SET Catalogue.CatalogueNo = (Select Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid);

But I get an error "Operation must use an updatable query. (Error 3073)",
even though the Catalogue number is not part of a relationship.

Am I going about this the wrong way? Should I be trying to use code??!!

Many thanks

Helen

 

[Chris2]

Background: I am creating a catalogue for entries into a livestock show.
The numbering can only be done once all the entries are recieved - they then
need sorting into order (ie into the sections and classes within the section
and then eg into DOB of the exhibitor), after this they need a number
assigned to them which will be printed in a catalogue, and used for judging.

Up till now I have achieved this by creating the catalogue number when
creating a new table, then deleting all the records from the "real" catalogue
table and then copying all the records back ie -

SELECT Catalogue.ClassEntryID, Catalogue.EntryID, Catalogue.ClassId, (Select
Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid) AS CatalogueNo,
Catalogue.[Category Running order], Catalogue.[class running order] INTO
tempCatalogue
FROM Catalogue;

Is there any way that I can achieve this result without creating a new table
eg by using an update query? I tried simply using the same expression in an
update query:

UPDATE Catalogue SET Catalogue.CatalogueNo = (Select Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid);

But I get an error "Operation must use an updatable query. (Error 3073)",
even though the Catalogue number is not part of a relationship.

Am I going about this the wrong way? Should I be trying to use code??!!

Many thanks

Helen

Helen,

Add a new field to Catalogue, JudgingID (or some such). Since math is
unlikely, just make it a TEXT type field (size should be "wide" enough
to accomodate the biggest number you expect plus at least 1, for
future expansion).

Air Code (which I pound out on the keyboard 3 minutes before leaving
for work):


public function LoadJudgingID() as string

dim db as dao.recordset
dim rs as dao.recordset
dim sql as string
dim loopcounter as long

sql = "SELECT * FROM Catalogue;"

set db = currentdb()
set rs = db.openrecordset(sql)

with rs
.movefirst
do until .eof
loopcounter = loopcounter + 1
.fields("JudgingID").value = loopcounter
.movenext
loop
end with

rs.close
db.close
rs = nothing
db = nothing

end function

Run the function you develop from the above whenever you wish to set
the IDs for your animals.

Totally Untested (flying out the door . . .).


Sincerely,

Chris O.

 

[Guest]

Background: I am creating a catalogue for entries into a livestock show.
The numbering can only be done once all the entries are recieved - they then
need sorting into order (ie into the sections and classes within the section
and then eg into DOB of the exhibitor), after this they need a number
assigned to them which will be printed in a catalogue, and used for judging.

Up till now I have achieved this by creating the catalogue number when
creating a new table, then deleting all the records from the "real" catalogue
table and then copying all the records back ie -

SELECT Catalogue.ClassEntryID, Catalogue.EntryID, Catalogue.ClassId, (Select
Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid) AS CatalogueNo,
Catalogue.[Category Running order], Catalogue.[class running order] INTO
tempCatalogue
FROM Catalogue;

Is there any way that I can achieve this result without creating a new table
eg by using an update query? I tried simply using the same expression in an
update query:

UPDATE Catalogue SET Catalogue.CatalogueNo = (Select Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid);

But I get an error "Operation must use an updatable query. (Error 3073)",
even though the Catalogue number is not part of a relationship.

Am I going about this the wrong way? Should I be trying to use code??!!

Many thanks

Helen

Helen,

Add a new field to Catalogue, JudgingID (or some such). Since math is
unlikely, just make it a TEXT type field (size should be "wide" enough
to accomodate the biggest number you expect plus at least 1, for
future expansion).

Air Code (which I pound out on the keyboard 3 minutes before leaving
for work):


public function LoadJudgingID() as string

dim db as dao.recordset
dim rs as dao.recordset
dim sql as string
dim loopcounter as long

sql = "SELECT * FROM Catalogue;"

set db = currentdb()
set rs = db.openrecordset(sql)

with rs
.movefirst
do until .eof
loopcounter = loopcounter + 1
.fields("JudgingID").value = loopcounter
.movenext
loop
end with

rs.close
db.close
rs = nothing
db = nothing

end function

Run the function you develop from the above whenever you wish to set
the IDs for your animals.

Totally Untested (flying out the door . . .).


Sincerely,

Chris O.

Thanks Chris, but I can't seem to get this to work...

firstly I have dropped the dao in the declarations ie I have :
Dim db As Recordset
Dim rs As Recordset

But now I am stuck at the line: Set rs = db.Openrecordset(sql) I get the
error:

Method or data member not found (Error 461)

I Know it is one way of learning - but I hate trying to debug these things
when you don't really know what you are doing - any thoughts???

 

[Guest]

Background: I am creating a catalogue for entries into a livestock show.
The numbering can only be done once all the entries are recieved - they then
need sorting into order (ie into the sections and classes within the section
and then eg into DOB of the exhibitor), after this they need a number
assigned to them which will be printed in a catalogue, and used for judging.

Up till now I have achieved this by creating the catalogue number when
creating a new table, then deleting all the records from the "real" catalogue
table and then copying all the records back ie -

SELECT Catalogue.ClassEntryID, Catalogue.EntryID, Catalogue.ClassId, (Select
Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid) AS CatalogueNo,
Catalogue.[Category Running order], Catalogue.[class running order] INTO
tempCatalogue
FROM Catalogue;

Is there any way that I can achieve this result without creating a new table
eg by using an update query? I tried simply using the same expression in an
update query:

UPDATE Catalogue SET Catalogue.CatalogueNo = (Select Count(*) FROM catalogue A
WHERE A.classentryid <=catalogue.classentryid);

But I get an error "Operation must use an updatable query. (Error 3073)",
even though the Catalogue number is not part of a relationship.

Am I going about this the wrong way? Should I be trying to use code??!!

Many thanks

Helen

Helen,

Add a new field to Catalogue, JudgingID (or some such). Since math is
unlikely, just make it a TEXT type field (size should be "wide" enough
to accomodate the biggest number you expect plus at least 1, for
future expansion).

Air Code (which I pound out on the keyboard 3 minutes before leaving
for work):


public function LoadJudgingID() as string

dim db as dao.recordset
dim rs as dao.recordset
dim sql as string
dim loopcounter as long

sql = "SELECT * FROM Catalogue;"

set db = currentdb()
set rs = db.openrecordset(sql)

with rs
.movefirst
do until .eof
loopcounter = loopcounter + 1
.fields("JudgingID").value = loopcounter
.movenext
loop
end with

rs.close
db.close
rs = nothing
db = nothing

end function

Run the function you develop from the above whenever you wish to set
the IDs for your animals.

Totally Untested (flying out the door . . .).


Sincerely,

Chris O.

Thanks Chris, but I can't seem to get this to work...

firstly I have dropped the dao in the declarations ie I have :
Dim db As Recordset
Dim rs As Recordset

But now I am stuck at the line: Set rs = db.Openrecordset(sql) I get the
error:

Method or data member not found (Error 461)

I Know it is one way of learning - but I hate trying to debug these things
when you don't really know what you are doing - any thoughts???

Thanks for your "Air code" I had to add in the DAO library (In the Visual
Basic Editor:Tools>References, then find "Microsoft DAO x.x Object Library"
and check it's box.) and add in a couple of extra lines (edit and update are
quite useful :-0 ) so here is my working code!!

Public Function CalcCatNo() As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

Dim sql As String
Dim loopcounter As Long

sql = "SELECT Catalogue.* FROM Catalogue;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql)

With rs
.MoveFirst
Do Until .EOF
loopcounter = loopcounter + 1
.Edit
.Fields("CatalogueNo").Value = loopcounter
.Update

.MoveNext
Loop
End With

rs.Close
db.Close


End Function

If you are around perhaps you could just answer a couple of worries - I left
out the last 2 lines of your code ie
rs = nothing
db = nothing
Access "didn't like" them (Invalid use of Property) and I didn't know what
they did.

Do I need to do anything else about errors?

Many thanks for the help so far