Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82

    Trying To Add A Record To A Table and Check for Duplicate Entries Using a Dialog Box

    I am currently working on a database for tracking equipment and equipment maintenance and I am currently having some issues on a form. On a form named frmEquipTrack I have a button called btnAddMfr. The user will be shown a dialog box that prompts them to add a new manufacture to the database when they click the button. The user will input the name of the new manufacture and when they click ok on the dialog box, the manufacture is added to a table called tblMfr.


    I have experienced some success with getting this done. When the code is:
    Code:
     
    
    Private Sub btnAddMfr_Click()
    Dim Message, Title, strMfr
    Dim dbsMfr As DAO.Database
    Dim rstMfrName As DAO.Recordset
    
    
    Set dbsMfr = CurrentDb
    Set rstMfrName = dbsMfr.OpenRecordset("tblMfr")
    Message = "Please Add New Manufacture"
    Title = "Add Manufature"
    
    
        strMfr = InputBox(Message, Title)
    
    
    
    
        rstMfrName.AddNew
        rstMfrName("Mfr").Value = strMfr
        rstMfrName.Update
    
    
    
    
    
    
    
    
    End Sub
    everything works fine and I am able to add the string that is entered by the user to the tblMfr table.




    However, I quickly realized that I could have duplicate entries since I wasn't checking the tblMfr table to see if that manufacture was already on the list. So i decided to try and search the record set and check if the string the user entered into the dialog box was already on the tblMfr table. I changed my code to:

    Code:
    Private Sub btnAddMfr_Click()
    Dim Message, Title, strMfr
    Dim strTest As String
    Dim dbsMfr As DAO.Database
    Dim rstMfrName As DAO.Recordset
    
    
    Set dbsMfr = CurrentDb
    Set rstMfrName = dbsMfr.OpenRecordset("tblMfr")
    Message = "Please Add New Manufacture"
    Title = "Add Manufature"
    
    
        strMfr = InputBox(Message, Title)
    With rstMfrName
    
    
        If Not .BOF And Not .EOF Then
    
    
            .MoveLast
            .MoveFirst
    
    
            While (Not .EOF)
    
    
               
                
                strTest = rstMfrName!Mfr
                If (StrComp(strMfr, strTest, vbTextCompare)) = 0 Then
                    MsgBox "Mfr Already In Database", vbOKOnly, "Warning"
                Else
                    rstMfrName.AddNew
                    rstMfrName("Mfr").Value = strMfr
                    rstMfrName.Update
                End If
    
    
            .MoveNext
    
    
            Wend
        End If
        .Close
    End With
    
    
    ExitSub:
        Set rstMfrName = Nothing
        Exit Sub
    ErrorHandler:
        Resume ExitSub
    
    
    End Sub
    and now the message box that should warn me that the manufacture that was just entered on the table pops up four times even if the manufacture isn't already on the table. Then I have four new record on the tblMfr table when I refresh the tblMfr table. I think my mistake is in my while loop but I am not sure what that might be.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try something like this:
    Code:
    Private Sub btnAddMfr_Click()
    Dim Message, Title, strMfr as string
    Dim dbsMfr As DAO.Database
    Dim rstMfrName As DAO.Recordset
    
    
    Set dbsMfr = CurrentDb
    Set rstMfrName = dbsMfr.OpenRecordset("tblMfr")
    Message = "Please Add New Manufacture"
    Title = "Add Manufature"
    
    
        strMfr = InputBox(Message, Title)
    'check if already exists
    if Dcount("*","tblMfr","Mfr=""" & strMfr & """")>0 then
        msgbox "Manufacture already exists",vbCritical,"Mfr validation"
        Goto CleanUp
        
    End if
        rstMfrName.AddNew
        rstMfrName("Mfr").Value = strMfr
        rstMfrName.Update
    
    
    CleanUp:
    Set rstMfrName =Nothing
    Set dbsMfr=Nothing
    
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    What is the purpose of movelast,movefirst?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Strcomp only returns 0 if the strings are equal, otherwise it returns -1 or 1 depending on the comparison as long as neither string is null.
    Wondering if Message and Title are Variants by design?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Welshgasman View Post
    What is the purpose of movelast,movefirst?
    The tutorial/example I found when I was trying to figure this out yesterday stated that while this isn't required it was considered best practice as a way to ensure that the when loop starts on the first record.

  6. #6
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Micron View Post
    Strcomp only returns 0 if the strings are equal, otherwise it returns -1 or 1 depending on the comparison as long as neither string is null.
    Wondering if Message and Title are Variants by design?

    StrComp was the first thing that I though of when I started to work on this yesterday. The message and title are currently variants because I was getting a syntax error when I was first setting up the Input Box, but I have figured out what I was doing wrong and have corrected the issue.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Did you try my suggestion?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by drunyan0824 View Post
    The tutorial/example I found when I was trying to figure this out yesterday stated that while this isn't required it was considered best practice as a way to ensure that the when loop starts on the first record.
    I have not to my knowledge (as small as it might be) ever heard of opening a file/recordset and not being on the first record (if it exists?)
    Even to that end just the MoveFirst would be needed, surely?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the reason for using movelast/movefirst is to enable a true recordcount to be returned (note that only movelast is actually required to do that). The problem is that for large datasets nothing will happen until the entire recordset has been returned - which can take time.

    Easy to see with a slow query - the recordcount is not returned until the entire recordset has been loaded. You may see the first few records displayed but try to sort/filter/find/scroll through the recordset before the recordcount is displayed and nothing much will happen. If you need to know the recordcount then fine, use movelast and wait, otherwise don't bother.

    So best practice? or overkill?

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I understand the method to get the real record count, but it was not used in this code, so therefore curious as to why it was being used?

    For example, you see in functions all the time, where a variable keeps track of the value the function will return, and that is set as pretty much the last line. I always wondered why the function name was just not set, and leave out the middleman.

    There is a reason, though I have forgotten it now , so thouth the moves might be along that sort of thinking?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Gicu View Post
    Did you try my suggestion?
    Cheers,
    Yes, I tried your suggestion and it worked perfectly. I did have a question on one line of code.

    on the line:

    Code:
    If DCount("*", "tblMfr", "Mfr=""" & strMfr & """") > 0 Then
    I understand that in this instance the Dcount function is looking at all of the records on the table tblMfr. I am assuming that using "*" in the expression of the function is a wildcard character that is basically telling the dcount function to look at the values in all fields that are on the tblMfr table. But I am still really struggling to understand what the delimitators in access are doing.

    The line
    Code:
    "Mfr=""" & strMfr & """"
    is comparing the values of the field Mfr on the tblMfr table to the strMfr that is entered in the input box, but to me it seems like there are two quotation marks that are unnecessary.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This """ is a double quote wrapped in quotes so that the string itself contains double quotes, as in "dog". The last 4 - same thing as for the first three. The last one ends the entire expression so that basically it is paired with the very first one. If I can, I don't do it that way as it's confusing for others, and sometimes even myself. I use single quotes ( ' ) as in
    "Mfr = ' " & strMfg & " ' " without the extra spaces that I included here for clarity.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The advantage of using double double quotes vs. single quotes is that the former would work with strings containing apostrophes or sigle quotes like O'Brian Manufacturing or Bob's Nails.

    Cheers,

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I understand that in this instance the Dcount function is looking at all of the records on the table tblMfr. I am assuming that using "*" in the expression of the function is a wildcard character that is basically telling the dcount function to look at the values in all fields that are on the tblMfr table.
    For clarification:
    Using e.g. DCount("FieldName","TableName") will count all the records where the specified field is not null
    Whereas DCount("*","TableName") will indeed count all the records (and is usually slightly faster)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Gicu View Post
    The advantage of using double double quotes vs. single quotes is that the former would work with strings containing apostrophes or sigle quotes like O'Brian Manufacturing or Bob's Nails.

    Cheers,
    Perhaps not so useful for e.g. 2 1/2" nails or 4" x 2" timber
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 12-16-2018, 06:45 AM
  2. Prevent duplicate entries in linked table
    By arothacker in forum Forms
    Replies: 5
    Last Post: 02-11-2014, 11:08 PM
  3. Replies: 2
    Last Post: 02-28-2013, 10:06 AM
  4. Replies: 6
    Last Post: 09-28-2012, 02:49 PM
  5. Replies: 2
    Last Post: 04-20-2011, 06:59 AM

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