Select Field Values In Table Using Loop

[bigmerf]

I'm trying to use a Do...While loop (or possibly FOR loop) to loop through a table in my Access Database that will display a value of a specific field as it loops through the table.

I have a table (TableA) with 3 fields: (Field1, Field2, Field3).

When pressing a button, I want to loop through TableA, starting with the first record, and select Field3 and display it to the screen.

I'm thinking that I need to use a Do...While loop so it looks something like this:

Dim rs As Recordset
Dim UserField as String

Set rs = db.OpenRecordset("SELECT * FROM TableA")
Do While Not rs.EOF
rs.movefirst

  UserField = ("SELECT Field3 FROM TableA")
  Msgbox "Your results = " & UserField

rs.MoveNext
    Loop
    rs.Close

I'm not too familiar with looping through code, just what I've seen posted here and there and the small microsoft examples I find from time to time. Any ideas on how to loop through a table and pull a specfic field to display until I reach the EOF?

Any help would be great!

 

[kjv1611]

May I ask why you're wanting to loop through just to show the value of one field? Surely there's something more than just wanting to show some field changing really fast?

 

[bigmerf]

Well, eventually I want to pass the value of this field to another table or perhaps to the To: section in an email that I will be sending out.

I guess I want to start with the basics to be able to just capture the value of a field in a table by starting with the first record, displaying the data, then to the second record, dispaying the data, etc...etc....

Once I have successfully captured the data from the table, I can pass the value to another area. I'm really just displaying the value to my screen for debugging purposes.

Hope this makes sense.

Thanks!

 

[MajP]

Public Sub displayField(tblName As String, fieldNumber As Integer)
On Error GoTo errlable
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select * from " & tblName
Set rs = CurrentDb.OpenRecordset(strSql)
If rs.Fields.Count < fieldNumber - 1 Then
MsgBox "Invalid Field Number. Enter a value from 1 to " & rs.Fields.Count
Else
Do While Not rs.EOF
MsgBox "Table: " & tblName & " Field Number: " & fieldNumber & " Value: " & rs.Fields(fieldNumber - 1)
rs.MoveNext
Loop
End If
Exit Sub
errlable:
If Err.Number = 3078 Then
MsgBox "Check the table name"
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub

Public Sub TestDisplay()
Dim tblName As String
Dim fieldNumber As Integer

tblName = InputBox("Enter table name")
fieldNumber = InputBox("Enter Field Number")
displayField tblName, fieldNumber
End Sub

 

[kjv1611]

Okay, just curious.... Sorry if it seemed like I was prying.

Something like this will work: (using a button to kick it off, cmdGo

Private Sub cmdGo_Click()
	[GREEN]'Create Variables[/GREEN]
	Dim db as DAO.Database
	Dim rs As DAO.Recordset
	Dim UserField as String

	[GREEN]'Instantiate Variables[/GREEN]
	Set db = CurrentDb
	Set rs = db.OpenRecordset("SELECT * FROM TableA ORDER BY MyID")
	[GREEN]'I'm assuming you have some ID field by which you can sort.
	'That will make sure you return the data in the order you prefer.[/GREEN]
	
	Do While Not rs.EOF
		[GREEN]'Don't need the next line, so I commented out
		'rs.movefirst[/GREEN]
		UserField = ("SELECT Field3 FROM TableA")
		Msgbox "Your results = " & rs.Fields("UserField"), vbInformation, "Results"
		rs.MoveNext
    Loop
	
	[GREEN]'Cleanup[/GREEN]
	rs.Close
	Set rs = Nothing
	db.Close
	Set db = Nothing	
End Sub

That doesn't include error handling, but it does at least clean up, assuming the code doesn't error out in the first place.

Let us know how it goes.. ask further questions if need be... if run into different questions, by all means start a new thread..

 

[kjv1611]

Figures, I try to go and make my code purty, and someone else cuts in line.

Entirely meant that statement as a joke/poking/ribbing in case anyone couldn't tell.

 

[MajP]

if you wanted to display each field and each record then
add something like

Public Sub testDisplay2()
  Dim tblName As String
  tblName = InputBox("Enter table name")
  displayFields tblName
End Sub
Public Sub displayFields(tblName As String)
  On Error GoTo errlable
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim strSql As String
  
  strSql = "Select * from " & tblName
  Set rs = CurrentDb.OpenRecordset(strSql)
  For Each fld In rs.Fields
    Do While Not rs.EOF
        MsgBox "Table: " & tblName & " Field Name: " & fld.Name & " Value: " & fld.Value
      rs.MoveNext
    Loop
  Next fld
  Exit Sub
errlable:
  If Err.Number = 3078 Then
    MsgBox "Check the table name"
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

 

[bigmerf]

Excellent! I appreciate the help. I was able to figure out a little on my own by just playing around and dabbling. Here is what I came up with (with real field names):

Dim db As Database
Dim rst As Recordset
Dim EmailAdd As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("Vendor Acknowledge Email")
  
    rst.MoveFirst
    Do Until rst.EOF
        If IsNull(rst("EmailAddress")) Then
            MsgBox "No Email Address"
        Else
            EmailAdd = rst("EmailAddress")
            MsgBox "This is your Email Address: " & EmailAdd
        End If
    rst.MoveNext
    Loop
  
    rst.Close
    Set rst = Nothing
    Set db = Nothing

Thanks again for all the help!