Hep with update query and Dmax

[Tony Williams]

I have a field that I want to populate each record with a consecutive number.
I'm trying to do this with an update query with this as the sql view

UPDATE Semita SET Semita.SEMITAREFNBR = DMax("[SEMITAREFNBR]","semita")+1;

However when I run the query the value of my field for every record is 1.
What am I doing wrong?
Thanks
Tony

 

[Michel Walsh]

Since DMAX arguments are 'constants', as least, as seen by the query
optimizer, it is executed just once, at the start of the query, and 'cached'
locally to be retrieved as required further on, in the query execution.

Unfortunately, since the update query is wrapped into some kind of internal
transaction, EVEN if you make, say, the third parameter, dependant of some
field, the way Jet works, you would still get just one value, repeated for
all rows.

The possibilities left are either to rank your data, either to append (the
actual key) into an empty table WHICH has an autonumber field.

To rank your data, that depends if you have, or not, duplicated values (and
if you have, what you do then, ie: with: 10, 20, 20, 30, will you say
first, second, second, fourth,... or first, third, third, fourth, ... or
first, second, second, third, ... etc).



Vanderghast, Access MVP

 

[John Spencer (MVP)]

The problem is that DMAX gets calculated ONCE and even if it did get
recalculated every time it wouldn't change since the new values aren't
committed to the table until the query has executed.

The solution may be that you will have to use VBA and step through the records
in a recordset and update each record in the record set - one at a time.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tony Williams]

Thanks Michael back to the drawing board
Tony

Since DMAX arguments are 'constants', as least, as seen by the query
optimizer, it is executed just once, at the start of the query, and 'cached'
locally to be retrieved as required further on, in the query execution.

Unfortunately, since the update query is wrapped into some kind of internal
transaction, EVEN if you make, say, the third parameter, dependant of some
field, the way Jet works, you would still get just one value, repeated for
all rows.

The possibilities left are either to rank your data, either to append (the
actual key) into an empty table WHICH has an autonumber field.

To rank your data, that depends if you have, or not, duplicated values (and
if you have, what you do then, ie: with: 10, 20, 20, 30, will you say
first, second, second, fourth,... or first, third, third, fourth, ... or
first, second, second, third, ... etc).



Vanderghast, Access MVP

I have a field that I want to populate each record with a consecutive
number.
I'm trying to do this with an update query with this as the sql view

UPDATE Semita SET Semita.SEMITAREFNBR = DMax("[SEMITAREFNBR]","semita")+1;

However when I run the query the value of my field for every record is 1.
What am I doing wrong?
Thanks
Tony

 

[Tony Williams]

Thanks John.

The problem I have is that I will have to import from Excel a number of
records on a daily basis. The default value of the control is a Dmax
expression and, if I create a new record using the form, it works fine.
However if I import a number of records then the value of the field doesn't
increase. I thought that I could run a query to update the field, which is
the record source of the control, after the import has happened. Obviously
not!

I'm very much a novice at VBA but could find my way around if you could
start me of on some example code which I could put behind a command button,
that would step through the new records I've imported and create a value for
the field based upon it being 1 higher than the current highest value. The
table name is semita and the filed name is semitarefnbr.

Would you mind helping me with that?
Many thanks
Tony

The problem is that DMAX gets calculated ONCE and even if it did get
recalculated every time it wouldn't change since the new values aren't
committed to the table until the query has executed.

The solution may be that you will have to use VBA and step through the records
in a recordset and update each record in the record set - one at a time.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a field that I want to populate each record with a consecutive number.
I'm trying to do this with an update query with this as the sql view

UPDATE Semita SET Semita.SEMITAREFNBR = DMax("[SEMITAREFNBR]","semita")+1;

However when I run the query the value of my field for every record is 1.
What am I doing wrong?
Thanks
Tony

 

[John Spencer (MVP)]

Quick and Dirty UNTESTED code with no error checking.

Public Sub fRenumber()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim lCount as Long

Set dbAny = Currentdb()
Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")

With rstAny
LCount = DMax("[SEMITAREFNBR]","semita")+1
If .RecordCount > 0 Then
Do Until .EOF
.Edit
.Fields("SEMITAREFNBR") = 1
.Update
LCount = lCount +1
Loop

End If
End With

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John.

The problem I have is that I will have to import from Excel a number of
records on a daily basis. The default value of the control is a Dmax
expression and, if I create a new record using the form, it works fine.
However if I import a number of records then the value of the field doesn't
increase. I thought that I could run a query to update the field, which is
the record source of the control, after the import has happened. Obviously
not!

I'm very much a novice at VBA but could find my way around if you could
start me of on some example code which I could put behind a command button,
that would step through the new records I've imported and create a value for
the field based upon it being 1 higher than the current highest value. The
table name is semita and the filed name is semitarefnbr.

