Sequential Numbering When Printing Forms

M

MCM Alarms

Hi,
I have a form set up for service calls for my customers to which I can call
in their details and print a service call sheet ready for the engineer to
take to site.
I need a way to insert an increasing serial number on to the printed sheet
so I can track each job. This will also help me track the number of sheets I
have printed.

Thanks

Matt
 
T

Tom van Stiphout

On Sun, 4 Nov 2007 12:58:02 -0800, MCM Alarms <MCM
[email protected]> wrote:

Doesn't each Job record already have a JobID, perhaps an AutoNumber?
That would be ideal as a unique number.

-Tom.
 
M

MCM Alarms

No, as it stands it is a form generated from a master list of names and
addresses, you can scroll through the list of contacts and the information
appears in the form. The ID field is relevant to the contact table not the
form, therefor I am current just printing identical forms each time a job is
done. Do I need to add a field from another table which creates and logs Job
Numbers??
 
B

Bob Barnes

You can write a function such as...

Public Function EnterNewOne()
Dim Z As Database, RS As DAO.Recordset, M$, N$, Q$, L&
Set Z = CurrentDb
Set RS = Z.OpenRecordset("EnterAction", dbOpenDynaset)
With RS
If Right(!GoSequence, 6) = "999999" Then
.Edit: !GoSequence = "A000000": .Update
Else
M = Right(!GoSequence, 6)
L = CLng(M)
L = L + 1
N = Format(L, "000000")
.Edit: !GoSequence = "A" & N: .Update
End If
EnterNewOne = !GoSequence
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With
End Function
 
M

MCM Alarms

Hi,
How would I go about adding this to my form. ( I have added the function in
the visual basic window and it apears in the list of functions in the build
event window)
Thanks
 
B

Bob Barnes

Add it in the Form's module..View - Design....View - Code.

"TheInvoceNumber Fieldname you're using" = EnterNewOne.

You'll need a Table "EnterAction" to store the Field "GoSequence" used in
the Function.

I have to attend a family gathering, and will be online tomorrow AM..I'm
Kentucky USA.

HTH - Bob
 
M

MCM Alarms

Hi I am getting a Compile Error on the Dim Z = Database Line (User defined
type not defined)

What have I done wrong
 
B

Bob Barnes

Be sure it's Dim Z As Database.

...and in your module, Tool - references has checked...
Microsoft DAO 3.6 Object Library.

Some will have Dim Z As DAO.Database, but I haven't ahd to do that in DAO,
and it works fine.

Let me know - Bob
 
M

MCM Alarms

Hi,

Yes nearly there now the reference wasnt checked. The text box I have for
the Job Number shows #Name?. The counter is increasing in the EnterAction
Table properly have I linked them incorrectly?
 
B

Bob Barnes

If you are using a bound Form, the #Name? may mean the Field you're
referencing isn't in the Form's recordsource.
 
Top