Problem with primary key that needs to be replicated 1

[Syerston]

I am developing a Housing Benefit database where each claimant has his own reference number eg. 836245 ( this 6 digit number is manually added as a unique reference for that claimant(Primary Key).

However, if the claimant moves property and reapplies for benefit he must keep the same 6 digit number.

This will cause a problem as all records relating to the reference number must be kept for archive purposes.

Is there a way of checking the table when a reference is keyed onto a form so that:

a. if no record exists append 00 onto the end of the reference string as a type of check digit.

b. if the reference does exist append 01 onto the end or if 01 exists, 02 and so on.

Any help will be greatly appreciated.

 

[jebry]

Hi!

There are a couple of ideas. To do the check in your form you can use the following code:

Private Sub txtReferenceNumber_BeforeUpdate(Cancel as Integer)

Dim rst As DAO.Recordset
Dim strCriteria As String

strCriteria = "Select ReferenceNumber From tblBenefit Where ReferenceNumber Like '" & txtReferenceNumber & "*'"

Set rst = CurrentDb.OpenRecordset(strCriteria)

If rst.EOF And rst.BOF Then
Else
rst.MoveLast
If rst.RecordCount < 10 Then
txtReferenceNumber = txtReferenceNumber & &quot;0&quot; & Format(rst.RecordCount)
Else
txtReferenceNumber = txtReferenceNumber & Format(rst.RecordCount)
End If
End If

End Sub

Another thought; Maybe you should create an archive table and if you have the same Reference Number, you could append the record to the archive table and delete the record from data table before continuing. Please note that in the above code, you must use table names, field names etc as they appear in your database.

hth
Jeff Bridgham

 

[Syerston]

Excellent work jebry, excellent work.

 

[BNPMike]

Syerston

Aren't you travelling a dangerous road here?. What you are saying is the real key is reference number, instance number ie two data items. If you merge them into one data field you are storing up trouble for yourself. For starters you now have to use Select with between or like which is much less efficient than Select with equals. Same for joins. Worse however is the loss of canonical design.

Access allows you a key with more than one column. Similarly with indexes. Why not use this approach? mike.stephens@bnpparibas.com