Would you mind helping me with that?
Many thanks
Tony

The problem is that DMAX gets calculated ONCE and even if it did get
recalculated every time it wouldn't change since the new values aren't
committed to the table until the query has executed.

The solution may be that you will have to use VBA and step through the records
in a recordset and update each record in the record set - one at a time.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a field that I want to populate each record with a consecutive number.
I'm trying to do this with an update query with this as the sql view

UPDATE Semita SET Semita.SEMITAREFNBR = DMax("[SEMITAREFNBR]","semita")+1;

However when I run the query the value of my field for every record is 1.
What am I doing wrong?
Thanks
Tony

 

[Tony Williams]

Thanks John. It's 17.57 here in the UK and it's been a difficult day! I need
to look at this with a clear mind so I'm closing my brain down and having a
look in the morning. If I may, I will come back to you if I run into any
problems.
Thanks again
Tony

Quick and Dirty UNTESTED code with no error checking.

Public Sub fRenumber()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim lCount as Long

Set dbAny = Currentdb()
Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")

With rstAny
LCount = DMax("[SEMITAREFNBR]","semita")+1
If .RecordCount > 0 Then
Do Until .EOF
.Edit
.Fields("SEMITAREFNBR") = 1
.Update
LCount = lCount +1
Loop

End If
End With

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John.

The problem I have is that I will have to import from Excel a number of
records on a daily basis. The default value of the control is a Dmax
expression and, if I create a new record using the form, it works fine.
However if I import a number of records then the value of the field doesn't
increase. I thought that I could run a query to update the field, which is
the record source of the control, after the import has happened. Obviously
not!

I'm very much a novice at VBA but could find my way around if you could
start me of on some example code which I could put behind a command button,
that would step through the new records I've imported and create a value for
the field based upon it being 1 higher than the current highest value. The
table name is semita and the filed name is semitarefnbr.

Would you mind helping me with that?
Many thanks
Tony

The problem is that DMAX gets calculated ONCE and even if it did get
recalculated every time it wouldn't change since the new values aren't
committed to the table until the query has executed.

The solution may be that you will have to use VBA and step through the records
in a recordset and update each record in the record set - one at a time.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
I have a field that I want to populate each record with a consecutive number.
I'm trying to do this with an update query with this as the sql view

UPDATE Semita SET Semita.SEMITAREFNBR = DMax("[SEMITAREFNBR]","semita")+1;

However when I run the query the value of my field for every record is 1.
What am I doing wrong?
Thanks
Tony

 

[Tony Williams]

Good morning John.
I tried two things:

1. I pasted your code into the VBA of the form and, as an alternative,

2. I also created a new module and pasted in your code.

In the OnClick Event of my commnad button I put =fRenumber(). In each case I
get an error message that said the database couldn't find the function by
that name.

As I mentioned I'm a VBA novice so I could have got this completely wrong.
Can you help?

Many thanks
Tony

Quick and Dirty UNTESTED code with no error checking.

Public Sub fRenumber()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim lCount as Long

Set dbAny = Currentdb()
Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")

With rstAny
LCount = DMax("[SEMITAREFNBR]","semita")+1
If .RecordCount > 0 Then
Do Until .EOF
.Edit
.Fields("SEMITAREFNBR") = 1
.Update
LCount = lCount +1
Loop

End If
End With

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John.

The problem I have is that I will have to import from Excel a number of
records on a daily basis. The default value of the control is a Dmax
expression and, if I create a new record using the form, it works fine.
However if I import a number of records then the value of the field doesn't
increase. I thought that I could run a query to update the field, which is
the record source of the control, after the import has happened. Obviously
not!

I'm very much a novice at VBA but could find my way around if you could
start me of on some example code which I could put behind a command button,
that would step through the new records I've imported and create a value for
the field based upon it being 1 higher than the current highest value. The
table name is semita and the filed name is semitarefnbr.

Would you mind helping me with that?
Many thanks
Tony

The problem is that DMAX gets calculated ONCE and even if it did get
recalculated every time it wouldn't change since the new values aren't
committed to the table until the query has executed.

The solution may be that you will have to use VBA and step through the records
in a recordset and update each record in the record set - one at a time.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
I have a field that I want to populate each record with a consecutive number.
I'm trying to do this with an update query with this as the sql view

UPDATE Semita SET Semita.SEMITAREFNBR = DMax("[SEMITAREFNBR]","semita")+1;

However when I run the query the value of my field for every record is 1.
What am I doing wrong?
Thanks
Tony

 

[John Spencer (MVP)]

Well, if you are going to call it that way, you need to change the SUB to a
FUNCTION. Just as the error says it can't find the FUNCTION by that name.

Also, make sure you backup your data FIRST. The function could work, but it
could do something other than what you want/expect.

