Loop through records problem. (1 Viewer)

[MMA]

Software version: Microsoft Access 2003

I have a table called SoldImagesCars
This table has three fields

id (primary key)
lotno (Data Type - number)
regno (Data Type - Text)

I also have a form called frmSplash which gets the data from the above table.

The following code VB code is called using a Command Button on the form fmrSplash and moves the matching images for the first row in the table.
i.e. id=1, lotno=156, regno=aa05 aaa
Image in C:\testfolder\Cars\ folder named 156 - aa05 aaa

            Private Sub Command464_Click()
Dim fso, f, f1, fc, s
            Set fso = CreateObject("Scripting.FileSystemObject")
            Set f = fso.GetFolder("[URL="file://\\Mma6\backup"]\[COLOR=#000000]C:\testfolder\Cars\[/COLOR][/URL]") '
               Set fc = f.Files
               FileCount = 0
               If fc.Count <> 0 Then
                  For Each File In fc
                  If InStr(UCase(File.Name), ".jpg") > 0 Then
                  FileCount = FileCount + 1
                    Call MoveAndRenameMyFile("C:\testfolder\Cars\", Me![lotno] & " - " & Me![RegNo] & "*.jpg", "C:\testfolder\Cars\Sold\" & Me![lotno] & " - " & Me![RegNo] & " (" & FileCount & ").jpg")
                  Else
                  End If
                  Next
                 
            End If
End Sub

This code works perfectly to move the matching images for the first row in the table.

The problem I'm having is that I would like the code to loop to move pictures for all the rows in the table called SoldImagesCars.
There would only be about 10-15 rows in this table.

I've tried a few loop examples I found on the web but to no avail. What do I need to do?

Many thanks in advance,

Rhys

 

[jardiamj]

You could open a Recordset of the table and loop through all the records to move the pictures; for every record.
I hope this helps. Cheers!

 

[MMA]

Thats the problem, I've tried all sorts of code to do this and they all come back with errors...

VB is not my strong point.

Is there any sample code I could use to do so?

Many thanks in advance once again!

 

[DCrake]

How to loop through a recordset using vba

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("YourTableName")

If Not Rs.EOF And Not Rs.BOF Then

   Do Until Rs.EOF
       For N = 0 To Rs.Fields.Count -1
           Debug.Print Rs(N).Name
       Next
       Rs.MoveNext
   Loop
   Rs.Close
End If

Set Rs = Nothing

In a nutshell

In the above example a table is selected by the recordset in no particular order. It is tested to see if there are any records in the table.
Next is begins a loop starting at the first record found. then for each field in the table it prints the name in the immediate window. It then moves to the next record in the table.

When it reaches the end of the file it closes the recordset then releases the recordset from memory.

David

 

[MMA]

Thank you!

The following error occurs:

Complie Error:

User-defined type not defined

Highlighted in VB -

Dim Rs As DAO.Recordset

 

[DCrake]

Check your tools and references and add

Microsoft DAO n.nn Object Library