Do while ... Loop

[a.phillips]

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Set rst1 = CurrentDb.OpenRecordset("HtoROrignial")
Set rst2 = CurrentDb.OpenRecordset("subfolder")
Do While Not rst1.EOF
Do While Not rst2.EOF
If rst2("Fname") <> 0 Then
strSQL = "INSERT INTO subfolder (notes) VALUES ('this works')"
DoCmd.RunSQL
End If
rst2.MoveNext
Exit Do
rst1.MoveNext
Exit Do
End Sub

Hi, above is my code, it isn't running the SQL, and there is a problems with the loop (is this because you can't have a loop within a loop?)

I basically want the code to search through all of the values in table 1, against all of the values in table 2 to see if the field Fname matches the field Fname in table 1 (which is what the if will eventually do, just want the loop to work first)

Any help/tips/hints will be much appreciated.

Thanks in advance

 

[gemma-the-husky]

can you not just do this in a query?

 

[a.phillips]

Thats what I initially planned on doing, but theres nothing to link the tables on (as the database was just designed for seeing if there was duplicate records in 2 diffrent folders) and when I add both of the tables into the query it brings back far to many results.

Example
table 1 will contain
1
2
3
4

table 2 will contain
a
b
c
d

and the query will display as
1 a
1 b
1 c
1 d
2 a
2 b
2 c
2 d
etc.

 

[nanscombe]

Try this ...

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String

