Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Acting on a Match (A2K)

    OK, this is more a question of procedure as opposed to out and out code. I'll cut the example down to the bone for clarity's sake.

    I'm receiving goods for repair purposes and entering them into a Receiving File with the following fields:

    IdCode (autonumber)

    Part Number: Looks up a master for matching numbers to pick up descriptions, etc.

    Serial Number: These are considered to be unique as the chances of duplicate serial numbers in this situation is gazzillions to one.


    What I want to have happen is when a part comes in with a Serial Number that matches a Serial Number already in the Receiving File, this will be an indication that that item is back for the second time for further repair. I would then do all sorts of Warranty checking, etc.

    So, how do I flag this item without holding up further entries. Not in List is not good. In List is not good. My mind has blanked out even more than usual.
    Cheers,
    Andy

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

    Re: Acting on a Match (A2K)

    You only want to flag a record if the serial number is a duplicate? You could use the DLookup or DCount function to test if there are records with the same Serial Number but a different IdCode. For example a text box with control source

    =IIf(DCount("*", "NameOfTable", "[Serial Number] = " & Chr(34) & [Serial Number] & Chr(34) & " And Not [IdCode] = " & [IdCode]) > 0, "DUPLICATE!", "")

    Substitute the correct names. I assumed that Serial Number is a text field.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    Thanks for pointing me in what appears to be a logical direction. Needless to say I will probably revisit this problem further down the line. Or after tomorrow's lunch, whichever comes first,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    Bang on as usual. I really could use a 9 day week and a 36 hour day, but I'm sure they'd get filled just as quickly.

    Further questions on my quest for duplicates. Is there any way to:

    1. Find more than one duplicate or does it stop on the first hit, and

    2. Once having found said duplicate, is there a way to extract other information from that duplicate's record?

    I would have asked this on the first go round, but I always feel that it's better to take these problems and break them down and make sure that each part works before delving into the next one. I'm probably sounding repetitive, but thanks again for your continual help and support,
    Cheers,
    Andy

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

    Re: Acting on a Match (A2K)

    You could pop up a form that shows the records with the same serial number.
    - Create a form based on your table (or query). It can be a single form or a continuous form, whichever you prefer.
    - Set the Popup property of this form (in the Other tab of the Properties window) to Yes.
    - Save it as - say - frmDuplicates.
    - Put a command button on the form you already had, say cmdShowDups.
    - Create an On Click event procedure for this button:

    Private Sub cmdShowDups_Click()
    If IsNull(Me.[Serial Number]) Or IsNull(Me.[IdCode]) Then
    Exit Sub
    End If

    If DCount("*", "NameOfTable", "[Serial Number] = " & Chr(34) & Me.[Serial Number] & Chr(34) & _
    " And Not [IdCode] = " & Me.[IdCode]) > 0 Then
    DoCmd.OpenForm FormName:="frmDuplicates", WhereCondition:="[Serial Number] = " & _
    Chr(34) & Me.[Serial Number] & Chr(34) & " And Not [IdCode] = " & Me.[IdCode]
    End If
    End Sub

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    Thanks for the quick response. My choice now is to work on this latest code or pay some serious attention to my grandaughter who is spending the weekend with me. No offense meant, but guess who gets priority. However, I will be back to you by Monday. Thanks again,
    Cheers,
    Andy

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

    Re: Acting on a Match (A2K)

    A wise decision. I agree wholeheartedly.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    My grandaughter & I thank you for letting us have the weekend off. Now it's back in the barrel time.

    Needless to say, your code worked perfectly. To reiterate the processes here's what you did.

    First Suggestion to Show Warning of Duplicate:

    =IIf(DCount("*", "NameOfTable", "[Serial Number] = " & Chr(34) & [Serial Number] & Chr(34) & " And Not [IdCode] = " & [IdCode]) > 0, "DUPLICATE!", "")


    Second Suggestion to PopUp Form displaying multiple occurrences of Duplicate Serial No.:

    Private Sub cmdShowDups_Click()
    If IsNull(Me.[Serial Number]) Or IsNull(Me.[IdCode]) Then
    Exit Sub
    End If

    If DCount("*", "NameOfTable", "[Serial Number] = " & Chr(34) & Me.[Serial Number] & Chr(34) & _
    " And Not [IdCode] = " & Me.[IdCode]) > 0 Then
    DoCmd.OpenForm FormName:="frmDuplicates", WhereCondition:="[Serial Number] = " & _
    Chr(34) & Me.[Serial Number] & Chr(34) & " And Not [IdCode] = " & Me.[IdCode]
    End If
    End Sub


    And now for the ideal situation.

    Is there any way to trigger the popping up of the form from the original Show Warning Code in order to alleviate the person doing the inputting having to think to click the form button when he sees the "Duplicate" message? No offense meant, but the less that person has to think while processing input, that much the better.

    Thanks in advance as always for your continuing help,
    Cheers,
    Andy

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

    Re: Acting on a Match (A2K)

    You could put the code into the After Update event of the control bound to the Serial Number field, instead of in the On Click event of a command button.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    Mind Boggling! If you ever decide to visit Toronto, Canada....... Seriously.

    Thanks again,
    Cheers,
    Andy

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    Back in Post 461894 I successfully put together the following code using all of your excellent suggestions and all worked well, in that the form popped up, buttons were clicked and everything proceeded.


    '===== Identify Duplicate Serial Nos. =========================================
    Private Sub F01_Part_Serial_No_AfterUpdate()

    If IsNull(Me.[F01_Part_Serial_No]) Or IsNull(Me.[SRT_CN]) Then
    Exit Sub
    End If

    If DCount("*", "qry SRT", "[F01_Part_Serial_No] = " & Chr(34) & Me.[F01_Part_Serial_No] & Chr(34) & _
    " And Not [SRT_CN] = " & Me.[SRT_CN]) > 0 Then
    DoCmd.OpenForm FormName:="frm SRT Master - Receive - SubSerialDupes", WhereCondition:="[F01_Part_Serial_No] = " & _
    Chr(34) & Me.[F01_Part_Serial_No] & Chr(34) & " And Not [SRT_CN] = " & Me.[SRT_CN]
    End If
    End Sub
    '============================================

    Needless to say someone came back with,

    Part One:

    Is it possible to go directly to print so that no manual interference ( or thinking ) need be involved?

    The report called "rpt Warrant" would have to reflect the fields referenced in the "If Dcount routine" and thereby eliminate the form "frm SRT Master - Receive - SubSerialDupes" altogether.

    And, to add insult to injury,

    Part Two:

    There is a field called "F01_WarDayDiffKalc" sitting in a text box called "txtTest" which calculates whether or not the record found falls within a precalculated time period. If the field result is less than zero (0), then the Warranty is not valid and nothing gets printed.

    Would appreciate any help on this. No grandchildren coming this weekend, so I've got time to work on it.

    Thanks in advance,
    Cheers,
    Andy

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

    Re: Acting on a Match (A2K)

    You can expand the test at the beginning:

    If IsNull(Me.[F01_Part_Serial_No]) Or IsNull(Me.[SRT_CN]) Or Me.txtTest < 0 Then
    Exit Sub
    End If

    You can open a report instead of a form in the next part:

    DoCmd.OpenReport ReportName:="rpt Warrant", WhereCondition:="[F01_Part_Serial_No] = " & _
    Chr(34) & Me.[F01_Part_Serial_No] & Chr(34) & " And Not [SRT_CN] = " & Me.[SRT_CN]

    Unless you explicitly specify that a report should be opened in preview mode, it will be sent directly to the printer.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    Ahead three steps, back two.

    This now goes directly to print as I had hoped for.

    However:

    1.
    I tried to get it to go to preview just while I'm testing because I'm doing way more printing than I'd like when I'm testing. I tried to stick in a "acPreview" but obviously in the wrong spot because it/we got rejected.

    2.
    The coding for txtTest is based on the DateItemShipped + 180 days of the previous record with the matching serial number and not the current record. This was badly explained by yours truly.

    [F01_WarDateKalc] which contains the +/- days, is sitting in the previous record.

    3.
    When I was initally printing the report, the data fields on the report looked like this:

    =[Forms]![frm SRT Master - Receive]![SRT_CN]

    because the data was based on the old record found and displayed on the form which had been 'popped up'.

    Now that I'm going directly to print and not referring to that form, I'm getting the #Name? instead, which is logical, but I'm not sure how to refer to those 'old' fields so that the data from the matching record is displayed.

    Worse case scenario, I may have to go back to the Form version of this debacle.

    Anyway, this is how the code looks at present:

    Private Sub F01_Part_Serial_No_AfterUpdate()
    '
    If IsNull(Me.[F01_Part_Serial_No]) Or IsNull(Me.[SRT_CN]) Or Me.txtTest < 0 Then
    Exit Sub
    End If
    '
    If DCount("*", "qry SRT", "[F01_Part_Serial_No] = " & Chr(34) & Me.[F01_Part_Serial_No] & Chr(34) & _
    " And Not [SRT_CN] = " & Me.[SRT_CN]) > 0 Then
    '
    DoCmd.OpenReport ReportName:="rpt Warranty", WhereCondition:="[F01_Part_Serial_No] = " & _
    Chr(34) & Me.[F01_Part_Serial_No] & Chr(34) & " And Not [SRT_CN] = " & Me.[SRT_CN]
    End If
    '
    End Sub


    Appreciate your patience in advance,
    Cheers,
    Andy

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

    Re: Acting on a Match (A2K)

    1. Just add

    <code>, View:=acViewPreview</code>

    at the end of the instruction that opens the report.

    2. Sorry, I don't understand.

    3. What is the name of the form that contains F01_Part_Serial_No? You should use the name of that form instead of frm SRT Master - Receive in

    =[Forms]![frm SRT Master - Receive]![SRT_CN]

  15. #15
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Acting on a Match (A2K)

    Hans,

    1. The preview option worked
    2. I'm not surprised that you don't understand. I hardly understand it. However, what I'm going to do is try and get it down in a logical form, or worst case scenario, post the zipped mdb. This will probably be done by tomorrow.
    3. I'll have to do the above before the print portion makes any sense as well.

    My apologies for the confusion and thanks again for your patient input. I shall return!
    Cheers,
    Andy

Page 1 of 2 12 LastLast

Posting Permissions

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