Looping through a table

[LB79]

Hi all,

Can anyone please tell me how to loop through a table.
I have a table with an undetermined number of records.
I want my vba to msgbox a field of the first row, then the second row etc etc.
Im just stuck on looping through the table.

Thanks for any help.

 

[vbaInet]

I have a table with an undetermined number of records.

You can always determine the number of records in a table.

I want my vba to msgbox a field of the first row, then the second row etc etc.
Im just stuck on looping through the table.

Thanks for any help.

Care to share what the point of this exercise is? Or what your overall goal is?

 

[LB79]

Its just a message box to remind users what tasks are due on the current day. When they log in it will give a message box with 5 or 6 lines saying the task name. I just need to loop the code so that I can shor more than the first record.

 

[vbaInet]

Why don't you pop up all the notifications in one msgbox or textbox?

 

[LB79]

Thats whats im trying to do. The only thing im having trouble with is loopiong through the table that holds the task name and due date.

 

[vbaInet]

First of all create a query to get all the notifications for the current day. Once you've done that I'll tell you the next steps.

 

[LB79]

Thanks.
I already have the query which filters the current date and user.

 

[vbaInet]

How is the User part defined? Is it pointing to a function that returns the current user name?

Here's some aircode:

dim rs as dao.recordset
dim strNotifications as string
 
set rs = currentdb.openrecordset("QueryName", dbopensnapshot)
 
do while not rs.eof
    strNotifications = strNotifications & rs![NotificationsField] & vbnewline
    rs.movenext
loop
 
set rs = nothing

strNotifications will contain your notifications.

 

[LB79]

Thanks for that.
User is a function that detects the log in user name.
Can Dlookup be used with this? Code below as an example:

Public Sub Reminder()
Set RS = CurrentDb.OpenRecordset("qry_REF_Reminder", dbOpenSnapshot)
If RS.RecordCount > 0 Then
RS.MoveFirst
ReminderText = ""
Do While Not RS.EOF
ReminderText = ReminderText & RS![Agent] & Chr(13) & Chr(13)
'ReminderText = ReminderText & DLookup("[Type]", "qry_REF_Reminder") & Chr(13) & DLookup("[Agency]", "qry_REF_Reminder") & Chr(13) & DLookup("Format([DueDate],'DD MMMM YYYY')", "qry_REF_Reminder") & Chr(13) & Chr(13)
RS.MoveNext
Loop
End If
ReminderText = ReminderText & "Would you like to supress further reminders for these items?"
If Eval("MsgBox ('Reminder@" & ReminderText & "@',68,'ATMT')") = vbYes Then
Exit Sub
Else
Exit Sub
End If
Set RS = Nothing
End Sub

 

[LB79]

Please ignore the last post - wasnt thinking! Realised now using

ReminderText = ReminderText & RS![Agent] & Chr(13) & RS![Agency] & Chr(13) & RS![Type] & Chr(13) & Chr(13)


Thanks again

 

[vbaInet]

You're welcome!