Dlast function "stops working"

[hognabbt]

Hello,

I have faced some weird challenges with the Dlast function I am using in a form.

Private Sub txtHonaID_Exit(Cancel As Integer)
Dim BurNr As Integer

BurNr = Nz(DLast("BurNr", "tblParn2010") + 1, 1)
Me.cboBurNr = BurNr
End If
End Sub

This is basically the event from the form. So what happens is that the form looks for the last "BurNr" field in the last inserted record in table [tblParn2010], and adds +1, and inserts it into my Combobox "cboBurNr" inside the form. If the latest number was 40, the next record to be inserted will get 41 as value for field "BurNr".

This usually works perfect, but at some point entering data to the form the number can stop updating. The number can e.g. stop at 35 and all the records entered after that will be 35. Even if i manually go into the table and change the latest value to e.g. 50, the form anyhow picks up 35. It feels like the (in this case) number "34" somehow is stored in the memory of the Dlast function.

I have to point out that I'm not trying to achieve an autonumber system with this function. It is neither anything that needs to be unique (BurNr field). I want to have it running in the form so i can manually change it whenever I want e.g. to 500, and the form picks it up and continues with 501, 502 etc.

Anyone know what could cause this problem, or is there perhaps a better function to use?

 

[JamesMcS]

Does this make a difference?

BurNr = Nz(DMax("BurNr", "tblParn2010"),0) + 1

I changed the value if null of the Nz to 0 otherwise you'd get a 2 (with the +1)

 

[hognabbt]

>Does this make a difference? Code:
> BurNr = Nz(DMax("BurNr", "tblParn2010"),0) + 1
>I changed the value if null of the Nz to 0 otherwise you'd get a 2 (with >the +1)

Thanks for your reply. I am not able to use DMax function, because the order in which I add data may vary. Lets say one day I need to start from 500 in the "BurNr" field, and thereby the new records get values like 501, 502, 503 etc. Later on I might need to start again e.g. from 40, 41, 42 etc. DMax will not allow this, since it will return me 503.

As I said earlier, usually this DLast function works exactly as I want it to do. But suddenly it can "stop running", and I don't know the reason why.

Btw, I then use a control button which runs a "SQL Insert into" command to select the values I have entered into the comboboxes in the form and transfer them into the table [tblParn2010].

 

[JamesMcS]

I can't figure it out either, apologies....

 

[vbaInet]

Here's an excerpt from the Help Files re DLast:

"You can use the DLast function to return a random record from a particular field in a table or query when you simply need any value from that field."

 

[LPurvis]

Well, that's the Help files being a little unfair and misleading.
What it probably means is that it will "return what may appear as a random record".
There's little you can do to control which row's value is returned - but it's not really random per se.

 

[namliam]

Unless you add a sorting/ordering in any query (or D command) any Last/first is going to be semi random a lot of times right but random regardless.

i.e. More or less just confirming that what INet said.

 

[LPurvis]

Even ordering won't actually make an impact on the First/Last functions.
They relate the the engine's fetching of data rather than the presented order - hence the lack of control over their results.

Cheers.

 

[hognabbt]

Thanks for all your answers. Since the DLast function seems to be a little unstable, maybe I need to take a little different approach. I have an autonumber field also in the table. I could look for the highest value in the Autonumber field and read the "BurNr" value for that record. DMax or a SQL SELECT statement should probably do the trick. I quess this should probably give me the LATEST inserted value, and not any "Random" value...

 

[vbaInet]

A combination of the Autonumber field and a Date/Time field would do the trick.

 

[JamesMcS]

Yeah. I was thinking of a date field to add into the WHERE of the DSum statement, "but what about if the OP wanted to change during the course of the day?" I thought.... but time/autonumber will sort that out!