Automatic Number on save and Concatenation

  • Thread starter Thread starter martha.dempsey
  • Start date Start date
M

martha.dempsey

Need help setting an account number to automatically
populate with the next number, i.e. Record #1 seq # 44-
231, Record #2 seq# 44-232, so on ONLY if the record is
saved (using a command button)

Next, I need to know how to concatenate a dept field with
the seq # to give me a unique number. Any suggestions?

Thanks!
martha
 
Easiest way would be to simply create an AUTONUMBER field and use that as
the Primary Key, then you wouldn't need to use the dept name to make the
field unique (it would then be a seperate description field).

However, if you want to pull a new number anyway then the easiest way is to
create a table (tblSeq - column 1 = Department ID, Column 2 = NextNumber)
and then create a public function (Public Function GetNextSequence (Dept as
integer) as Integer) and then just call the function (this can be called
from a query as well to populate the field.

--- This is you cmdSave_Click() procedure ---
currentdb.Execute ("UPDATE YourTable SET KeyField = " & TextBoxDept &
"-" & GetNextSequence(TextBoxDepartment) & ", NextColumn = " & TextBox1
......

OR

rs.AddNew
![KeyField] = TextBoxDept & "-" & GetNextSequence(TextBoxDepartment)
![NextColumn] = TextBox1
.....
rs.Update

-- End Procedure --

Public Function GetNextSequence (Dept as integer) as Integer
'Add Error Handling
Dim rs as DAO.Recordset

Set rs = CurrentDB.OpenRecordSet("SELECT NextNumber From tblSeq WHERE
DepartmentID = " & Dept)

With rs
If Not .RecordCount = 0 Then
GetNextSequence = ![NextNumber]
rs.Edit
![NextNumber] = ![NextNumber] + 1
rs.Update
End If
End With

End Function

NOTE: This is untested code, may need a bit of tweaking (also the
DAO.Recordset may cause an error, just dump the DAO part if it does).

Eric Dreksler
 
Need help setting an account number to automatically
populate with the next number, i.e. Record #1 seq # 44-
231, Record #2 seq# 44-232, so on ONLY if the record is
saved (using a command button)

Next, I need to know how to concatenate a dept field with
the seq # to give me a unique number. Any suggestions?
Click to expand...

Concatenating multiple fields to create a unique number is unnecessary
and unwise: storing multiple fields jammend into one field is
redundant and violates the principle of atomicity!

You can create a unique Index or a primary Key on up to TEN fields;
it's not necessary to store the same data redundantly.

If you have a Seq field, you can ensure that all data entry occurs
using a Form, and increment Seq in the Form's BeforeUpdate event (or
the command button code). Use DLookUp to find the largest existing
value, add one, and assign that value to the control.
 
Back
Top