Looping through recordset uses last record to update all records

R

rctong

Registered User.
Local time
Today, 21:19
Joined
Apr 1, 2009
Messages
11
Hi Guys,

I have 5 subforms that run off 5 separate queries that are filtered by the criteria pointing to each day of the week. I have put together the following but it updates the 'ptemp' in each record with the last value it calculated, i.e. the correct value for the last record. Obviously i'm looking for each record to be dealt with on its own.

This is my first go at putting together a loop from scratch. Any ideas? :)

Code: 
Private Sub Nearby_Click()
Dim postcode1 As String
Dim postcode2 As String
Dim sHold As String
Dim strSQL As String

Dim Rst As DAO.Recordset
Dim db As DAO.Database


'On Error GoTo Errorskip

postcode1 = Me.postcode1.Value
strSQL = "SELECT * FROM qryDate"
Set Rst = CurrentDb.OpenRecordset(strSQL)


Rst.MoveFirst
Rst.MoveLast

 Do While Not Rst.EOF

            
            
     If Rst.RecordCount = 0 Then
     
            Exit Sub
     Else
            postcode2 = Rst![Postcode]
            
sHold = PostcodeSearch(postcode1, postcode2)
            Forms![frmCalendar]![Date]![ptemp] = Mid(sHold, InStr(sHold, "|") + 1)
     End If
     
Rst.MoveNext

Loop

Rst.Close
db.Close
    
'Errorskip:
' Resume Next
 
End Sub
 
What is PostcodeSearch? qryDate?
What are you trying to do in plain English?

Can you post a dumbed down version of your database (remove private/confidential stuff)?

If we're going to help we need some info.
 
Postcode search is a complex function that takes two values postcode1 and postcode2 and asks google what the distance and time is between these. So if you typed say "sw19 to cf24" into maps.google.co.uk it would give you the same answer.

What I am trying to do is loop through each record in the query (qrydate) which has a the field [postcode] and let the fuction postcodesearch use the field [postcode] from qrydate be postcode2 and postcode1 be the value in an unbound text box "postcode1".

So postcode1 doesn't change and there is only one value. postcode2 comes from the field postcode in the query "qrydate" of which there are many records. ptemp is another field in "qrydate" which is blank and not bound to a table.

Anyway basically i was hoping some guru on looping might know what is generally wrong with the structure of my code which would make the loop take the last record result and post it in every record!

Would prefer not to upload a dumbed down version yet. Let me know if there is any other info you need and thanks for getting back so quickly!
 
Hi, thanks jdraw but that part works fine and i have been using it elsewhere. The issue is with the loop not the code to get the distance/time between to postcodes. In fact it displays the correct distance/time but only for the last record in the recordset and it then takes that value and puts it in the ptemp for all the records. I need the code run individually for each record...finish...then move to the next and repeat.

Thanks
 
Add another

Rst.MoveFirst right after your Rst.MoveLast

I think you are only reading the last record in the recordset.
 
No change, still does the same as before.
 
Post a copy of your database.
 
I do, tried it to death. I have tried so many different ways of doing loops etc.

Do you know of any good loop templates that might help??
 
Last edited:
Watch this video --- I just found it. He starts with an existing issue , then modifies a loop and tests it.
https://www.youtube.com/watch?v=I7ntnYi9OUw

You don't have to have a click event to run a loop. Try to use simple examples.
Here's one of mine.
Code: 
Sub Test()    'just testing the rs("Countryname")
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Countries")
    Do While Not rs.EOF
        Debug.Print "Record  " & rs("Countryname")
        rs.MoveNext
    Loop
End Sub
 
Had a look at the video and a few other samples and changed the code a bit. Its cleaner and still calculates the time/distance perfectly, however now it only remembers the last record it worked on which is at the end of the query and posts that result in the first records ptemp field :S

Code: 
Private Sub Nearby_Click()
Dim postcode1 As String
Dim postcode2 As String
Dim sHold As String
Dim strSQL As String

Dim Rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb

'On Error GoTo Errorskip

postcode1 = Me.postcode1.Value

Set qdf = db.QueryDefs("qrydate")
qdf.Parameters(0) = Forms!frmcalendarmain!frmCalendarWeek!txt1
Set Rst = qdf.OpenRecordset
Rst.MoveFirst

Do Until Rst.EOF
           postcode2 = Rst![Postcode]
            
sHold = PostcodeSearch(postcode1, postcode2)
            Forms![frmCalendar]![Date]![ptemp] = Mid(sHold, InStr(sHold, "|") + 1)

Rst.MoveNext

Loop

Rst.Close
qdf.Close
    
'Errorskip:
' Resume Next
 
End Sub
 
Try this, I just added a couple of lines (in red) to your code. Try this and let's see if we can't find out what's going on.

What do you want to happen?

Code: 
Private Sub Nearby_Click()
Dim postcode1 As String
Dim postcode2 As String
Dim sHold As String
Dim strSQL As String
[COLOR="Red"]Dim i As Integer
i = 0[/COLOR]
Dim Rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb

'On Error GoTo Errorskip

postcode1 = Me.postcode1.Value

Set qdf = db.QueryDefs("qrydate")
qdf.Parameters(0) = Forms!frmcalendarmain!frmCalendarWeek!txt1
Set Rst = qdf.OpenRecordset
Rst.MoveFirst

Do Until Rst.EOF
           postcode2 = Rst![Postcode]
 [COLOR="Red"]i = i + 1[/COLOR]
sHold = PostcodeSearch(postcode1, postcode2)
            Forms![frmCalendar]![Date]![ptemp] = Mid(sHold, InStr(sHold, "|") + 1)
[COLOR="Red"]Debug.Print i & "  sHold  " & sHold[/COLOR]
Rst.MoveNext

Loop

Rst.Close
qdf.Close
    
'Errorskip:
' Resume Next
 
End Sub
 
Are you trying to update records displayed in a form? If so you really should just be using an Update query and then refreshing the form.
 
jdraw - no change, i stepped through with you lines but it does that same thing. i see where your going with it though.

boblarson - sort of but i only what the update to be temporary, i want it to be store until a refresh, form close or the button is clicked again.
 
Hit cntl G and look in the immediate window, you should see a sequence of numbers , a string "shold" and values for variable shold.

I think you should tell us exactly what you think you are doing. It could just be that we don't understand because you haven't told us specifically the inputs, the outputs and where the outputs are suppose to be.
 
1--------- sDistance 133 mi
2------ sDuration 2 hours 40 mins
1 sHold 133 mi|2 hours 40 mins
1--------- sDistance 133 mi
2------ sDuration 2 hours 40 mins
2 sHold 133 mi|2 hours 40 mins
1--------- sDistance 1 ft
2------ sDuration 1 min
3 sHold 1 ft|1 min

All the inputs and shold output are spot on, notice there were only 3 records in the recordset i used. The last value (shold 1 ft 1 min) is being sent to the first records ptemp value, this record should be 133 mi|2 hours 40 mins as above.

So to simplify:

Code: 
Record/Row    Should give value in ptemp        ptemp actual value

1                  133 mi|2 hours 40 mins             1 ft|1 min
2                  133 mi|2 hours 40 mins             *blank
3                  1 ft|1 min                              *blank
 
Last edited:
All the debug.print to the immediate window is doing is confirming that you are going through the loop
The logic of your program is what causes/prevents/forgets to update some fields or forms.

Since your loop is working it is now up to you to sort out where your output is suppose to go.

I have offered to assist if you post a database. But I still have no confirmation of what you are trying to do, and how your code (including the queries and forms that we can't see) matches what you want to happen.

The format of your debug,print looks surprisingly like the output in my Distance between 2 zipcodes routine.

Anyway, good luck with your project.
 
WORKS! amazing, thank you for helping push me in the right direction!

Code: 
Private Sub Nearby_Click()
Dim postcode1 As String
Dim postcode2 As String
Dim value As String
Dim sHold As String
Dim strSQL As String

Dim Rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
'On Error GoTo Errorskip

postcode1 = Me.postcode1.value

Set qdf = db.QueryDefs("qrydate")
qdf.Parameters(0) = Forms!frmcalendar!txt1
Set Rst = qdf.OpenRecordset
Rst.MoveFirst

Do Until Rst.EOF
postcode1 = Me.postcode1.value
postcode2 = Rst![Postcode]
       
sHold = PostcodeSearch(postcode1, postcode2)

Rst.Edit
Rst!ptemp = Mid(sHold, InStr(sHold, "|") + 1)
Rst.Update

Rst.MoveNext
Loop

Rst.Close
qdf.Close
Set Rst = Nothing
Set db = Nothing
    
'Errorskip:
' Resume Next

DoCmd.Requery

End Sub
 
You must log in or register to reply here.

Similar threads

C
Matching vba recordset to form recordset
Replies
12
Views
1,006
Gasman
Gasman
C
Solved Position form to added record
Replies
8
Views
706
ClaraBarton
C
F
NESTED LOOPS
Replies
2
Views
340
fool_vio
F
G
Solved Update recordset skipping the last record
2
Replies
21
Views
1,480
The_Doc_Man
The_Doc_Man
M
Solved Update records through a recordset
Replies
10
Views
1,588
MadPiet
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom