Incrementing a field

F

Fred

I have 3 tables: People, related to 1 or more addresses
related to 1 or more descriptive entries.

I have a form that displays the people data, with a
subform form that presents the address entries an another
subform that displays the descriptive data for each
address.

Everything works fine. However, the descriptive records
have a sequence number field (non-key) in them with
default value 1. I would like that field to increment
for each subsequent entry for the address. The prime key
to this table is an autonumber ID, ant there is a foreign
key to the address table.

Can I do this?

Fred
 
A

Arvin Meyer

Sure, just build a table (tblNextNumber) with one field (JobNumber) and one
record to seed it. Then in a Standard module:

Public Function GetNextJobNumber() As Long
On Error GoTo Error_Handler
Dim DB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select JobNumber From tblNextNumber"

Set DB = CurrentDb
Set rst = DB.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst
GetNextJobNumber = !JobNumber
.Edit
!JobNumber = !JobNumber + 1
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Function

Error_Handler:
Call ErrorLog("basUtilities", "GetNextJobNumber")
Resume Exit_Here

End Function

Public Function ErrorLog(objName As String, routineName As String)
Dim DB As DAO.Database

Set DB = CurrentDb

Open "C:\Error.log" For Append As #1

Print #1, Format(Now, "mm/dd/yyyy, hh:nn:ss") & ", " & DB.Name & vbCrLf & _
"An error occured in: " & objName & ", Procedure: " & routineName &
vbCrLf & _
"User: " & CurrentUser() & ", Error#: " & Err.Number & ": " &
Err.Description

Close #1
End Function

Use an event to call GetNextJobNumber() and your there.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
F

Fred

Arvin, thank you.

If I understand what you're doing here, you've got one
field that you'r incrementing for the whole database. So,
if I have 150 jobs, regardless of address, the next will
be 151.
What I want to do is sequence the jobs for each address--
e.g., address A has 2 jobs and Address B has 4. I want
the next job for Address A to be 3 and the next job for
address B to be 5--and I want the subform where I add the
jobs to show the next number in the job field by default.

Does that make more sense?
 
A

Arvin Meyer

OK, Then what you will need to do is parse out the number if it is part of a
longer number, or just use the number itself, if it is being used like any
other autonumber. You'll need to segregate the Addresses into A, B, C, etc.
with a where clause so that you'll know which address to increment. Use
DMax() to return the highest value, something like (aircode):

Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = True Then
Dim x
x = DMax("JobNumber", "tblWhatever", "Address = '" & Me.txtAddress &"'")
Me.txtJobNumber = x+1
End If
End Sub

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top