Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    records double on form not in query (A2k3)

    Hi All,
    I've got a silly little question/problem.
    I have this simple little query, followed by an OpenForm

    strSQL = "SELECT DISTINCT [NumberPKID], [EntrantPKID], [FirstName], [LastName], [SSN] "
    strSQL = strSQL & "FROM qryMultipleEntryCheck "
    strSQL = strSQL & "WHERE (([FirstName] = '" & rstDupes!FirstName & "'"
    strSQL = strSQL & " AND [LastName] = '" & rstDupes!LastName & "')"
    strSQL = strSQL & " AND [NumberPKID] <> " & rstDupes![NumberPKID] & ")"
    strSQL = strSQL & " OR ([SSN] = '" & rstDupes!SSN & "'"
    strSQL = strSQL & " AND [NumberPKID] <> " & rstDupes![NumberPKID] & ")"
    'Debug.Print strSQL
    DoCmd.OpenForm strDupesForm, , strSQL, , , acDialog

    When I debug, print and paste strSQL into a query I get the recordcount of records I expect.
    However when the form opens, using strSQL as a filter, every record is doubled.
    An example: the query returns (in select order)
    30, 7, Jane, Doe, 123-45-6789
    47, 87, Jane, Doe, 123-45-6789
    The form, then, has doubled each record.
    The form has as its recordsource qryMultipleEntryCheck, the same query I am using to filter with and is continuous.
    I'd really like to see the recordcount of the query and not the doubled value as found on my form,
    so what am I forgetting? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Thanks.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: records double on form not in query (A2k3)

    But does qryMultipleEntryCheck have SELECT DISTINCT in its SQL? In other words, has the Unique Values property of the query been set to Yes?

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: records double on form not in query (A2k3)

    Hi Hans,
    Yes it does.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: records double on form not in query (A2k3)

    If you open the form by itself (not through the DoCmd.OpenForm instruction), does it contain duplicates?

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: records double on form not in query (A2k3)

    When I just open the form: 219 records
    When I open the query: 219 records

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: records double on form not in query (A2k3)

    Sorry, no duplicates that I can discern. Looks like the same returned records between the 2.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: records double on form not in query (A2k3)

    I don't think we'll solve this without seeing the database. Could you post a stripped down version?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]Thanks.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: records double on form not in query (A2k3)

    Sure.
    I'll get it there (or here, I think) as soon as I can.
    Thanks.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: records double on form not in query (A2k3)

    Hi Hans,
    I must apologize.
    While deconstructing the db to send it, I came on a hidden textbox, placed under a "visible" textbox on the form. I must have placed it there, or it got covered in a redesign process or something and I obviously had no recollection of it being there. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Anyway, on removal of that textbox I was left with the proper number of records showing on the form.
    I am sorry to have wasted your time. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Thank you for your help.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: records double on form not in query (A2k3)

    There is no need to apologize. Without this exchange of posts, you probably wouldn't have tried to deconstruct the database, so it was useful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •