Incrementing a field

  • Thread starter Thread starter Fred
  • Start date Start date
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
 
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
 
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?
 
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
 
Back
Top