none
Prior to appending records to Schedule I want to check if this record has been added before RRS feed

  • Question

  • Good afternoon,

    I have a form that has Work #, Date, Hour, and Color on it. When they hit submit it does an append from this form and the specific units table adding the Work #, Date, Hour, and Color to all the rows within the specific unit table to the master schedule. However, since there are different shifts I wanted it to look into the master schedule to see if this work # has already been added. I cannot do no duplicates because that work # will be common for that one unit. (e.g. the master schedule has Work # 1 used for record 1-15 for all the piece parts required)

    I want to do something like 

    Dim i as Integer

    for i = 0 to <Number of records in master list>

    if me.Work_no.Value = [Schedule].W_No.Column(0,i) then

    Exit 

    Else

    'Do the rest of the working code'

    Help?

    VR,

    BMille49


    • Edited by Bmille49 Wednesday, March 29, 2017 4:53 PM Forgot code
    Wednesday, March 29, 2017 4:51 PM

Answers

  • For example: If DCount("*", "[Hang Scedule]", "[M#]='" & Me.M_No & "'") > 0 Then

    • Edited by .theDBguy Wednesday, March 29, 2017 5:45 PM
    • Marked as answer by Bmille49 Wednesday, March 29, 2017 5:59 PM
    Wednesday, March 29, 2017 5:42 PM

All replies

  • Hi,

    You'll have to define what constitute a duplicate. Then, you should be able to use the DCount() function to check for duplicates. For example, if a duplicate means the same work # and date, then maybe something like this:

    If DCount("*", "MasterTable", "WorkNo=" & Me.WorkNo & " AND DateField=#" & Me.DateField & "#") > 0 Then

    'duplicate

    Else

    'not duplicate

    End If

    Hope it helps...

    Wednesday, March 29, 2017 5:07 PM
  • theDBguy,

    I think I understand what you mean. Can you help out with this, if I give a little more info? 

    I want to see if that M_No has been used before. 

    If DCount("*", "[Hang Schedule]", "[M#]=" & Me.M_No) > 0 Then
        MsgBox "Duplicate"
        Else
        MsgBox "Not Duplicate"
    End If

    This gives me the error "Run-time error '3464':

    Data type mismatch in criteria expression"

    The table is set up for text and the text box is text....

    Wednesday, March 29, 2017 5:22 PM
  • Hi, If M_No is a Text field, then you'll need to enclose the value in quotes. Hope it helps...
    Wednesday, March 29, 2017 5:34 PM
  • For example: If DCount("*", "[Hang Scedule]", "[M#]='" & Me.M_No & "'") > 0 Then

    • Edited by .theDBguy Wednesday, March 29, 2017 5:45 PM
    • Marked as answer by Bmille49 Wednesday, March 29, 2017 5:59 PM
    Wednesday, March 29, 2017 5:42 PM
  • theDBguy,

    Thank you very much! That was easier than I thought...I am embarrassed to say how many different approaches I tried....

    'Dim SQL As String
    'SQL = "SELECT Count([Hang Schedule].[M#]) as Count_Of_M_No" & _
    '      "FROM [Hang Schedule]" & _
    '      "WHERE ([Hang Schedule].[M#])=" & Mey_M_No & ";"

    'DoCmd.RunSQL SQL, False

    DCount

    Dlookup

    Wednesday, March 29, 2017 6:00 PM
  • Hi, You're welcome. Glad we could assist. Good luck with your project.
    Wednesday, March 29, 2017 6:06 PM