Solved Loop Crashing Access when updating record set (1 Viewer)

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
OK....

Im sure that everyone will tell me I should do this in a different way than what i have come up with.

I have a query which is appending a table and fills out two columns with the data from the query being a result of a search (which I have already had help with in the query forum).

However, I need the Primary Key ID number from the current form to be added to a column for each record appended to the table.

I have therefore written the below VBA (Which works other than Access crashes, which i assume is due to the loop not closing.

I think it is due to the "Do Until .EOF" but what other loop can I use?

Code:
Private Sub Command30_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConflictResultsClients Query"
DoCmd.SetWarnings True

Dim FrmNo As String
Dim rs As DAO.Recordset

FrmNo = Forms![ConflictsofInterestForm]!Form_Number.Value 'set variable as text box on form

Set rs = CurrentDb().OpenRecordset("Conflict Results", dbOpenDynaset) 'Open table that contains Apended Records from Query
  
  
    With rs
    Do Until .EOF 'do this until End of Function
        .FindFirst "[Form Number] = 0"  'Find the first record that = 0
        .Edit
          ![Form Number] = FrmNo 'Change field of that record to the new value
        .Update
    Loop
        
    End With
    
    rs.Close
    Set rs = Nothing

End Sub
 

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
I do not think you will ever get to EOF?
https://docs.microsoft.com/en-us/of...base-reference/recordset-findfirst-method-dao

I'd just use and Update query?
Gasman

The reason I have created this as above is because there is no link between the tables whatsoever but I need to create a link between the results with the primary key of the data in the table/form used for the search to save the results.

The search and append query also produces multiple results to be saved and that is why i built in the loop.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:47
Joined
Oct 29, 2018
Messages
15,542
By "crashes," do you mean Access hangs? If so, as @Gasman said, you may be getting into an infinite loop, since you are not moving your record pointer through the loop. Do you know if you're actually reaching the end of the recordset?
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:47
Joined
Mar 14, 2017
Messages
5,380
I rarely use any 'find' functions in recordsets, so am not 100% sure, but I do know one thing for 100% sure - if you keep moving next, you WILl get to EOF.

Possibly throw in a MoveNext

But honestly, I wouldn't suggest using a FindFirst at all. Just open the recordset with the appropriate criteria so no finding is needed
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:47
Joined
Sep 21, 2011
Messages
8,493
Gasman

The reason I have created this as above is because there is no link between the tables whatsoever but I need to create a link between the results with the primary key of the data in the table/form used for the search to save the results.

The search and append query also produces multiple results to be saved and that is why i built in the loop.
Really? all I can see is you updating a field that is zero to that of a form control value.?

What am I missing?
 

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
I rarely use any 'find' functions in recordsets, so am not 100% sure, but I do know one thing for 100% sure - if you keep moving next, you WILl get to EOF.

Possibly throw in a MoveNext

But honestly, I wouldn't suggest using a FindFirst at all. Just open the recordset with the appropriate criteria so no finding is needed
@ Isaac

The .MoveNext did the trick..... didn't think i needed it as i was looping! Newbie mistake I think there!
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:47
Joined
Sep 21, 2011
Messages
8,493
Yes it just hangs as it infinitely goes through the loop
If you read the link it states that the record pointer is unknown if no record is found?

Perhaps you should have used Do Until .NoMatch?
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:47
Joined
Mar 14, 2017
Messages
5,380
@ Isaac

The .MoveNext did the trick..... didn't think i needed it as i was looping! Newbie mistake I think there!

LOL glad to hear it - no worries. The "looping" only guarantees that the first line of the loop content will keep iterating. Forever, unless told differently. Any idea of progression toward an end-point has to be added in, so to speak.

This reminds me of the Scripting.Filesystemobject, what I usually use in VBA to read text from a file. You can code:
Do Until TextstreamObject.AtEndOfStream = True
'something
Loop

..... But that loop will run forever unless you either kick yourself out of it somehow, or remember to use the .ReadLine method.

Note - it should be noted that I am not super familiar with the FindFirst method and how it really behaves, and whether or not .MoveNext might actually move you beyond a record that FindFirst might have otherwise 'found'.

Honestly, I would recommend - if you are going to use FindFirst - Do Until Dcount()
Or, not use FindFirst, and open a recordset where [field] is null in the first place............THEN loop through every record.
 

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
Really? all I can see is you updating a field that is zero to that of a form control value.?

What am I missing?
Sorry if im not clear.... this database has just grown and grown in size!

I have a table with two columns for client names and client references.

I have a table with a form which a user inputs a new client and a query is undertaken from multiple fields on that form against the client list and if there is a match returns the client(s) names.

So i have two unlinked tables with no corresponding field being the same and a query result.

I need that query result to be appended into a table to save the search results but at the same time i need those query results to now have a link back to the original record in the new client table for future use.

I could not get a left link to work on a second query as the table i need to use for the links is the table that I am appending and therefore no results appear in the new query. Therefore, I came up with the above to use the original query to append the table and then use the VBA to update the fields in the table where the form ID should be (which is now 0 as the other data has been appended) to the actual form number.

I hope that makes sense!
 

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
Now because this whole thing can be run a second time by the user with updated information and I don't want the original information to be kept I need to somehow include a delete duplicates into this as well!
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:47
Joined
Sep 21, 2011
Messages
8,493
Sorry if im not clear.... this database has just grown and grown in size!

I have a table with two columns for client names and client references.

I have a table with a form which a user inputs a new client and a query is undertaken from multiple fields on that form against the client list and if there is a match returns the client(s) names.

So i have two unlinked tables with no corresponding field being the same and a query result.

I need that query result to be appended into a table to save the search results but at the same time i need those query results to now have a link back to the original record in the new client table for future use.

I could not get a left link to work on a second query as the table i need to use for the links is the table that I am appending and therefore no results appear in the new query. Therefore, I came up with the above to use the original query to append the table and then use the VBA to update the fields in the table where the form ID should be (which is now 0 as the other data has been appended) to the actual form number.

I hope that makes sense!
That might well be, but all that code is doing, is what I have already stated?
There is generally more than one way to accomplish a task in computing, you just chose another way, different to the way I would have done it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:47
Joined
Sep 21, 2011
Messages
8,493
This is for my benefit but why would the second FindNext line be present.?
This is an MS example from that link I posted earlier in this thread?

Code:
Sub FindOrgName()
   
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
       
        'Get the database and Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblCustomers")
   
        'Search for the first matching record  
        rst.FindFirst "[OrgName] LIKE '*parts*'"
       
        'Check the result
        If rst.NoMatch Then
            MsgBox "Record not found."
            GotTo Cleanup
        Else
            Do While Not rst.NoMatch
                MsgBox "Customer name: " & rst!CustName
                rst.FindNext "[OrgName] LIKE '*parts*'"
            Loop
   
            'Search for the next matching record
          rst.FindNext "[OrgName] LIKE '*parts*'" ' This line here?????
        End If
      
        Cleanup:
            rst.Close
            Set rst = Nothing
            Set dbs = Nothing
   
    End Sub
[/ode]
 
Last edited:

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
This is for my benefit but why would the second FindNext line be present.?
This is an MS example from that link I posted earlier in this thread?

Code:
Sub FindOrgName()
   
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
       
        'Get the database and Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblCustomers")
   
        'Search for the first matching record  
        rst.FindFirst "[OrgName] LIKE '*parts*'"
       
        'Check the result
        If rst.NoMatch Then
            MsgBox "Record not found."
            GotTo Cleanup
        Else
            Do While Not rst.NoMatch
                MsgBox "Customer name: " & rst!CustName
                rst.FindNext "[OrgName] LIKE '*parts*'"
            Loop
   
            'Search for the next matching record
          [B]  [/B]rst.FindNext "[OrgName] LIKE '*parts*'" ' This line here?????
        End If
      
        Cleanup:
            rst.Close
            Set rst = Nothing
            Set dbs = Nothing
   
    End Sub
[/ode]
Your right instead of the loop I could have used the FindNext function
 

moke123

AWF VIP
Local time
Today, 02:47
Joined
Jan 11, 2013
Messages
2,470
Now because this whole thing can be run a second time by the user with updated information and I don't want the original information to be kept I need to somehow include a delete duplicates into this as well!
What is the overall purpose of this exercise?
 

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
What is the overall purpose of this exercise?
As information about a job is found out then further checks against third parties is required and therefore the user will have to go back in update and re-run....

haven't started to figure out the delete duplicates yet as it appears to be a minefield!
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:47
Joined
Mar 14, 2017
Messages
5,380
If you have a situation where you have a column that data is duplicated in, and you can 'go' by that - then it becomes very easy.
Example, I have 10 records. Each record is duplicated 2, maybe 3 or 4 times. The duplicate values can be identified (perhaps among other ways), but a column like ID. There are 2 records with ID 2, 3 record with ID 4, and so on.

Your query logic becomes something like:

delete * from tablename where id not in (select top 1 id from tablename as tn where tn.id = tablename.id)

.... deleting all but the top 1 will randomly preserve one record.

Any other situation and personally I would write a recordset loop that sorts by the duplicate column moves through them taking appropriate action.
 

CharlesDavenport

New member
Local time
Today, 07:47
Joined
Dec 7, 2020
Messages
26
If you have a situation where you have a column that data is duplicated in, and you can 'go' by that - then it becomes very easy.
Example, I have 10 records. Each record is duplicated 2, maybe 3 or 4 times. The duplicate values can be identified (perhaps among other ways), but a column like ID. There are 2 records with ID 2, 3 record with ID 4, and so on.

Your query logic becomes something like:

delete * from tablename where id not in (select top 1 id from tablename as tn where tn.id = tablename.id)

.... deleting all but the top 1 will randomly preserve one record.

Any other situation and personally I would write a recordset loop that sorts by the duplicate column moves through them taking appropriate action.
Unfortunately I do not have one column with duplicated data.

The table could look like this

IDFormNumberJobReferenceClientName
12E003This Company
22T001That Company
35E003This Company
45V006Various Companies
55R001Random Company
66V006Various Companies
76R001Random Company
86H004His Company
92E003This Company
102T001That Company
112V006Various Companies

So the Duplicate Data is all of the fields in ID 1, 2, 9 & 10 and i need to delete one instance of each of the duplicates only ie ID1 & 2 whilst keeping ID 9, 10 & 11(which is new data and not a duplicate)

Does this make sense?

Not even tried to get my head around it yet but i know ill be back here asking for any assistance!
 

Users who are viewing this thread

Top Bottom