adding multiple records, with out failing the unique index criteria

[sheela]

I have already spent almost a day on this with out any
success. I really appreciate any help on this issue.
I am working on a freezers database for a lab. This will
store and maintain the data of samples in multiple
freezers.

I am using a form to add multiple records (for
simultaneous positions) to the table. The user specifies
start position and end position in the form. Then this
form adds multiple records to the table, all fields having
same value except the position field will differ, runs
through the start position and end position.

The table has a unique index to prevent storing more than
one sample in a position. If there is already a sample
stored in a position, say 15. Using this form if we
specify to enter from position 10 to 20, then it will add
into the position 10, and gives an error about the index.
I don't want to add any records if any of the start
position through end position is already filled in. I
would really appreciate if some body could help me.
Right now I am using the code as follows. Even I am
using "DoCmd.RunCommand acCmdDeleteRecord" there is still
one record is being added to the table.
Private Sub cmdSaveClose_Click()
Dim k As Integer
On Error GoTo Err_cmdSaveClose_Click

With Me.RecordsetClone
For k = Me.txtStartPosition.Value To
Me.txtEndPosition.Value
.AddNew
!RecordID = Me!RecordID
!Position = k
!TrialName = Me!TrialName
!FreezerName = Me!cmbFreezerName
!RackNumber = Me!cmbRackNumber
!Box = Me!cmbBox
!SampleId = Me!txtSampleId
!SampleType = Me!cmbSampleTyp
!Storedby = Me!txtStoredby
!StoredDate = Me!txtStoredDate
!StorageTemp = Me!txtStorageTemp
.Update
Next k
End With


DoCmd.Close
Exit_cmdSaveClose_Click:
Exit Sub
Err_cmdSaveClose_Click:
If Err.Number = 3022 Then
MsgBox ("Sorry, ...")

DoCmd.RunCommand acCmdDeleteRecord
Else: MsgBox "Error No: " & Err.Number & ";
Description: " & Err.Description

Resume Exit_cmdSaveClose_Click
End If
DoCmd.Close

End Sub

 

[Emilia Maxim]

I am using a form to add multiple records (for
simultaneous positions) to the table. The user specifies
start position and end position in the form. Then this
form adds multiple records to the table, all fields having
same value except the position field will differ, runs
through the start position and end position.

Sheela,

storing the same data in multiple tables is bad database design. It
seems your tables are not normalized. From the info you give, the
fields having teh same value for multiple positions ought to be stored
in one table and the positions in a separate table related 1:n to the
first table.

This said, to your problem

The table has a unique index to prevent storing more than
one sample in a position. If there is already a sample
stored in a position, say 15. Using this form if we
specify to enter from position 10 to 20, then it will add
into the position 10, and gives an error about the index.
I don't want to add any records if any of the start
position through end position is already filled in. I

You could first check if there is a position within the given interval
before adding the records. However, you'll have to think about what
should happen with positions 10-14 and 16-20 from the above example.

The code would look like this (beware, untested!), please look up item
DLookup in the Help:

Private Sub cmdSaveClose_Click()

Dim k As Integer
Dim strWhere As String
Dim varPos As Variant

On Error GoTo Err_cmdSaveClose_Click

'Build a criteria with the given position interval
'to find a position in between
strWhere = "[Position] >=" & Me!txtStartPosition _
& "[Position] <=" & Me!txtEndPosition

'Now, look for a record with a position
'corresponding to above criteria
varPos = DLookup("[RecordID]", "MyTableWithPositions", strWhere)

If IsNull(varPos) Then
'No record found, so add the new records
With Me.RecordsetClone
For k = Me!txtStartPosition To Me!txtEndPosition
.AddNew
!RecordID = Me!RecordID
!Position = k
!TrialName = Me!TrialName
!FreezerName = Me!cmbFreezerName
!RackNumber = Me!cmbRackNumber
!Box = Me!cmbBox
!SampleId = Me!txtSampleId
!SampleType = Me!cmbSampleTyp
!Storedby = Me!txtStoredby
!StoredDate = Me!txtStoredDate
!StorageTemp = Me!txtStorageTemp
.Update
Next k
End With
Else
MsgBox "Position between start and end found!", vbOKOnly
End If

Exit_cmdSaveClose_Click:
DoCmd.Close
Exit Sub

Err_cmdSaveClose_Click:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume Exit_cmdSaveClose_Click

End Sub

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de