Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 08-27-2014, 07:10 AM   #1
The Archn00b
Newly Registered User
 
Join Date: Jun 2013
Posts: 76
Thanks: 9
Thanked 0 Times in 0 Posts
The Archn00b is on a distinguished road
Question Loop through recordset not recognizing .MoveNext?

Hello,

So I have a table "zztblArticles." Some fields should go to "tblArticles" and values in Tag_ID should go to a lookup table "tblTag" and a junction table "tblArticles_Tags." I'll explain the code I've written below:


Code:
Private Sub cmdSubmit_Click()
    
    Dim db As Database
    Dim strINSERT As String
    Dim strVALUES As String
    Dim rszztblArticles As DAO.Recordset
    
    Set db = CurrentDb
    Set rszztblArticles = db.OpenRecordset("SELECT zztblArticles.Publishing_Date, zztblArticles.Title" & _
        ", zztblArticles.Source_ID, zztblArticles.Sourcer_ID, zztblArticles.Sourcing_Date" & _
        ", zztblArticles.Source_Category_ID, zztblArticles.Location, zztblArticles.Comments, zztblArticles.Tag_ID FROM zztblArticles;")
        
    strINSERT = "INSERT INTO tblArticles (Publishing_Date, Title, Location, Sourcing_Date, Comments "
    strVAlUES = " VALUES ('" & DateDiff("d", #5/16/2014#, rszztblArticles!Publishing_Date) & _
        "', '" & rszztblArticles!Title & "', '" & rszztblArticles!Location & _
        "', '" & rszztblArticles!Sourcing_Date & "', '" & rszztblArticles!Comments & "'"

    With rszztblArticles
    
    .MoveFirst
    
    Do While .EOF = False
            
            'Creates sql to append to single-value fields from zztblArticles to tblArticles
            If IsNull(!Source_ID) = False Then
            strINSERT = strINSERT & ", Source_ID"
            strVAlUES = strVAlUES & ", '" & !Source_ID & "'"
            End If
            
            If IsNull(!Sourcer_ID) = False Then
            strINSERT = strINSERT & ", Sourcer_ID"
            strVAlUES = strVAlUES & ", '" & !Sourcer_ID & "'"
            End If
            
            If IsNull(!Source_Category_ID) = False Then
            strINSERT = strINSERT & ", Source_Category_ID"
            strVAlUES = strVAlUES & ", '" & !Source_Category_ID & "'"
            End If
            
            'Appends single-value fields to tblArticles
            db.Execute (strINSERT & ")" & strVALUES & ")")
            
            'Finds the automatically generated ID in tblArticles by matching Sourcing_Date
            intArticleID = DLookup("ID", "tblArticles", "Sourcing_Date = " & !Sourcing_Date)
            
'Creates an array from the Tag ID value which could be (England, Scotland, Wales)
            TagsArray = Split(!Tag_ID, ",", , vbTextCompare)
            
            Dim i As Integer
'For each item in the Array            
            For i = LBound(TagsArray) To UBound(TagsArray)
                If IsNull(DLookup("Tag", "tblTag", "tblTag.Tag = '" & Trim(TagsArray(i)) & "'")) Then
                db.Execute ("INSERT INTO tblTag (Tag) VALUES ('" & Trim(TagsArray(i)) & "')")
                End If
                intTagID = DLookup("ID", "tblTag", "tblTag.Tag = '" & Trim(TagsArray(i)) & "'")
                db.Execute ("INSERT INTO tblArticles_Tags (Tag_ID, Article_ID) VALUES (" & intTagID & "," & intArticleID & ")")
            Next i
            
            db.Execute ("DELETE * FROM zztblArticles WHERE Sourcing_Date = " & rszztblArticles!Sourcing_Date)
        .MoveNext
    
    Loop
    
    .Close
    
    End With
    
    Set db = Nothing
    db.Close
    MsgBox ("Your articles have been uploaded successfully")
    
    Exit Sub
            
End Sub
The first loop through works fine, I get the records uploaded to all tables. The second loop through fails at

Code:
intArticleID = DLookup("ID", "tblArticles", "Sourcing_Date = " & !Sourcing_Date)
Because it can't decide which ID value to use. This is because the value has been duplicated in tblArticles after the code acts on the same record again. It has completely failed to move to the next record in the recordset, despite the .MoveNext before the Loop!

Any ideas?

Thanks!

The Archn00b is offline   Reply With Quote
Old 08-28-2014, 12:12 AM   #2
The Archn00b
Newly Registered User
 
Join Date: Jun 2013
Posts: 76
Thanks: 9
Thanked 0 Times in 0 Posts
The Archn00b is on a distinguished road
Re: Loop through recordset not recognizing .MoveNext?

Is there anything I can make clearer? I know it's a quite a long question.
The Archn00b is offline   Reply With Quote
Old 08-28-2014, 12:23 AM   #3
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Re: Loop through recordset not recognizing .MoveNext?

Quick thought: have you tried using the date delimiter when using dates as criteria? (And brackets around that awkward underscoring in the field name...!!)

Code:
intArticleID = DLookup("ID", "tblArticles", "[Sourcing_Date] = #" & ![Sourcing_Date] & "#")

Mile-O is offline   Reply With Quote
Old 08-28-2014, 12:41 AM   #4
The Archn00b
Newly Registered User
 
Join Date: Jun 2013
Posts: 76
Thanks: 9
Thanked 0 Times in 0 Posts
The Archn00b is on a distinguished road
Re: Loop through recordset not recognizing .MoveNext?

Quote:
Originally Posted by Mile-O View Post
Quick thought: have you tried using the date delimiter when using dates as criteria? (And brackets around that awkward underscoring in the field name...!!)

Code:
intArticleID = DLookup("ID", "tblArticles", "[Sourcing_Date] = #" & ![Sourcing_Date] & "#")
The field Sourcing_Date is actually stored as a number and recalculated into a date using Date_Add on demand. This line of code actually seems to work on the first loop through, but then fails on the second loop, because the recordset hasn't moved to the next record (.movenext not working for some reason). The Sourcing_Date value is then appended AGAIN and so the code can't find the unique value of Sourcing_Date in tblArticles.
The Archn00b is offline   Reply With Quote
Old 08-28-2014, 01:10 AM   #5
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: Loop through recordset not recognizing .MoveNext?

I would suggest that you step through the code to see what is happening and what is not.
__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,


Last edited by RainLover; 08-28-2014 at 01:27 AM.
RainLover is offline   Reply With Quote
Old 08-28-2014, 01:39 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Loop through recordset not recognizing .MoveNext?

if you get a rte, the process will break before processing the .movenext

however, is it possible that the delete has the effect of deleting the records in the recordset, so the .movenext causes .eof to be true, and therefore the block terminates normally?
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 08-28-2014, 02:06 AM   #7
The Archn00b
Newly Registered User
 
Join Date: Jun 2013
Posts: 76
Thanks: 9
Thanked 0 Times in 0 Posts
The Archn00b is on a distinguished road
Re: Loop through recordset not recognizing .MoveNext?

Quote:
Originally Posted by gemma-the-husky View Post
if you get a rte
What's an rte?

The Archn00b is offline   Reply With Quote
Old 08-28-2014, 02:16 AM   #8
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Loop through recordset not recognizing .MoveNext?

All the suggestions made (from Mile-O, Rain and gemma-the-husky) are valid:

1. Step through your code
2. Format your date parameters
3. The Delete execution is probably deleting more records than is necessary or deleting the wrong record. Or the date format (point 2) you're passing to the DLookup can't find a related date. You're in a bit of a recursive loop.

Why don't you do all of this in different queries? We would need to see some data to fully understand what you're trying to accomplish.
vbaInet is offline   Reply With Quote
Old 08-28-2014, 05:23 AM   #9
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: Loop through recordset not recognizing .MoveNext?

Quote:
Originally Posted by The Archn00b View Post
What's an rte?
An RTE is something that makes people look smarter. Groovy talk.

When I went to school I learnt that communication is only good when both parties understand, and to take it further, when each party understands that the other parties understand.

I hope you understand.
__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Old 08-28-2014, 08:23 AM   #10
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Loop through recordset not recognizing .MoveNext?

rte is a run time error.

ie something goes wrong before it gets to the .movement statement.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 08-28-2014, 08:27 AM   #11
The Archn00b
Newly Registered User
 
Join Date: Jun 2013
Posts: 76
Thanks: 9
Thanked 0 Times in 0 Posts
The Archn00b is on a distinguished road
Re: Loop through recordset not recognizing .MoveNext?

Quote:
Originally Posted by RainLover View Post
An RTE is something that makes people look smarter. Groovy talk.

When I went to school I learnt that communication is only good when both parties understand, and to take it further, when each party understands that the other parties understand.

I hope you understand.
uwotm8

Quote:
rte is a run time error.

ie something goes wrong before it gets to the .movement statement.
Ah thanks
The Archn00b is offline   Reply With Quote
Old 09-01-2014, 01:48 AM   #12
The Archn00b
Newly Registered User
 
Join Date: Jun 2013
Posts: 76
Thanks: 9
Thanked 0 Times in 0 Posts
The Archn00b is on a distinguished road
Re: Loop through recordset not recognizing .MoveNext?

Sup fellas. Found the solution after going through it step by step. The dimension "strVALUES" should have been inside the Do/Loop. It wasn't subject to MoveNext and therefore stayed on the same record!

Code:
Private Sub cmdSubmit_Click()
    
    Dim db As Database
    Dim strINSERT As String
    Dim strVALUES As String
    Dim rszztblArticles As DAO.Recordset
    
    Set db = CurrentDb
    Set rszztblArticles = db.OpenRecordset("SELECT zztblArticles.Publishing_Date, zztblArticles.Title" & _
        ", zztblArticles.Source_ID, zztblArticles.Sourcer_ID, zztblArticles.Sourcing_Date" & _
        ", zztblArticles.Source_Category_ID, zztblArticles.Location, zztblArticles.Comments, zztblArticles.Tag_ID FROM zztblArticles;")
        
    strINSERT = "INSERT INTO tblArticles (Publishing_Date, Title, Location, Sourcing_Date, Comments "
    
    With rszztblArticles
    
    Do While .EOF = False
            
            'Creates sql to append to single-value fields from zztblArticles to tblArticles

strVALUES = " VALUES ('" & DateDiff("d", #5/16/2014#, rszztblArticles!Publishing_Date) & _
        "', '" & rszztblArticles!Title & "', '" & rszztblArticles!Location & _
        "', '" & rszztblArticles!Sourcing_Date & "', '" & rszztblArticles!Comments & "'"
            If IsNull(!Source_ID) = False Then
            strINSERT = strINSERT & ", Source_ID"
            strVAlUES = strVAlUES & ", '" & !Source_ID & "'"
            End If
            
            If IsNull(!Sourcer_ID) = False Then
            strINSERT = strINSERT & ", Sourcer_ID"
            strVAlUES = strVAlUES & ", '" & !Sourcer_ID & "'"
            End If
            
            If IsNull(!Source_Category_ID) = False Then
            strINSERT = strINSERT & ", Source_Category_ID"
            strVAlUES = strVAlUES & ", '" & !Source_Category_ID & "'"
            End If
            
            'Appends single-value fields to tblArticles
            db.Execute (strINSERT & ")" & strVALUES & ")")
            
            'Finds the automatically generated ID in tblArticles by matching Sourcing_Date
            intArticleID = DLookup("ID", "tblArticles", "Sourcing_Date = " & !Sourcing_Date)
            
'Creates an array from the Tag ID value which could be (England, Scotland, Wales)
            TagsArray = Split(!Tag_ID, ",", , vbTextCompare)
            
            Dim i As Integer
'For each item in the Array            
            For i = LBound(TagsArray) To UBound(TagsArray)
                If IsNull(DLookup("Tag", "tblTag", "tblTag.Tag = '" & Trim(TagsArray(i)) & "'")) Then
                db.Execute ("INSERT INTO tblTag (Tag) VALUES ('" & Trim(TagsArray(i)) & "')")
                End If
                intTagID = DLookup("ID", "tblTag", "tblTag.Tag = '" & Trim(TagsArray(i)) & "'")
                db.Execute ("INSERT INTO tblArticles_Tags (Tag_ID, Article_ID) VALUES (" & intTagID & "," & intArticleID & ")")
            Next i
            
            db.Execute ("DELETE * FROM zztblArticles WHERE Sourcing_Date = " & rszztblArticles!Sourcing_Date)
        .MoveNext
    
    Loop
    
    .Close
    
    End With
    
    Set db = Nothing
    db.Close
    MsgBox ("Your articles have been uploaded successfully")
    
    Exit Sub
            
End Sub
Thanks!
The Archn00b is offline   Reply With Quote
Old 09-02-2014, 05:40 AM   #13
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: Loop through recordset not recognizing .MoveNext?

Quote:
Originally Posted by RainLover View Post
I would suggest that you step through the code to see what is happening and what is not.


You said you found a solution which is good.

If you had followed my suggestion # 5 you would have learnt how to solve many of problems that have a similar grounding.
VBA in post 8 also suggest the same.

__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Reply

Tags
loop , movenext , recordset

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems with recordset.movenext Richard_1 Modules & VBA 6 04-30-2009 08:03 AM
Email loop not recognizing apostrophe TallMan Modules & VBA 6 03-25-2009 01:25 PM
loop through recordset gpass Modules & VBA 5 03-17-2007 10:34 AM
recordset loop Sleekmac Modules & VBA 5 10-16-2006 12:13 PM
Loop and MoveNext problem! Markvand Modules & VBA 7 11-30-2004 12:50 PM




All times are GMT -8. The time now is 12:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World