Welcome to SPN

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

Sign Up Now!

Mailing List without duplication

Discussion in 'Information Technology' started by huzefahashim@gmail.com, Jul 28, 2006.

  1. huzefahashim@gmail.com

    huzefahashim@gmail.com
    Expand Collapse
    Guest

    Hi,
    I would like to create a mailing list where there are no duplicates. It
    should check while the user is entering data if any previous records
    exist with the same data.

    For example, if there is a John Smith residing at 123 Oldsville Road,
    Somewhere, SM with phone number 12345, as soon as the user enters John
    in the 'First Name' Access should pop up all records matching first
    name John. When the user enters Smith, all records with John Smith
    should show up. Same record searching for phone number, address fields
    etc. The user does not need to necessarily enter data in a certain
    order. If he enters only the phone number, it should show up all
    records with that phone number.

    I hope I haven't left any confusion.

    Thanks,
    Zef.
     
  2. Loading...

    Similar Threads Forum Date
    Request#3 - Introduce SPN to a Mailing Lists/Social Networking Groups Get Involved Jan 20, 2009
    Sikh News Brit-Sikh cop in the dock for blackmailing sex offenders (New Kerala) Breaking News Dec 9, 2008
    Sikh News How The Left & Indian Nationalists Distorted The History Of Sikh Gadhar Movement - Breaking News Sep 21, 2016
    Interfaith Materialists And Its Discontents Interfaith Dialogues Jun 10, 2016
    Sikhi Quasi-Pragmatists And Quasi-Spiritualists, Care To Explain? Sikh Sikhi Sikhism Jun 6, 2016

  3. Sheila D

    Sheila D
    Expand Collapse
    Guest

    I think you might find that rather cumbersome, it may be better to set up a
    search facility so that the user can check before they start the input. You
    can also set up a Find duplicates query so that you can do 'house keeping '
    at regular intervals

    SHeila
    www.c-i-m-s.com

    "huzefahashim@gmail.com" wrote:

    > Hi,
    > I would like to create a mailing list where there are no duplicates. It
    > should check while the user is entering data if any previous records
    > exist with the same data.
    >
    > For example, if there is a John Smith residing at 123 Oldsville Road,
    > Somewhere, SM with phone number 12345, as soon as the user enters John
    > in the 'First Name' Access should pop up all records matching first
    > name John. When the user enters Smith, all records with John Smith
    > should show up. Same record searching for phone number, address fields
    > etc. The user does not need to necessarily enter data in a certain
    > order. If he enters only the phone number, it should show up all
    > records with that phone number.
    >
    > I hope I haven't left any confusion.
    >
    > Thanks,
    > Zef.
    >
    >
     
  4. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    Hi Zef

    It's still 10 mins to lunch so try this stuff. LoL ;-)

    I like that question – made me think (just a bit). This code has not been
    tested but (hopefully) it should be OK and am sitting here waiting for lunch

    Note it's not been tested but it "should" be OK but may need jigging about
    with.

    Note that if the name that you want “is†on the list the focus will be set
    to the [1stname] text box of that record – if that’s where you want the user
    to be when the form open – if not you can change it.

    Note you need to have column 1 of the combo set to show the [ClientID], then
    [Surname], then [firstname[, [etc] and whatever other stuff you need.


    Set this on on the Not On List option of your combo ( [ComboSurnameSearch]
    )


    Private Sub ComboSurnameSearch_NotInList(NewData As String, Response As
    Integer)
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim Msg As String

    Msg = "'" & NewData & "' is not on in the data base." & vbCr & vbCr
    Msg = Msg & "Do you want to add New Record?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    Response = acDataErrContinue
    MsgBox "Have another go."
    Else
    Set Db = CurrentDb
    Set rs = Db.OpenRecordset("tblClients", dbOpenDynaset)

    rs.AddNew
    rs![Surname] = NewData
    rs.Update
    Response = acDataErrAdded

    Me.ButtonNewRecordConfirm.Visible = True

    End If

    End Sub



    On the after-update option add this

    Private Sub ComboSurnameSearch_AfterUpdate()
    If Me.ComboSurnameSearch = "Add" Then
    DoCmd.GoToRecord , , acNewRec
    CD1stName.SetFocus
    Else

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ClientIDField] = " & Str(Me![ComboSurnameSearch])
    Me.Bookmark = rs.Bookmark

    Me.SubBookings.SetFocus
    End If

    Me.ComboSurnameSearch = ""

    End Sub


    Add a new button to your page set the visible (on-load to “Falseâ€) to
    confirm that the user wants to add the new record. The not on list option
    will display it until the new record is created – you can change this to
    “true†but why bother as in this case simply don’t code the “visible†bit –
    just set the button on the form and leave it. BUT it will always be there
    and forms should be as empty as possible until the control is needed (well
    that’s my ideas anyway).
    As you can see I have set this to close the main form and then reopen it as
    I am still having problems with my server (it’s crap and dead old) but if you
    work for a company that isn’t so mean you could simply requery the form.

    As you can see from the
    DoCmd.GoToRecord , , acLast
    This will also reopen the main form onto the new record that you have just
    created.


    Set this to the on-click of the new button


    Private Sub ButtonNewRecordConfirm_Click()
    On Error GoTo Err_ButtonNewRecordConfirm_Click


    DoCmd.Close
    DoCmd.OpenForm "MainFormName", acNormal
    DoCmd.GoToRecord , , acLast


    Exit_ButtonNewRecordConfirm_Click:
    Exit Sub

    Err_ButtonNewRecordConfirm_Click:
    MsgBox Err.Description
    Resume Exit_ButtonNewRecordConfirm_Click

    End Sub



    Hope this helps - I'm off to lunch

    And "Yes" I know I have done what you’re not meant to by simply adding the
    word “add†but couldn’t think of anything else and a bit lazy (and hungry)
    today.

    --
    Wayne
    Manchester, England.




    "huzefahashim@gmail.com" wrote:

    > Hi,
    > I would like to create a mailing list where there are no duplicates. It
    > should check while the user is entering data if any previous records
    > exist with the same data.
    >
    > For example, if there is a John Smith residing at 123 Oldsville Road,
    > Somewhere, SM with phone number 12345, as soon as the user enters John
    > in the 'First Name' Access should pop up all records matching first
    > name John. When the user enters Smith, all records with John Smith
    > should show up. Same record searching for phone number, address fields
    > etc. The user does not need to necessarily enter data in a certain
    > order. If he enters only the phone number, it should show up all
    > records with that phone number.
    >
    > I hope I haven't left any confusion.
    >
    > Thanks,
    > Zef.
    >
    >
     
  5. huzefahashim@gmail.com

    huzefahashim@gmail.com
    Expand Collapse
    Guest

    Hi Wayne

    Thanks for your quick update. Even if it meant delaying your lunch!
    Going through your code, made me realize MS Access is definetely not
    meant for what I want to do. I think I'll try looking online to see if
    I can find softwares especially made for that purpose. My company
    doesn't care how I do it. As long as it gets done!

    Thanks anyways,
    Zef

    Wayne-I-M wrote:
    > Hi Zef
    >
    > It's still 10 mins to lunch so try this stuff. LoL ;-)
    >
    > I like that question - made me think (just a bit). This code has not been
    > tested but (hopefully) it should be OK and am sitting here waiting for lunch
    >
    > Note it's not been tested but it "should" be OK but may need jigging about
    > with.
    >
    > Note that if the name that you want "is" on the list the focus will be set
    > to the [1stname] text box of that record - if that's where you want the user
    > to be when the form open - if not you can change it.
    >
    > Note you need to have column 1 of the combo set to show the [ClientID], then
    > [Surname], then [firstname[, [etc] and whatever other stuff you need.
    >
    >
    > Set this on on the Not On List option of your combo ( [ComboSurnameSearch]
    > )
    >
    >
    > Private Sub ComboSurnameSearch_NotInList(NewData As String, Response As
    > Integer)
    > Dim Db As DAO.Database
    > Dim rs As DAO.Recordset
    > Dim Msg As String
    >
    > Msg = "'" & NewData & "' is not on in the data base." & vbCr & vbCr
    > Msg = Msg & "Do you want to add New Record?"
    > If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    > Response = acDataErrContinue
    > MsgBox "Have another go."
    > Else
    > Set Db = CurrentDb
    > Set rs = Db.OpenRecordset("tblClients", dbOpenDynaset)
    >
    > rs.AddNew
    > rs![Surname] = NewData
    > rs.Update
    > Response = acDataErrAdded
    >
    > Me.ButtonNewRecordConfirm.Visible = True
    >
    > End If
    >
    > End Sub
    >
    >
    >
    > On the after-update option add this
    >
    > Private Sub ComboSurnameSearch_AfterUpdate()
    > If Me.ComboSurnameSearch = "Add" Then
    > DoCmd.GoToRecord , , acNewRec
    > CD1stName.SetFocus
    > Else
    >
    > Dim rs As Object
    > Set rs = Me.Recordset.Clone
    > rs.FindFirst "[ClientIDField] = " & Str(Me![ComboSurnameSearch])
    > Me.Bookmark = rs.Bookmark
    >
    > Me.SubBookings.SetFocus
    > End If
    >
    > Me.ComboSurnameSearch = ""
    >
    > End Sub
    >
    >
    > Add a new button to your page set the visible (on-load to "False") to
    > confirm that the user wants to add the new record. The not on list option
    > will display it until the new record is created - you can change this to
    > "true" but why bother as in this case simply don't code the "visible" bit -
    > just set the button on the form and leave it. BUT it will always be there
    > and forms should be as empty as possible until the control is needed (well
    > that's my ideas anyway).
    > As you can see I have set this to close the main form and then reopen it as
    > I am still having problems with my server (it's crap and dead old) but if you
    > work for a company that isn't so mean you could simply requery the form.
    >
    > As you can see from the
    > DoCmd.GoToRecord , , acLast
    > This will also reopen the main form onto the new record that you have just
    > created.
    >
    >
    > Set this to the on-click of the new button
    >
    >
    > Private Sub ButtonNewRecordConfirm_Click()
    > On Error GoTo Err_ButtonNewRecordConfirm_Click
    >
    >
    > DoCmd.Close
    > DoCmd.OpenForm "MainFormName", acNormal
    > DoCmd.GoToRecord , , acLast
    >
    >
    > Exit_ButtonNewRecordConfirm_Click:
    > Exit Sub
    >
    > Err_ButtonNewRecordConfirm_Click:
    > MsgBox Err.Description
    > Resume Exit_ButtonNewRecordConfirm_Click
    >
    > End Sub
    >
    >
    >
    > Hope this helps - I'm off to lunch
    >
    > And "Yes" I know I have done what you're not meant to by simply adding the
    > word "add" but couldn't think of anything else and a bit lazy (and hungry)
    > today.
    >
    > --
    > Wayne
    > Manchester, England.
    >
    >
    >
    >
    > "huzefahashim@gmail.com" wrote:
    >
    > > Hi,
    > > I would like to create a mailing list where there are no duplicates. It
    > > should check while the user is entering data if any previous records
    > > exist with the same data.
    > >
    > > For example, if there is a John Smith residing at 123 Oldsville Road,
    > > Somewhere, SM with phone number 12345, as soon as the user enters John
    > > in the 'First Name' Access should pop up all records matching first
    > > name John. When the user enters Smith, all records with John Smith
    > > should show up. Same record searching for phone number, address fields
    > > etc. The user does not need to necessarily enter data in a certain
    > > order. If he enters only the phone number, it should show up all
    > > records with that phone number.
    > >
    > > I hope I haven't left any confusion.
    > >
    > > Thanks,
    > > Zef.
    > >
    > >
     

Share This Page