Public FUNCTION fRenumber()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim lCount as Long

Set dbAny = Currentdb()
Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")

With rstAny
LCount = DMax("[SEMITAREFNBR]","semita")+1
If .RecordCount > 0 Then
Do Until .EOF
.Edit
.Fields("SEMITAREFNBR") = 1
.Update
LCount = lCount +1
Loop

End If
End With

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tony Williams]

The first time I ran it nothing happened. I looked at the table and realised
that as the format of this field was a number the default value for the
imported records was 0, which I assume is not the same as Null. So I deleted
the 0's for those new records and ran the code again. A step forward, the
error message has gone. However what happens now is this I imported 18 new
records. The last number used for semitarefnbr was 227695. The first new
record was allocated with the number 1 and then Access hung.

I think we're moving in the right direction but I'm lost as to where I
should go from here. Can you help?
Thanks for sticking with me John.
Tony

Well, if you are going to call it that way, you need to change the SUB to a
FUNCTION. Just as the error says it can't find the FUNCTION by that name.

Also, make sure you backup your data FIRST. The function could work, but it
could do something other than what you want/expect.

Public FUNCTION fRenumber()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim lCount as Long

Set dbAny = Currentdb()
Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")

With rstAny
LCount = DMax("[SEMITAREFNBR]","semita")+1
If .RecordCount > 0 Then
Do Until .EOF
.Edit
.Fields("SEMITAREFNBR") = 1
.Update
LCount = lCount +1
Loop

End If
End With

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Good morning John.
I tried two things:

1. I pasted your code into the VBA of the form and, as an alternative,

2. I also created a new module and pasted in your code.

In the OnClick Event of my commnad button I put =fRenumber(). In each case I
get an error message that said the database couldn't find the function by
that name.

As I mentioned I'm a VBA novice so I could have got this completely wrong.
Can you help?

Many thanks
Tony

 

[Tony Williams]

Partial Success!!!!!

I looked through a couple of VBA text books I had and found something
similar in a book "Access 2002 Programmimg by Example" and ended up with this:

Public Function fRenumber()
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim lCount As Long

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")


With rstAny

If .RecordCount > 0 Then
Do While Not rstAny.EOF
rstAny.Edit
rstAny![SEMITAREFNBR] = DMax("[SEMITAREFNBR]", "Semita") + 1
rstAny.Update
rstAny.MoveNext

Loop

End If
End With

End Function

However this only works if the value in the field is Null and as I mentioned
in the last post, as the format is a number the default value in the field is
0. So I need to work out how to delete the value of the field to get Null and
then run the code.

The other step I want to try is to get all three steps behind 1 control
button rather than have 3 seperate command buttons ie triggering the import
wizard, delete the zero then run the update code.

Any help would be appreciated

Well, if you are going to call it that way, you need to change the SUB to a
FUNCTION. Just as the error says it can't find the FUNCTION by that name.

Also, make sure you backup your data FIRST. The function could work, but it
could do something other than what you want/expect.

Public FUNCTION fRenumber()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim lCount as Long

Set dbAny = Currentdb()
Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")

With rstAny
LCount = DMax("[SEMITAREFNBR]","semita")+1
If .RecordCount > 0 Then
Do Until .EOF
.Edit
.Fields("SEMITAREFNBR") = 1
.Update
LCount = lCount +1
Loop

End If
End With

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Good morning John.
I tried two things:

1. I pasted your code into the VBA of the form and, as an alternative,

2. I also created a new module and pasted in your code.

In the OnClick Event of my commnad button I put =fRenumber(). In each case I
get an error message that said the database couldn't find the function by
that name.

As I mentioned I'm a VBA novice so I could have got this completely wrong.
Can you help?

Many thanks
Tony

 

[John Spencer (MVP)]

Change the recordset line to

Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null or SEMITAREFNBR = 0")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tony Williams]

Thanks John
Tony

Change the recordset line to

Set rstAny = dbAny.OpenRecordset ("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null or SEMITAREFNBR = 0")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Partial Success!!!!!

I looked through a couple of VBA text books I had and found something
similar in a book "Access 2002 Programmimg by Example" and ended up with this:

Public Function fRenumber()
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim lCount As Long

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset("SELECT SEMITAREFNBR FROM Semita WHERE
SEMITAREFNBR is Null")


With rstAny

If .RecordCount > 0 Then
Do While Not rstAny.EOF
rstAny.Edit
rstAny![SEMITAREFNBR] = DMax("[SEMITAREFNBR]", "Semita") + 1
rstAny.Update
rstAny.MoveNext

Loop

End If
End With

End Function

However this only works if the value in the field is Null and as I mentioned
in the last post, as the format is a number the default value in the field is
0. So I need to work out how to delete the value of the field to get Null and
then run the code.