How to MessageBox contents of a table with a button

[alireza1989@hot]

This may seem a very simple question, but I really don't know how to do it. Basically, I want to MessageBox contents of a table with a button. I have a table called Map, and has two fiels: Column Number and Description. When a button is pressed, then I want a messagebox to come up and show the column number and the description of a single field. Although I know it is very wrong, here is the code I used for this (r![column Number] = 4 is meant to be the forth column number in the table):

Dim d As Database
Set d = CurrentDb
Dim r As Recordset
Set r = d.OpenRecordset("Map")


r![column Number] = 4
MsgBox "Column No: " & r![column Number] & ". Description: " & (r![Description])

By the way, there are seperate buttons for seperate fields. It is DAO as well
Thanx in advance

 

[Oldsoftboss]

Your table doesn't have "columns", but field names. Open the table and have a look at the name in the header of each column. This is usually the field name. You can check this by opening the table in design mode.

The way you are displaying data in the msgbox, even if the table names were used, you would display the first record in the table. You need to find the record first, then display the data.

In the references of the vb editor, set a reference to DAO 3.6 (or the like)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strField1 as String
Dim strField2 as String

Set db = CurrentDb()
Set rst = db.OpenRecordset("Map", dbOpenDynaset)

rst.FindFirst MapID = 6 (Your criteria here. we can help with the syntax later)

strField1 = rst!Field1Name
strField2 = rst!Field2Name

MsgBox "Column No: " & strField1 & ". Description: " & strField2

HTH

Dave

 

[alireza1989@hot]

Thanx for your help, here is the code I wrote, and following error comes up (see line 11):

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strField1 As String
Dim strField2 As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("Map", dbOpenDynaset)

rst.FindFirst ID = 6

strField1 = rst!Field1 (here it says item not found in the collection)
strField2 = rst!Field2

MsgBox "ID: " & strField1 & ". Description: " & strField2

Thanx in advance

 

[pbaldy]

"Field1" would need to be the actual name of a field in the data.

 

[alireza1989@hot]

Well, I did that, and I see some improvements, no error messages. But now it only displays the first item in the table, as it did before. Here is the new code I wrote:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strField1 As String
Dim strField2 As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("Map", dbOpenDynaset)

rst.FindFirst ID = 6

strField1 = rst!ID
strField2 = rst!Description

MsgBox "ID: " & strField1 & ". Description: " & strField2

Please guys, I need to get this done as soon as possible
Thanx in advance

 

[pbaldy]

I think this is the first you've mentioned wanting multiple records. First, let's correct the opening of the recordset, which wasn't the most efficient anyway. Change that to:

Set rst = db.OpenRecordset("SELECT * FROM Map WHERE ID = 6", dbOpenDynaset)

and get rid of this line:

rst.FindFirst ID = 6

Now you can do what you want with all the records returned with a loop:

Do While Not rst.EOF
  'do what you want with each record
  rst.MoveNext
Loop

 

[alireza1989@hot]

WOW thanx a lot, that works now.
Thanx for your help mate.