IIF statement??

[Teri Cyr]

I was going to use Payroll ID numbers as the unique identifier for each
person that I enter into a database template that I recently downloaded, but
realized on Friday that that may not work as we have some folks to be put
into the database who do not have ID numbers. I would like to use IDs for
those who have them, and then have the database randomly or sequentially
generate them for those that I do not have IDs for. Would an IIF statement
be the best way to do this? And if it is, how would I go about doing it?

Thank you,

Teri.

 

[.Len B]

Hi Teri,
I'd be more inclined to add a command button to the data-entry form in
the vicinity of
the ID field. I would then have the user click on the button to generate
a random ID in
the appropriate range and in the appropriate format for those people who
need it.
--
Len
______________________________________________________
remove nothing for valid email address.
| I was going to use Payroll ID numbers as the unique identifier for each
| person that I enter into a database template that I recently
downloaded, but
| realized on Friday that that may not work as we have some folks to be
put
| into the database who do not have ID numbers. I would like to use IDs
for
| those who have them, and then have the database randomly or
sequentially
| generate them for those that I do not have IDs for. Would an IIF
statement
| be the best way to do this? And if it is, how would I go about doing
it?
|
| Thank you,
|
| Teri.

 

[Arvin Meyer [MVP]]

I'd create a table seeded with a number which can never be an employee ID.
Then I'd just increment it. Something like:

Function GetEmpID()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblDefaults", dbOpenDynaset)

With rst
rst.MoveFirst
rst.Edit
rst!EmpID = rst!EmpID + 1
rst.Update
End With

Me.txtPayrollID = rst!EmpID

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

You will need to use the form's Current event to enable/disable the Command
Button that calls this function:

Private Sub Form_Current()
If Len(Me.txtPayrollID & vbNullString) > 0 Then
Me.cmdGetID.Enabled = False
Else
Me.cmdGetID.Enabled = True
End If
End Sub

And the Command button's Click event property sheet should have:

= GetEmpID()