Set rst1 = CurrentDb.OpenRecordset("HtoROrignial", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("subfolder", dbOpenDynaset)

Do While Not rst1.EOF

  Do While Not rst2.EOF

    'If rst1!Fname <> rst2!Fname Then
    '  strSQL = "INSERT INTO subfolder (notes) VALUES ('this works')"
    '  DoCmd.RunSQL
    'End If

' Just output the results to the immediate window.
    Debug.print rst1!FName & " " & rst2!FName 

  rst2.MoveNext
  Loop

  rst1.MoveNext
  rst2.MoveFirst

Loop

rst1.Close
Set rst1 = Nothing

rst2.Close
Set rst2 = Nothing
End Sub

You may also find that indenting your Loop helps you to keep track of what's going on.

 

[a.phillips]

Thank you very much, I will give this a go!

 

[nanscombe]

I've just made a slight change so it "Debug.Prints" the result into the immediate window so you can see the result easier.

 

[vbaInet]

and the query will display as
1 a
1 b
1 c
1 d
2 a
2 b
2 c
2 d
etc.

By the way, is this what you want it to become or is it what it looks like when you do it in a query?

 

[a.phillips]

Thank you very much for that code, and it seems to work in the imidiate window, but I think i still need the SQL part in as I need to update the notes section on that table. When I uncomment the SQL section it brings up the error complie error, argument not optional, any thoughts on this.

 

[a.phillips]

By the way, is this what you want it to become or is it what it looks like when you do it in a query?

This is what it looks like in the query, but I understand why it is doing this.

I would prefer it all to be done in code if possible

 

[vbaInet]

Ok, but based on your example above, what results do you want to get?

 

[a.phillips]

Ok, so basically I am comparing 2 folders, to see which fiels are the same, so duplicates can be deleted. There is an update button which gets:
The file name
Modified date
and size
of each file in a folder and puts them in 2 seperate tabels.

I need the code to compare the data in all of the fields with each other to see if they are an exact match, or if only certain parts match. E.g. if the filename matches, data may need to be copied over to the newist version.

This will be done in the If statements (which I have not put in the code as I think I will be able to manage them). For example if the file name, modified date and size are exactally the same, it should enter duplicate into the notes section.

It is just the loops I am having problems with

Hope this has made it a bit more clear

Thanks

 

[vbaInet]

So like you did in post #3, why not show us what you have and what you would like it to be. You can do it in a spreadsheet and post that.

 

[nanscombe]

I'm beginning to see what you are up to. Rather than looping through an entire recordset looking for a match you can use the FindFirst method to look for a match instead.

Dim rst1 As Recordset
Dim rst2 As Recordset
Dim strSQL As String
Dim isIdentical as Boolean, matchingFileFound as Boolean

Set rst1 = CurrentDb.OpenRecordset("HtoROrignial", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("subfolder", dbOpenDynaset)

' =========================================
rst1.MoveFirst
Do While Not rst1.EOF

  strSQL = "FName = '" & rst1!FName & "'"
  isIdentical = true
' Look for an occurrence of rst1!FName in rst2
  rst2.FindFirst strSQL
  
  If rst2.NoMatch then
' If no match is found do this
    isIdentical = false
    matchingFileFound = false

  Else
' If a match is found then do this
    matchingFileFound = true
    If rst1!FSize <> rst2!FSize then isIdentical = false
    If rst1!FDate <> rst2!FDate then isIdentical = false

  Endif

  If (matchingFileFound = true) AND (isIdentical = false) then
' Do something with matchingFileFound and isIdentical here
  End If

  rst1.MoveNext

Loop

' =========================================

rst2.MoveFirst
Do While Not rst2.EOF

  strSQL = "FName = '" & rst2!FName & "'"
  isIdentical = true

' Look for an occurrence of rst2!FName in rst1
  rst1.FindFirst strSQL
  
  If rst1.NoMatch then
' If no match is found do this
    isIdentical = false
    matchingFileFound = false

  Else
' If a match is found then do this
    matchingFileFound = true
    If rst2!FSize <> rst1!FSize then isIdentical = false
    If rst2!FDate <> rst1!FDate then isIdentical = false

  Endif

  If (matchingFileFound = true) AND (isIdentical = false) then
' Do something with matchingFileFound and isIdentical here
  End If

  rst2.MoveNext

Loop

' =========================================

rst1.Close
Set rst1 = Nothing

rst2.Close
Set rst2 = Nothing
End Sub

This what I have so far but it only requires one loop at a time.

Obviously I don't know what your size and date fields are but I took an educated guess.

 

[a.phillips]

Thank you, this has been very helpful, I will keep you updated on my progess. Thanks again

 

[a.phillips]

Nanscombe, I understand the code you wrote and it does seem good, but I am getting an error on this bit of code:

strSQL = "FName = '" & rst1!FName & "'"
isIdentical = True
' Look for an occurrence of rst1!FName in rst2
rst2.FindFirst strSQL

It is the bottom line of code that the error is on, with the error:

Runtime error 3077, syntax error (missing opperator) in expression.

Hope you can help me with this, thanks for your time

 

[a.phillips]

Also, in the SQL string in this part of the code:

If rst2.NoMatch Then
' If no match is found do this
strSQL1 = "INSERT INTO Subfolder (notes) VALUES ('no match');"
DoCmd.RunSQL strSQL1
isIdentical = False
matchingFileFound = False

What is the best way to say where the coulm = fname (so the 'no match' goes into the correct record')

Thanks

 

[nanscombe]

Nanscombe, I understand the code you wrote and it does seem good, but I am getting an error on this bit of code:

strSQL = "FName = '" & rst1!FName & "'"
isIdentical = True
' Look for an occurrence of rst1!FName in rst2
rst2.FindFirst strSQL

It is the bottom line of code that the error is on, with the error:

Runtime error 3077, syntax error (missing opperator) in expression.

Hope you can help me with this, thanks for your time

If you add the line below in bold you can at least see what the contents of strSQL are which may give a clue as to the problem.

  strSQL = "FName = '" & rst1!FName & "'"
  isIdentical = True
' Look for an occurrence of rst1!FName in rst2
  [B]Debug.Print strSQL[/B]
  rst2.FindFirst strSQL

==============================================

Also, in the SQL string in this part of the code:

If rst2.NoMatch Then
' If no match is found do this
strSQL1 = "INSERT INTO Subfolder (notes) VALUES ('no match');"
DoCmd.RunSQL strSQL1
isIdentical = False
matchingFileFound = False

What is the best way to say where the coulm = fname (so the 'no match' goes into the correct record')

Thanks

Since there is a record in rst1, but no matching record in rst2, and as we have the recordsets open already I would not use SQL but would update the record directly.

  If rst2.NoMatch then
   [B] rst1.edit
    rst1!Note = "No Matching record" ' Or whichever field you are using
    rst1.Update[/B]
    isIdentical = False
    matchingFileFound = False
....
  End If

And similarly for rst1.NoMatch

  If rst1.NoMatch then
    [B]rst2.edit
    rst2!Note = "No Matching record" ' Or whichever field you are using
    rst2.Update[/B]
    isIdentical = False
    matchingFileFound = False
....
  End If

 

[a.phillips]

Thankyou, that is much easier and very useful! You have saved me a lot of time.

 

[nanscombe]

Oh Bother! I've been blinded by my love of coding.

Two tables (HtoROriginal & subfolder).
Both linked by field FName
Set field 'Note' to something if there is no match

A lovely bit of code or two relatively simple queries

Update HtoROriginal 'Note' if there is no matching record in subfolder

UPDATE HtoROriginal LEFT JOIN Subfolder ON HtoROriginal.FName = Subfolder.FName SET HtoROriginal.[Note] = "No Match"
WHERE (((Subfolder.FName) Is Null));


Update subfolder 'Note' if there is no matching record in HtoROriginal

UPDATE HtoROriginal RIGHT JOIN Subfolder ON HtoROriginal.FName = Subfolder.FName SET Subfolder.[Note] = "No Match"
WHERE (((HtoROriginal.FName) Is Null));

It always comes to you when you're not thinking about it.

 

[a.phillips]

Thank you.

I dont require the code yet .. but would it be possible to delete the file from one of the folder (sub-folder) IF it says exact match?

Also can you give me some advice on the best way to develop my skills in vba? I have been using it for a while but still dont fully understand all aspects. I have never been tought it from college or uni, but have been doing some reading and have picked up small bits from working with databases.

Thanks