473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to Loop Records in Access

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")

If RST.EOF Then
RST.MoveFirst
Do While Not RST.EOF
RST.MoveNext
Loop
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
6 8015
MindBender77
234 100+
Hello and Welcome to Bytes.com,

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
  7.  
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.
http://msdn.microsoft.com/en-us/libr...a7(VS.80).aspx

Hope this points you in the right direction,
Bender
Dec 12 '08 #2
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?

Viv
Dec 12 '08 #3
ADezii
8,834 Expert 8TB
@VivDenham
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
  4.  
  5. lngLoopCounter = lngLoopCounter + 1
  6.  
  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
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
ADezii
8,834 Expert 8TB
@VivDenham
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
Thanks ADezii, I thought I was probably asking for the impossible. Thanks for all your help regarding the Loop.
Dec 14 '08 #7

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

Similar topics

2
by: Ted Duross | last post by:
Hi, I’ve created an educational software application accessed by students from a central server. On Start-Up the application reads an XML file that stores student progress records. This XML...
6
by: MS | last post by:
Access 97 here. I want a simple way to "lock" certain records on a form. Some records remain "live" until all data is available which happens over time. When all the fields are complete, I want...
2
by: boyleyc | last post by:
Hi all the following code works perfectly well. Basically it populates a series of check boxes on my form, depending on whether dlookup finds an associated record. The problem i have is that...
5
by: kevin | last post by:
Hi, Hope you can help - I can't see the wood for the trees any more. I currently have a local MS Access file. I'd like to do this in VB.NET: connect to file (select * from table) for...
3
by: hbean | last post by:
I'm working on an event procedure for a form - seems relatively straightforward to me, but for some reason Access keeps crashing on me. All the procedure is doing is checking to see if a recordset...
2
by: VSS | last post by:
Hello, I need to know what is the maximum number of records Access can hold? I need approx. 6 million records, do you know if Access can hold this amount? Thank you everyone.
14
by: gilsygirl | last post by:
Hello everyone Am using a For Loop to access records from my database but the code opens instead of a specific record base on values passed by the user. These values a fields in the database...
1
by: elbatz | last post by:
Hi! Does anyone know the code of how to delete records in access using excel as reference. example: Delete * FROM table1 WHERE table1.ID=excelfile.ID ? Thanks
2
by: puT3 | last post by:
How can I make the report do not show any empty records? For example, in this table do not has address...I want the report only shows company that has address
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.