Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Controlling duplicated records

Discussion in 'Information Technology' started by Fadi, Jul 28, 2006.

  1. Fadi

    Fadi
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    General Controlling Kaam (Lust) Hard Talk Apr 8, 2014
    Money and Controlling Spending Questions and Answers Nov 18, 2011
    USA Anti-Islam pastor called controlling, "mad" Breaking News Sep 13, 2010
    Opinion Manmohan Singh Phenomenon: India's Grand Strategy of Controlling the Thought of Sikhs Breaking News Jul 8, 2010
    Technique Of Controlling Mind Gurmat Vichaar Jan 27, 2008

  3. NetworkTrade

    NetworkTrade
    Expand Collapse
    Guest

    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
    --
    NTC


    "Fadi" wrote:

    > 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
    >
    >
    >
     
  4. Fadi

    Fadi
    Expand Collapse
    Guest

    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" <NetworkTrade@discussions.microsoft.com> wrote in message
    news:18636127-B46A-4626-AF13-70BB47C25133@microsoft.com...
    > 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
    > --
    > NTC
    >
    >
    > "Fadi" wrote:
    >
    >> 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
    >>
    >>
    >>
     
  5. NetworkTrade

    NetworkTrade
    Expand Collapse
    Guest

    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....

    --
    NTC


    "Fadi" wrote:

    > 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" <NetworkTrade@discussions.microsoft.com> wrote in message
    > news:18636127-B46A-4626-AF13-70BB47C25133@microsoft.com...
    > > 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
    > > --
    > > NTC
    > >
    > >
    > > "Fadi" wrote:
    > >
    > >> 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
    > >>
    > >>
    > >>

    >
    >
    >
     
  6. Fadi

    Fadi
    Expand Collapse
    Guest

    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" <NetworkTrade@discussions.microsoft.com> wrote in message
    news:CA2D5FE6-8E08-4FD3-A3E0-196C7C972187@microsoft.com...
    > 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....
    >
    > --
    > NTC
    >
    >
    > "Fadi" wrote:
    >
    >> 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" <NetworkTrade@discussions.microsoft.com> wrote in message
    >> news:18636127-B46A-4626-AF13-70BB47C25133@microsoft.com...
    >> > 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
    >> > --
    >> > NTC
    >> >
    >> >
    >> > "Fadi" wrote:
    >> >
    >> >> 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
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  7. NetworkTrade

    NetworkTrade
    Expand Collapse
    Guest

    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.
    --
    NTC


    "Fadi" wrote:

    > 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" <NetworkTrade@discussions.microsoft.com> wrote in message
    > news:CA2D5FE6-8E08-4FD3-A3E0-196C7C972187@microsoft.com...
    > > 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....
    > >
    > > --
    > > NTC
    > >
    > >
    > > "Fadi" wrote:
    > >
    > >> 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" <NetworkTrade@discussions.microsoft.com> wrote in message
    > >> news:18636127-B46A-4626-AF13-70BB47C25133@microsoft.com...
    > >> > 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
    > >> > --
    > >> > NTC
    > >> >
    > >> >
    > >> > "Fadi" wrote:
    > >> >
    > >> >> 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
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  8. Fadi

    Fadi
    Expand Collapse
    Guest

    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

    "NetworkTrade" <NetworkTrade@discussions.microsoft.com> wrote in message
    news:031C267B-2D18-43DF-9015-690427F67CA8@microsoft.com...
    > 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.
    > --
    > NTC
    >
    >
    > "Fadi" wrote:
    >
    >> 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" <NetworkTrade@discussions.microsoft.com> wrote in message
    >> news:CA2D5FE6-8E08-4FD3-A3E0-196C7C972187@microsoft.com...
    >> > 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....
    >> >
    >> > --
    >> > NTC
    >> >
    >> >
    >> > "Fadi" wrote:
    >> >
    >> >> 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" <NetworkTrade@discussions.microsoft.com> wrote in
    >> >> message
    >> >> news:18636127-B46A-4626-AF13-70BB47C25133@microsoft.com...
    >> >> > 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
    >> >> > --
    >> >> > NTC
    >> >> >
    >> >> >
    >> >> > "Fadi" wrote:
    >> >> >
    >> >> >> 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
    >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     

Share This Page