By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

How to Loop Records in Access

P: 44
Hi there

I am brand new to VBA - a 61 year old lady who likes machine knitting. For the last couple of years, I have been designing an Access database called Knitting Database.

I want a Welcome screen on which I have a welcome banner, and a OLE photograph of each garment I have knitted. So far, so good.

However, I would like the photo to scroll through all the garments. Again, so far so good. Have set the timer, and used the DoCmd.GoToRecord ... acNext. The photos show for a few seconds then scroll to the next garment knitted, until it gets to the last record. Then it stops so I need it to loop to the first record again.

So I've been on varous forums trying to find out how to Loop records, but I just can't understand how to do it.

My code so far is:

Dim DB As Database
Dim RST As Recordset

Set DB = CurrentDb()
Set RST = DB.OpenRecordset("T - Results - Tops")

Do While Not RST.EOF
End If

The database if called "Knitting Database".
The records are in "Table - Results - Tops".

Could anyone tell me in dum-dum language, exactly how I should write the code. I think there is something wrong with the Object Variable or something???

Many thanks
Viv Denham
Dec 12 '08 #1
Share this Question
Share on Google+
6 Replies

P: 234
Hello and Welcome to,

I believe your on the right track. Your code:
Expand|Select|Wrap|Line Numbers
  1. If RST.EOF Then
  2.          RST.MoveFirst
  3.      Do While Not RST.EOF
  4.          RST.MoveNext
  5.      Loop
  6.      End If
Goes through each record in your table, pauses then displays the next image. However, whenever it reaches the last record, the loop stops. I believe you will need a loop that will repeat until something happens like a user opens a form or a button is pressed.

This might help as well.

Hope this points you in the right direction,
Dec 12 '08 #2

P: 44
Hi Bender

Thanx for speedy reply. My problem seems higher up than that, on line:

Set RST = DB.OpenRecordset("T - Results - Tops")

I get an error message saying "Compile Error - Method or Data member not found" and highlighting the ".Open Recordset" bit.

I can't say if the rest of the code works because I cannot get past the Open Recordset line at the moment.

Any ideas?

Dec 12 '08 #3

Expert 5K+
P: 8,638
Here is 'Generic' code, placed in the Form's Timer() Event that will Loop through all Records in the Record Source of your Welcome Screen, a total of 3 times. The code assumes a TimerInterval of 1000 (1 Record per second). Change Line #13 to a different value to adjust the Number of Loops that you want:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2. Static lngLoopCounter As Long
  3. Static lngNumOfLoops As Long
  5. lngLoopCounter = lngLoopCounter + 1
  7. If lngLoopCounter < Me.Recordset.RecordCount Then
  8.   DoCmd.GoToRecord , , acNext
  9. Else
  10.   lngLoopCounter = 0            'Reset Counter
  11.   DoCmd.GoToRecord , , acFirst
  12.   lngNumOfLoops = lngNumOfLoops + 1
  13.     If lngNumOfLoops = 3 Then   'Has 3 complete Loop Iterations been completed?
  14.       Me.TimerInterval = 0      'disable Timer
  15.       DoCmd.Close               'Close Welcome Screen
  16.         Exit Sub
  17.     End If
  18. End If
  19. End Sub
Dec 13 '08 #4

P: 44
Thanks ADezii - that works brilliantly. Have changed the 3 repeats to 999 so that it goes on infinitely. Also removed the DoCmd.Close as I want the form to remain open. So I AM learning - thanks v.much.

Now............ can I do such a thing as a "Transition" (I've used these in Powerpoint but cannot find anything in Access). When the photos change from one to another, I would like them to fade between each other, rather than just changing instantly. I'm probably asking the impossible, as only the ignorant would!!!

Any ideas?
Dec 13 '08 #5

Expert 5K+
P: 8,638
As far as I know, there is no TRansition effect available in Access, but you may be able to modify the Properties of the Control containing the Images to achieve a similar effect.
Dec 13 '08 #6

P: 44
Thanks ADezii, I thought I was probably asking for the impossible. Thanks for all your help regarding the Loop.
Dec 14 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.