Is there a better way

[RayC]

Just trying to find out if I am writing my code in the correct way. The Block
should be self explanitory but is there a better way to write it?


Public Function util_GetNewContractNo(a As Long) As Long

'*****************************************************************=**********************
' Name: util_GetNewContractNo :- Utility Number 2
' Purpose: Store and increment the next Contract Number
'
' Returns: Long
'
' Author:
' Date: August 07, 2006
' Comment: Stores the current Contract No and returns an incremented Number
if required

' This function works with the Table "const_tbl_NewContractNo" to find the
next Contract No
' in the sequence; retreive it, increment it and write out the new
next contract no.
' If the function receives a 0 or a Null from the calling routine, it will
return the
' stored Contract No.
' If it receives a valid Contract No from the calling routine, it will
incement the number and
' store the next incremented number in the Table ready for the next use.

'*****************************************************************=**********************

On Error GoTo Err_GetNewContractNo

Dim dbs As Database, rs88 As Recordset, a As Long
Set dbs = CurrentDb
Set rs86 = dbs.OpenRecordset("const_tbl_NewContractNo", dbOpenDynaset)

' Get last Contract Number from "const_tbl_NewContractNo", increment it and
return.
rs86.MoveLast
If a = 0 Or a = Null Then
util_GetNewContractNo = rs86("ContractNo") ' return the Contract
No stored in the Table
ElseIf a = rs86("ContractNo") Then
rs86.MoveLast: rs86.Edit
rs86("ContractNo") = a + 1 ' Save incremented last Contract No
in "NewContractNo"
rs86.Update
util_GetNewContractNo = rs86("ContractNo") ' return the Contract
No stored in the Table
Else
MsgBox "Error you don't have the correct Contract Number",
vbOKOnly, "ERROR"
util_GetNewContractNo = 0 ' return 0 to
signify the error
End If


Exit_GetNewContractNo:
rs86.Close: dbs.Close
Exit Function


Err_GetNewContractNo:
Select Case Err

Case 0


Case Else
MsgBox Err.Description
Resume Exit_GetNewContractNo
End Select

End Function

Thanks for any pointers to get me in the right direction.

RayC

 

[Duane Hookom]

Just a quick look suggests:
Use
Dim dbs As DAO.Database, rs88 As DAO.Recordset, a As Long
MoveLast on an unordered recordset isn't reliable.

You have "a" as an argument in the function and then you Dim it in the code.
This generally creates an error.
Since "a" is dimmed always as Long, it will never be Null. Plus you can't
test for Null with "a = Null". You have to use a function like IsNull(a).
I would also try to be more descriptive than "a". All of your other code
seems to have better naming.

I prefer to not place more than a single statement in a line of code
"rs86.MoveLast: rs86.Edit"

 

[RayC]

Thanks Duane, just a couple of quick questions.
Whilst I am sure that it is relevant, I don't understand about DAO and
therefor, that is why it is not in my code. Cooulg you expand or point me in
a direction that will explain DAO.

a dimed in code, I undersatand. error on my part.

Did not know that by declaring a as long, it precluded any reference to Null
and that is a usefull piece of information. In this particular instance, does
it matter and should I change things. If so, what do you suggest?

I take your point with naming "a" but I get lazy sometimes.

I take your point about concatenating lines of code but I have an
overwhelming compulsion to compress my code. I know that it does not make any
difference to the program or the way it runs but I seem to have to do it. I
will try to avoid that in future.

I very much appreciate your help and comments
Regards RayC

 

[Duane Hookom]

There are many different code libraries used in Access/VBA. If you open any
module and select Tools->References, you will find a list of available
libraries/dlls/activeX,... The checked libraries are possibly used by your
current mdb. The DAO and ADO object libraries both use a "Recordset" object.
This has caused many issues when developers have converted older apps to
newer versions of Access.

If the ADO library was checked in your references and was listed higher than
DAO, you might have generated an error.