Results 1 to 12 of 12
  1. #1
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111

    Matching Records Not Registering As Matching In VBA Code

    I have a table, "tScenarios", with a short text field called "wostCaseTopEvent". The value for this field can either be a preset selection or something the user enters themselves. I wrote some code that adds anything new that the user enters to another table, "tTopEvents". It compares the contents of "tScenarios" to the contents of "tTopEvents", and if it finds a match it won't create a new record.



    For some reason it's not registering entries that match as matching, and duplicates records a number of times. This doesn't happen for all entries, only some, and I can't figure out why. I even made sure there were no extra spaces / spelling mistakes and copy/pasted the entries from one table onto the other to ensure they were exactly the same, and the issue persists.

    Here's my code:


    Code:
    Dim db As Database, rs As Recordset, rs2 As Recordset
    
    
    Dim TE As String, TEcheck As String
    
    
    Set db = CurrentDb
    Set rs2 = db.OpenRecordset("tTopEvents")
    Set rs = db.OpenRecordset("tScenarios")
    
    
    Do Until rs.EOF
        
        TE = rs(5)
        
        'for testing, remove later
        MsgBox "scenario event: " & TE
        
        TEcheck = "False"
        
            Do Until rs2.EOF
                
                
                If rs2(1) = TE Then
                    
                    'for testing, remove later
                    MsgBox TE & " matched with " & rs2(1)
                    
                    TEcheck = "True"
                    
                End If
                
                rs2.MoveNext
                
            Loop
        
        If TEcheck = "False" Then
            
            'for testing, remove later
            MsgBox "No match found for " & TE & " Adding to table"
            
            db.Execute "INSERT INTO tTopEvents(topEvent)" & "VALUES(""" & TE & """)"
            
        End If
        
        
        
        rs.MoveNext
        
        
    Loop
    If anyone knows why this is happening, please let me know. Any help is appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you need to compare all the records of 2 tables just to validate the input of a single record?

    What event is this code in? Where is user doing this input - a combobox? Why loop through a recordset just to look for a single value - a DLookup() function can accomplish that. Or apply filter to recordset so only records meeting the input are returned - then test if recordset is empty to determine if there is matching data.

    Need to provide sample data (successes and failures) that can be used to test code.

    Maybe you should explore combobox NotInList event. Common topic, many examples.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    The event occurs when the form is opened. If the user clicks the "Edit" button on another form, this one is brought up. It checks to make sure it's listing all possible choices, and adds anything its missing. The person this is being built for doesn't want to use combo boxes, they want to be able to hit a button and add the record instead of going through a list in a combo box. There are reasons for this that I won't get into.

    Not sure why I didn't just use a DLookup tbh. Lack of sleep / overthinking I guess.

    I changed it to use DLookups instead, and the issue persists

    This is just what I threw together real quick to see if the issue continued

    Code:
    Do Until rs.EOF
        
        TE = rs(5)
        
        If TE = DLookup("topEvent", "tTopEvents", "topEventID > 0") Then
            
            MsgBox "found a match for " & TE
            
        Else
        
            MsgBox "No match found for " & TE
            
        End If
        
        
        
        rs.MoveNext
        
        
    Loop
    It tells me it finds a match for the very first record, then everything after that it can't find a match for.

    The only match is "Generic Malware"

    Examples of things that don't match, but should:

    "ICS Specific Malware"

    "Ransomware"

    "Stolen Data"

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So why are you not including the recordset field in the DLookup criteria?

    Code:
    If Not IsNull(DLookup("topEvent", "tTopEvents", "topEventID =" & rs(5))) Then
    Is topEventID a number type? If so, why would you compare with text values like "Stolen Data"?

    What is the relationship of these tables?

    Maybe you should look at a FindUnmatched query.
    Last edited by June7; 07-17-2018 at 07:53 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are not controlling where the recordset starts. Don't assume it will begin with what you see in the first row - it could start anywhere as you have written it. Suggest you use any of the customary methods to ensure there are records, then MoveFirst. I also note that you don't move through the first recordset, so that suggests there might be only one record in the table. In that case, I don't see why all that code is necessary to check one static value against some other table or recordset. Or is it that you don't want to compare any of the other values from the 1st set against the 2nd?

    Maybe you ought to say what the high level goal is. I can't figure out if it's just to log list values that someone else has created, or not allow duplicates, or both. This might be as simple as having a unique index on the table. Or maybe all values should be in one table, with a separate field to flag those created by users after an initial set of list values might be put into production. I think there would have to be a good reason for splitting list values for any one entity into separate tables.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    June7:

    topEventID is a number type, but it's not being compared to anything. topEvent is a text value, which is being compared to rs(5), which is another text value. The recordset field is being set to a variable which is then being compared. I could do it either way, but ultimately it's doing the same thing. Just outright using the recordset field probably makes more sense so I'll make that change. I'll look into using a FindUnmatched query when I can and post results, I'm a bit pressed for time as I'm writing this.

    Micron:

    The MsgBoxes in both the first and second code I posted confirm that it is starting at the first record. It shows me what value its going to be comparing, what it finds a match with if it does, and what it doesn't find a match for. I'm not sure I completely understand the rest of your post, but from what I gathered:

    You say I don't move through the first recordset, but if I'm understanding you correctly, I am moving through the first recordset. I can confirm this because of the MsgBoxes, which tell me what values it does and doesn't find matches for. Because of this I know it's starting at the first record and moving down appropriately. The first record, "Generic Malware", finds a match appropriately. The second and onward do not. There are 32 records in the scenarios table, and 9 records in the topEvents table.

    The high level goal is just to make sure that if a user has entered their own top event value it gets added to this list. The list is being used to determine what should be displayed on the buttons in the edit form so that all the options the user wants are there. I went into why I'm using buttons instead of comboboxes in a previous post in case you missed that.

    -------------------------------------

    Keep in mind that the message boxes I set up to troubleshoot are confirming that the strings SHOULD match, the recordsets are going through in the right order, and I'm pulling the correct field values from each recordset.


    Sorry if I missed anything obvious here, like I said I'm a bit rushed atm. I do appreciate the help.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why do you duplicate text values in two tables? Should just save PK from one as FK in other. Then the search would use the keys as criteria. If you want to search on the text and rs(5) is text field not the key value:

    If DCount("*", "tTopEvents", "topEvent='" & rs(5) & "'") = 0 Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    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

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    my comments in bold.
    Micron:
    The MsgBoxes in both the first and second code I posted confirm that it is starting at the first record. A matter of luck. I have seen a recordset start in the 'middle' of a table more than 2 or 3 times over the years.

    You say I don't move through the first recordset...I think my whole window scrolled rather than the code itself. I missed the move and loop at the end. Sorry.

    Personally, I would not repeatedly execute a DLookup on a table when you can load the whole recordset and move through it - especially if going over a network. Probably best if you posted a zipped db copy as suggested since we (I?) seem to be dancing around the problem.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Micron has good point. Instead of domain aggregate after all, go back to two recordsets but instead of looping thru the second one, use FindFirst method.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    The DCount that June7 posted ended up working for me. I think I was misunderstanding how DLookup goes through a table, I thought it would check every record that satisfied the "topEventID > 0" criteria to see if it matched, but I guess it just stops at the first record that satisfies the criteria? Regardless, thank you.

    As far as improving it by not using a DLookup and instead using a FindFirst, could someone explain why that would be more efficient? I'm not questioning whether it's the better option or not, just curious as to why that is the case.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Because with DLookup if rs has 1000 records code makes 1000 calls to table whereas opening a recordset is 1 call. If db is split and backend is on server performance may be slower.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 6
    Last Post: 01-03-2021, 05:06 AM
  2. Replies: 6
    Last Post: 04-08-2018, 03:45 AM
  3. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  4. Replies: 1
    Last Post: 08-11-2011, 11:33 AM
  5. Trace not matching code
    By Buakaw in forum Programming
    Replies: 2
    Last Post: 07-21-2011, 01: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