Help my function and calling this function

[mdbBound]

Please tell me what is wrong with the following code and how can i best accomplish the following.

I a created a module (NextNum) with the 3 functions. I want to call these functions in 3 different forms. My goal is (1) to get the next available autonumber, (2)Convert that from Long to Text, (3) Display the Value in a textbox located in a form. "Your next available number is:"

Code in NextNum module:
Option Compare Database
Dim WNum As Long
Dim ENum As Long
Dim GNum As Long

Public Function NextW()
WNum = Nz(DMax("[WLogNo]", "stLogW"), 0) + 1
End Function

Public Function NextE()
ENum = Nz(DMax("[ELogNo]", "stLogE"), 0) + 1
End Function

Public Function NextG()
GNum = Nz(DMax("[GLogNo]", "stLogG"), 0) + 1
End Function
********************************
In one of my forms I have the following code:

Dim AvailW As String
Dim UW As String

Private Sub Command0_Click()

Call NextW
AvailW = CStr(NextWSJV)
WText.txt = AvailW

End Sub

When I click the button I get the following message:
"Member already exist in an object module form which this object module derives."

Please help. I just starting to learn using functions but I have to make this work ASAP.

Thanks in advance

 

[DALeffler]

Try this in your module:

Public Function fncNextW() as Long
fncNextW = Nz(DMax("[WLogNo]", "stLogW")) + 1
End Function

Public Function fncNextE() as Long
fncNextE = Nz(DMax("[ELogNo]", "stLogE")) + 1
End Function

Public Function fncNextG() as Long
fncNextG = Nz(DMax("[GLogNo]", "stLogG")) + 1
End Function

And this behind the command button:

Private Sub Command0_Click()

Me.txtMyTextBxW = fncNextW()
Me.txtMyTextBxE = fncNextE()
Me.txtMyTextBxG = fncNextG()

End Sub

hth,

 

[Pat Hartman]

You said that your id's were stored as text. You can't add to a text field, you need to convert it to numeric.

Public Function NextW()
WNum = CLng(Nz(DMax("[WLogNo]", "stLogW"), 0)) + 1
End Function

Public Function NextE()
ENum = CLng(Nz(DMax("[ELogNo]", "stLogE"), 0)) + 1
End Function

Public Function NextG()
GNum = CLng(Nz(DMax("[GLogNo]", "stLogG"), 0)) + 1
End Function

DALeffler, why would you remove the 0's from the Nz()'s? Without them, nulls will be returned as zero-length strings since the No fields are defined as text.

 

[DALeffler]

Hi, Pat.

DALeffler, why would you remove the 0's from the Nz()'s? Without them, nulls will be returned as zero-length strings since the No fields are defined as text.

Because of this (from the Nz function of the Acc97 help file):

For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.

 

[mdbBound]

You said that your id's were stored as text. You can't add to a text field, you need to convert it to numeric.

Public Function NextW()
WNum = CLng(Nz(DMax("[WLogNo]", "stLogW"), 0)) + 1
End Function

Public Function NextE()
ENum = CLng(Nz(DMax("[ELogNo]", "stLogE"), 0)) + 1
End Function

Public Function NextG()
GNum = CLng(Nz(DMax("[GLogNo]", "stLogG"), 0)) + 1
End Function

DALeffler, why would you remove the 0's from the Nz()'s? Without them, nulls will be returned as zero-length strings since the No fields are defined as text.

Hi Pat

thanks. My function returns a Long (it is the autonumber). I convert it to text or string when I show it to my form and also when I append/update it to a field in a table. These all works fine but I am just concerned about this Dmax scheme in a multiuser environment.

By the way I hope you will share some thoughts on my other post "How do Modules, public Functions, public variables behave after the split FE/BE and in a multiuser environment.

Thanks, you've always been a great help.

 

[Pat Hartman]

Doug,

For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.

- yes but - when varX is a string, the Nz() without a second parameter will return a zero-length string. It is only when varX is numeric that Nz() without a second parameter will return 0. That is why I prefer to specify what I want returned when my first argument is null. You can specify whatever you want. You are not limited to 0 or "":

Nz(SomeField, "Empty") - will return the string "Empty" when SomeField is null. Otherwise, it will return the value of SomeField.

mdbBound,

My function returns a Long

- the code you posted doesn't actually return anything. I didn't notice that earlier. When you write a function, you should have some statement that gives the function a return value.

Public Function NextW() As Long
NextW = CLng(Nz(DMax("[WLogNo]", "stLogW"), 0)) + 1
End Function

Then in code you would say:

Somefield = NextW()

This will calculate the Next W value and place it in SomeField.

 

[mdbBound]

Thanks Pat, It's taken care of.