Results 1 to 14 of 14
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    unknow VBA statement


    This an If THEN expression in Visual Basic for Access working db to check if a new record that is entered into a table via a form is already in the
    table so as to not put two identical records into the same table.


    Code:
    'Verify form is filled
        If Me.Dirty Then
            'Verify that personnel is not already in database
            If DCount("*", "qryFindDuplicatePersonnel") > 0 Then
                verifyBox = MsgBox("There is already a record with that name.  Would you like to add another?", vbOKOnly, "Duplicate Name")
                If verifyBox = vbNo Then
                    Exit Sub
                End If
            End If

    Now I understand the program comments on the IF-ENDIF statement, but I do not understand the line

    Code:
    If DCount("*", "qryFindDuplicatePersonnel") > 0 Then
                verifyBox = MsgBox("There is already a record with that name.  Would you like to add another?", vbOKOnly, "Duplicate Name")
                If verifyBox = vbNo Then
    I have not found a good tutorial on the internet that discusses this statement in detail. If someone could provide a link that
    goes in this VBA statement (and ones like it in detail) it would be greatly appreciated.

    Thanks in advance.

    Respectfully,


    Lou Reed

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It checks the count in the query,
    if the count >0 then tell the user there is already a record, so Add another?
    if no, then exit.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dcount()
    MsgBox()
    If verifyBox = vbNo Then ' is checkiing the user's response to the MsgBox function

    As Ranman said DCount(....) > 0 Then
    ' checks to see if the expression/value exists

  4. #4
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    What a minute it not only has to check to see if the count is o or above. it must also check to see if the record that is being placed in the table is the same as one in the table already.

    It there are no records in the table the answer is easy, place the new record in the table. But if there is a record with the data as the one being prosed to insert then do not insert the record.

    If you did, it would then have duplicate records. That is the question. How does it search for duplicate records. Anything query other than that is easy.

    Thanks in advance.


    Respectfully,


    Lou Reed

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    In this line:

    verifyBox = MsgBox("There is already a record with that name. Would you like to add another?", vbOKOnly, "Duplicate Name")

    VerifyBox will NEVER = vbNo, because the button option is wrong.

    vbOKOnly must be vbYesNo in order for the Yes and No buttons to show on the message box.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    How does it search for duplicate records.
    That depends on how qryFindDuplicatePersonnel works. The code infers that if the query returns any records, then the new record that is about to be inserted will be a duplicate.

    Please post the SQL of qryFindDuplicatePersonnel so we can know what it does.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Lou,

    As John said it depends on the query referenced in the DCount.
    Here's a sample that may clarify
    Code:
    20    If DCount("user", "tbluser", "User='" & Environ("username") & "'") = 0 Then  'indicates there is no such Username
    30      MsgBox "The user " & Environ("username") & "  doesn't exist"
    40    Else
    50      MsgBox "exists"
    60    End If
    Another

    If DCount("IngredientName", "tblIngredients", "IngredientName =" & Combo2.ItemData(0)) > 0 Then
    MsgBox " That would be a duplicate -no can do"
    .............

  8. #8
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    The SQL code is now shown:

    Code:
    ELECT tblPersonnel.LastName, tblPersonnel.FirstName, tblPersonnel.PersonnelID
    FROM tblPersonnel
    WHERE (((tblPersonnel.LastName)=[Forms]![frmAddPersonnel]![txtLastName]) AND ((tblPersonnel.FirstName)=[Forms]![frmAddPersonnel]![txtFirstName]));
    I hope that this helps.

    Respectfully,


    Lou Reed

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    That is SELECT-ing LastName, FirstName, PersonnelID FROM table tbpPersonnel to compare WHERE the LastName AND FirstName are = to the txtboxes txtLastName AND txtFirstName

  10. #10
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    That is SELECT-ing LastName, FirstName, PersonnelID FROM table tblPersonnel to compare WHERE the LastName AND FirstName are = to the txtboxes txtLastName AND txtFirstName

  11. #11
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Explain this one again. Please be frank. I did not write the code so I am not going to be offended. Just tell what is wrong in this code.

    Thanks in advance.

    Respectfully,

    Lou Reed

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Other than the error I pointed out in the Msgbox statement (see post #5), I don't see anything wrong with it.

    What do you mean by "wrong"? Are you getting an error message, or are you getting incorrect results? Do you get the duplicate warning message if you attempt to add a duplicate?

  13. #13
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    No I am not getting a error. it is just that I am very new to SQL coding and I thought that the last few posts (the ones after I posted the SQL code)
    were alluding to or pointing out an error.

    I have not put any records into this database yet.

    Thanks for your help.

    Respectfully,


    Lou Reed

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  2. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  3. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  4. If Statement Help
    By vennies83 in forum Queries
    Replies: 1
    Last Post: 06-09-2011, 03:50 PM
  5. BETWEEN in a FROM Statement
    By Cyborg in forum Queries
    Replies: 6
    Last Post: 05-12-2011, 01:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums