Right in Dmax 3

[lars7]

Hi,
I have the following code to give me the max of a "codeno" in a field but the first part of the "codeno" is text i,e, DIAG1000

Countmax = DMax("Right([Job Ref No],4)", "[Vac Board]")
Me.[Job Ref No] = Countmax + 1

my problem is that the number part has reached 9999 and if i change the code to:

Countmax = DMax("Right([Job Ref No],5)", "[Vac Board]")
Me.[Job Ref No] = Countmax + 1

I get a type mismatch.

Does anyone know how i can fix this.

Thanks in advance.

 

[PHV]

the prefix can be 4 or 5 digits
Countmax = Val(DMax("Mid([Job Ref No],IIf([Job Ref No] Like '????#',5,6))", "[Vac Board]"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Hi PH,

I used set up some test conditions to try out your code and I'm getting some duplicate numbers created. Here is the code I'm using:

Private Sub Command0_Click()

DoCmd.GoToRecord , , acNewRec

Countmax = Val(DMax("Mid([Job Ref No],IIf([Job Ref No] Like '????#',5,6))", "[Vac Board]"))
Me.Prefix = ""
Me.NumGen = Countmax + 1

DoCmd.GoToControl "Prefix"

End Sub

and the results in my table:

Vac Board
job ref no
GDIAG10000
GDIAG10002
GPPSU10001
GWACC10003
PPSU10001
WACC10002


It seems that the number only goes up if the prefix has 5 letters.

 

[TheAceMan1]

OK lars7 . . .

On the premise the user determines the prefix to use, the prefix can be passed to the function:

[blue]Public Function NextSerialNum([purple][b][i]PreFix[/i][/b][/purple] As String) As String
   Dim Ans As Long
   
   Ans = DMax("Val(Right([Job Ref No], 5))", "Vac Board")
   
   If Ans = 0 Then
      Ans = DMax("Val(Right([Job Ref No], 4))", "Vac Board")
   End If
   
   NextSerialNum = [purple][b][i]PreFix[/i][/b][/purple] & Ans + 1
   
End Function[/blue]

. . . ad the call:

[blue]   NextSerialNum("GWAC")[/blue]

You should get:
[blue]GDIAG9999
GDIAG10000
GPPSU10001
GWACC10002
PPSU10003
WACC10004[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Zion7]

PHV forgot the asterix

...Like '????#*',5,6...

 

[lars7]

Hi Ace,

thanks that worked great.


job ref no
GPPSU10000
GPPSU10001
GPPSU10004
GRADS10005
GWACC10003
GWACC10007
PPSU10002
WACS10006

I think I understand how this works apart from this bit:

("GWAC")

could you explain please?

 

[lars7]

Thanks Guys,

Looks like I have 2 options on Monday.

 

[Zion7]

Lars7, If AceMan doesn't mind me answering on behalf of him,
("GWAC") is the argument required for Ace's function,
(the parameter value).
BUT, I think AceMan was under the impression the User
inputs the prefix, prior to running the code & obtaining
the next sequential number.
Is this the intent Lars7?
I thought the current record, would determine that,
(prefix already established) ?

 

[TheAceMan1]

Zion7 . . .

Your correct in my assumption . . . It hasn't been pointed out just what the source of the prefix is.

[blue]Lars . . .[/blue]
The function doesn't know the prefix intended, so it has to be told. Your simply passing the prefix to the function so it does know!

If the source of the prefix is a textbox on the current record then the call becomes:

[blue]   NextSerialNum(Me![[purple][b][i]TextboxName[/i][/b][/purple]])[/blue]

See Ya! . . . . . .

 

[lars7]

Hi Zion,

My idea was for "GenNum" to get the reference number automatically when the new record button is pressed and the curser to appear in "Prefix" ready for the text to be added:

DoCmd.Save

DoCmd.GoToRecord , , acNewRec

Countmax = Val(DMax("Mid([Job Ref No],IIf([Job Ref No] Like '????#*',5,6))", "[Vac Board]"))

Me.Prefix = ""
Me.NumGen = Countmax + 1

DoCmd.GoToControl "Prefix"


In the after update of "Prefix" I have

Me.[job ref no] = Me.Prefix & Me.NumGen

and this works well.


With Ace's I have this when the new record button is pressed:

DoCmd.Save

DoCmd.GoToRecord , , acNewRec

Me.Prefix = ""

DoCmd.GoToControl "Prefix"

and the control source of "NumGen" as:

=NextSerialNum ("GWAC")

and again in the after update of "Prefix" I have

Me.[job ref no] = Me.Prefix & Me.NumGen


with the Public Function that works well too but I'm not sure why.

 

[lars7]

HI Guys,

Ace I get a name error with:

=NextSerialNum(Me!prefix)

do I have it in the right place?

I should have said that both Prefix and GenNum are text boxes

I hope my intentions are clearer to you from my last post.

 

[Zion7]

Lars7, If I understand correctly, we've all been barking up
the wrong tree?!

You should be doing your DMax() on [GenNum]????

no need for Right() or Left() this.

Your control source Of NumGen, you Can't use "Me"

=NextSerialNum(Forms!frmName!prefix) or
=NextSerialNum([prefix])

It's strange Ace's code is working, the way you have it set up.

Me.[job ref no] = Me.Prefix & Me.NumGen

Should equal "GWACGWAC00001"???

 

[TheAceMan1]

Zion7 . . .

Thanks for the correction I hadn't thought about [blue]controlsource[/blue].

See Ya! . . . . . .

 

[lars7]

Hi Zion,

"You should be doing your DMax() on [GenNum]????

no need for Right() or Left() this."

GenNum is an unbound texbox just to display the number part of the Job Ref No before concatenating with Prefix into Job ref no.


"It's strange Ace's code is working, the way you have it set up.

Me.[job ref no] = Me.Prefix & Me.NumGen

Should equal "GWACGWAC00001"???."

All that appears in GenNum is the number, i'e "10019" there is no prefix that is what I can't understand about the call:

=NextSerialNum ("GWAC")

but if I remove the "("GWAC")" from the code I get a name error.

But it works great.

 

[TheAceMan1]

lars7 . . .

Realize, although you have [blue]GenNum[/blue] your using [blue]DMax[/blue] to return the max number in the table! So [blue]GenNum[/blue] isn't needed.

In the [blue]AfterUpdate[/blue] event of [blue]Prefix[/blue], correct would be:

[blue]      Me.[job ref no] = NextSerialNum (Me!Prefix)[/blue]

This time Me is OK . . .

See Ya! . . . . . .

 

[lars7]

Hi Again,

I used this and again it worked but only the number part is appearing in GenNum:

=NextSerialNum([prefix])


here is the data in the table generated from using Ace's code only:


job ref no
DIAG10019
GDIAG10016
GDIAG10017
GDIAG10020
GDIAG10021
GPPSU10000
GPPSU10001
GPPSU10004
GPPSU10010
GPPSU10012
GPPSU10013
GRADS10005
GREWS10014
GWAC10009
GWACC10003
GWACS10007
GWACS10018
PPSU10002
PPSU10015
WACC10008
WACC10022
WACS10006
WACS10011

as you can see there is no duplication of Prefix.

 

[lars7]

Hi Ace,

Tried this:

Me.[job ref no] = NextSerialNum(Me!Prefix)

but only the number part went into Job Ref No.

 

[TheAceMan1]

. . . and what was the value of [blue]Me!Prefix[/blue] when executed?

See Ya! . . . . . .

 

[lars7]

Hi,

I tried a few GDIAG, GWACC and PPSU. I also tried to take the focus away first with this:


Private Sub Prefix_AfterUpdate()

'DoCmd.GoToControl "NextText"
Me.[job ref no] = NextSerialNum(Me!Prefix)

End Sub

but still the same.

 

[lars7]

Hi Ace,

This is what I have on my test form and hope works tomorrow at work.



Option Compare Database

Private Sub CommandNewRecord_Click()

DoCmd.Save

DoCmd.GoToRecord , , acNewRec


Me.Prefix.Visible = True
Me.NumGen.Visible = True
Me.RefNo.Visible = False

Me.Prefix = ""
DoCmd.GoToControl "Prefix"

End Sub

Private Sub Form_Open(Cancel As Integer)

Me.Prefix.Visible = False
Me.NumGen.Visible = False

End Sub

Private Sub Prefix_AfterUpdate()

Me.[Job Ref No] = Me.Prefix & Me.NumGen

Me.RefNo.Visible = True

DoCmd.GoToControl "NextTextBox"

Me.Prefix.Visible = False
Me.NumGen.Visible = False


End Sub

Public Function NextSerialNum(Prefix As String) As String

Dim Ans As Long

Ans = DMax("Val(Right([Job Ref No], 5))", "Vac Board")

If Ans = 0 Then
Ans = DMax("Val(Right([Job Ref No], 4))", "Vac Board")
End If

NextSerialNum = Ans + 1

End Function

RefNo --> TextBox with control source "Job Ref No"--> sample data "GWAC10012"

Prefix --> TextBox Unbound --> sample data typed in "GDIAG"

NumGen --> TextBox with control source "=NextSerialNum([Prefix])" sample data updated with Public Function --> "10023"

I have "Prefix and "NumGen" sitting on top of "RefNo" and being visible true or false at the appropriate times and it all looks ok and works fine I'm just not sure why.

 

[TheAceMan1]

lars7 . . .

For starters I've simulate what you have as fars as table [blue]Vac Board[/blue] and your [blue]Prefix[/blue] & [blue].[Job Ref No][/blue] fields are concerned. No problemo using:

[blue]Private Sub Prefix_AfterUpdate()
   Me.[job ref no] = NextSerialNum(Me!PreFix)
End Sub

Public Function NextSerialNum(PreFix As String) As String
   Dim Ans As Long
   
   Ans = DMax("Val(Right([Job Ref No], 5))", "Vac Board")
   
   If Ans = 0 Then
      Ans = DMax("Val(Right([Job Ref No], 4))", "Vac Board")
   End If
   
   NextSerialNum = PreFix & (Ans + 1)
   
End Function[/blue]

One of the problems is you didn't use this. Although told twice you didn't need [blue]GenNum[/blue] you insisted. But the biggest problem is that you didn't remove the [blue]control source[/blue] from [Job Ref No] or GenNum when you tried the code in the AfterUpdate event above! . . .

You've also introduced the additional field of [blue]RefNo[/blue] with its [blue]control source[/blue] set to [Job Ref No]! . . .

Why the duplicate data is beyond me. Your going around the block for what you can do at home. So you have two fields you don't need.

As you have it code should be:

[blue]Private Sub Prefix_AfterUpdate()
   Me.[purple][b][i]RefNo[/i][/b][/purple] = NextSerialNum(Me!PreFix)
End Sub[/blue]

Your latest code works because you removed preappending the prefix to the max number found and only return numeric to gennum.

See Ya! . . . . . .

Be sure to see FAQ219-2884: