HOW TO?!?! Display a record # in a message box

[Nick34]

I need help displaying the 30th record in a table. I don't know the language to get the query to display the 30th record each time (the 30th record will be a different value every day). I would like to have this record displayed in a message box. Does anyone know how to do this??

Any help would be great.

 

[PaulBricker]

You could create a module like this
Sub movetoThirty()
Dim rst As DAO.Recordset
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("Work Orders", dbOpenDynaset)
rst.MoveFirst
For i = 2 To 30
rst.MoveNext
Next i
MsgBox "This is the 30th record " & rst![FieldNameHere]
End Sub

and then call it with a button or an event to display the message box.

Paul

 

[TheAceMan1]

How are ya Nick34 . . . . . . .

Give the table name and name/data type of each field you wish to display.

TheAceMan

 

[gol4]

option1
what you are asking for does not make a lot of sense to me as there really is no 30th record in a relational database
but here are a few options
option1
dim rst as recordset
set rst = currentdb.openrecordset("table1&quot
for x = 1 to 29
rst.movenext
next
msgbox rst.field(0)
rst.close
set rst= nothing

option2
me.list1.recordsource = "table1"
msgbox(list1.column(1,29)

option3
DoCmd.GoToRecord acDataForm, "form1", acGoTo, 7
msgbox text1.text

 

[Nick34]

I am getting the error message "Invalid Argument", and the line "Set rst = CurrentDb.OpenRecordset("U1 Start Date Q", dbOpenDynaset) seems to be the one causing the problem.

The Query name is "U1 Start Date Q" and the only field it contains is a date field.

This is so hard....

 

[Nick34]

I have another idea but I do not know how to tell the query to simply return the 30th record every time.

 

[PaulBricker]

I just renamed on of my queries to U1 Start Date Q and ran the code like this

Sub thirty()
Dim rst As DAO.Recordset
Dim i As Integer
Set rst = CurrentDb.OpenRecordset("U1 Start Date Q", dbOpenDynaset)
rst.MoveFirst
For i = 2 To 30
rst.MoveNext
Next i
MsgBox rst!myCalc
End Sub

and it ran fine. Not sure what the problem is with the invalid argument.

Paul

 

[Nick34]

Paul, now I am getting a complie error that says "User defined type not defined". The line that gets highlighted from this error is "rst As DAO.Recordset". All I have to do is paste this into a new module and then change the "rst!myCalc" from myCalc to whatever the name of my field is, right??? In this case the name of that field is "DATE".

 

[Nick34]

Paul I just figured it out. I had to add as a reference the Microsoft DAO 3.6 Object Library.

THANKS FOR ALL THE HELP!!!!

 

[PaulBricker]

Good job getting the reference set.

Paul