Assigning a Temp Id

[OrganMan]

Wondering if someone can help me out again. I have a field ApplicantId in a table Applicant which is a primary key and is displayed on a form frmCandidate. The problem is that the user would like to add a new applicant without having an applicant id for them, all of the other demographics are filled out. I have added a button on the form named AssignTempId which when clicked will add a temporary id in the ApplicantId field preferably as a negative number. Example, -1 would be added to the first applicant without an Id then -2 for the next Applicant without an Id. Can someone help me with the code to fill the applicantId textbox with the temp id as well as the ApplicantId with the same temp id.

Thanks for the help

 

[nIGHTmAYOR]

i would rather suggest that u create a new Autonumbered field (Indented field if you'r using sql server) which you assign as your primary key and quit relying on ApplicantId since it's shifting to irrelevance.

however if you r realy obligued to achieve your -ve key quest then here goes:
In Before Update event of your input form add the following code

If IsNull(Me![ApplicantId]) Then    'if your table has a default value set ex (0)change condition according ie "If Me![ApplicationId] = 0 Then"
  Dim lngLastID As Long
  If DLookup("Min([ApplicantId])","YourTableNameHere") > -1 Then
     lngLastID = -1
   Else
     lngLastID = DLookup("Min([ApplicantId])","YourTableNameHere") - 1
   End If
Me![ApplicantId]=lngLastID
End If

 

[OrganMan]

Hey nIGHTmAYOR, Thanks for the help. I think the code that you gave me is very close but still not working. I am assuming that with this code I will not have to use the AssignTemp button to assign the negative ApplicantId. Am I correct in assuming that with your code as soon as I click on a new record (add a new applicant) the Applicant Id textbox will populate with example: -1, then -2 when adding the next Applicant? This will actually work better for me then having a button. Oh and I do not have 0 as a default in my ApplicantId field.

Here is the code that I am using:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![ApplicantId]) Then
Dim lngLastID As Long
If DLookup("Min([ApplicantId])", "Applicant") > -1 Then
lngLastID = -1
Else
lngLastID = DLookup("Min([ApplicantId])", "Applicant") - 1
End If
Me![ApplicantId] = lngLastID
End If
End Sub

Thanks

 

[nIGHTmAYOR]

yes exactly you will not need no button , it will just populate it the minute the record is to be saved.
yet i dont get you, this code is working fine for me , what error messages does it produce ??

 

[OrganMan]

Hey nIGHT mAYOR,

I changed the code a bit and am close, can you tell me why I am getting the error type mismatch on the set of the recordset? Oh, I am looking for the applicant id to populate as soon as the user opens the form (adding Applicant form).

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As Database, rs As Recordset, i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Min(ApplicantId) FROM Applicant", DB_OPEN_DYNASET)

If Not rs.EOF Then
rs.MoveFirst
i = CInt(rs.Fields("ApplicantId").Value)
i = i + 1
End If
'Not sure if its this or that
Me.txtApplicantId = i
'Me.ApplicantId = i
End Sub

Thanks for the help!

 

[nIGHTmAYOR]

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Min(ApplicantId) [COLOR=red]As Expr1[/COLOR]  FROM Applicant", DB_OPEN_DYNASET)

If Not rs.EOF Then
rs.MoveFirst
i = CInt(rs.Fields("[COLOR=red]Expr1[/COLOR]").Value)
i = i [COLOR=red]-[/COLOR] 1
End If
[COLOR=red]' what exactly is the name of your control not your control source ? should be Me.yourcontrolnamehere[/COLOR]
'Me.txtApplicantId = i
Me.ApplicantId = i
End Sub

that'd be all

 

[LPurvis]

There's no resolute need to alias your aggregate field value of course though.
Referencing the field by ordinal position is easy (and technically more efficient).

i = CInt(rs.Fields(0).Value)

 

[nIGHTmAYOR]

well its essential for the select statment but irrelivent for fields declaration but the user suggested it so i decided to carry along. why you'd ask ? every user has his signature in coding which appears clearly in multiple choiced syntaxes and optional arguments , introducing new syntax structure to the code of a user is more like introducing a red patch to a white dress

 

[LPurvis]

After getting on for 24,000 online posts I think it's safe to say I'm familiar with handling questioner's needs without confusing them too often.
The ordinal position syntax isn't a concept which needs excessive explanation if it's not understood initially. But is a concept which is worth understanding due to the constant occurance of the various collections that will crop up over time. (Not least of which are form controls as an inevitable example).
At worst it was a throwaway comment as an alternative.