Controlling duplicated records

[Fadi]

Hi
I'll try to explain my problem as simple as possible
I have to fields IDField, TypeField
Its ok for me if the record duplicated if the TypeField=1
But the duplicating is not ok if the TypeField=2

For example these values is ok
IDField TypeField
================
1 1
1 2
1 2
2 1
2 2
2 2
2 2

and these not
IDField TypeField
================
1 1
1 1

I used this code on the form but it's worked with the new records
But its give me an error when I try to edit an existing record

Private Sub Form_BeforeUpdate(Cancel As Integer)
Set rs = CurrentDb.OpenRecordset("Select IDField, TypeField From MyTable
Where (((IDField)=" & IDFieldTxt.Value & ") And ((TypeField)=" &
TypeFieldTxt.Value & "))")
If (Not rs.EOF) And TypeFieldTxt.Value = 1 Then
Cancel = 1
MsgBox "Record Exist", vbOKOnly + vbCritical
ElseIf (Not rs.EOF) And TypeFieldTxt.Value = 2 Then
s = MsgBox("Record Exist, Do you want to save this record as its",
vbYesNo + vbQuestion)
If s = vbNo Then
Cancel = 1
MsgBox "Record NOT saved", vbOKOnly + vbCritical, "Save Error"
End If
End If
End Sub

what i am missing here

Thanks in advance

Fadi

 

[NetworkTrade]

well you state "duplicating is not ok if the TypeField=2"

but in your example you show duplicates to be ok

and then you show what is NOT ok to be Typefield 1 duplicates

 

[Fadi]

Sorry its typing mistake
it should be

Its ok for me if the record duplicated if the TypeField=2
But the duplicating is not ok if the TypeField=1

sorry agian and thanks NetworkTrade

Fadi

 

[NetworkTrade]

well in short; there is nothing intrinsic in Access that does what you want.
The intrinsic NoDuplicates feature is for the field and not for selective
values of the field - being dependent upon the value of a second field.

but of course what you seek is very do-able; it requires coding in a Module.
Possibly one of the MVPs will come up with another solution for you - - -

I find it isn't feasible to explain a highly custom code solution via this
type forum....sorry to be less then helpful on this one....

 

[Fadi]

Thank you NetworkTrade for reply,
so let me ask in another way

can i know if the user working on existed record (Editing), or he/she is
adding a new record
coase if i know that i can modify the code to work with it

thanks all

Fadi

 

[NetworkTrade]

well - if one is working directly in the Table then you can not really manage
that at all.

But if they are working via a Form - then definitely you have controls
possible. One thing is to set the form to look at existing records but not
able to edit. Another way is to set a form to only go to a new/blank record
- and remove the scroll & record locater controls.....these are intrinic in
the properties of a form.

This does not per se tell you what they are doing. But you can set up a
form that will prevent them from editing an existing record...

or a form that allows them to look at existing records but prevents them
from adding a new record

or only allow them to add new records but not see existing

.....things like this......these are intrinic in the properties of a form.

 

[Fadi]

Hi NetworkTrade,
thanks again for your reply
i think this is a good idea
look what i'll do,
i'll put an option button ( or toggle for example ) to let the user select
what he want to do (add / edit)
so the user will stay in the same form

i really appreciate your help
thanks again

Fadi