Display contents of a table in a message box

[ghudson]

How can I display the contents of a table into a message box?

I am creating a table with an unmatched query that will list all of the unique invalid accounts that the user tried to import. I want to display the results of that table in a message box. There will never be more than a few accounts in the table so I am not worried that there will be too many to display in a message box. The table is named tblInvalidAccounts and the only field is named [Accounts].

The message would look something like this...

There are 3 invalid accounts that you tried to import.

012345, 654789, 987654

How can I grab the values [records] of that table and put it in a message box? Thanks in advance for your help!

 

[ReAn]

VBA Code:

Dim rst as DAO.Recordset
Dim sql as string, recordlist as string, msgboxstring as string
Dim r as Integer

sql = "SELECT *, Count([Accounts]) FROM tblInvalidAccounts"
Set rst = CurrentDB.OpenRecordset(sql)

r = 1
While Not rst.EOF
  If r = 1 Then
    recordlist = rst("Accounts")
  Else
    recordlist = recordlist & ", " & rst("Accounts")
  End If
  r = r + 1
  rst.MoveNext()
WEnd

msgboxstring = "There are " & (r-1) & " invalid accounts that you tried to import." & Chr(13) & Chr(13) & recordlist

MsgBox msgboxstring, vbCritical, "Invalid Accounts"

Put that in code somewhere and run it.

 

[ReAn]

Whoops.. you can remove that, i originally was goin to do that but remembered you cannot do that. So i removed the code related and used a simple counter, just missed part of the SQL...

BTW: Since he's doing a Message Box, i did it with code, and since it's a small number of records... who cares about efficiency. If it's like 2-50 records it should take a whole 1 second to calculate.

 

[WayneRyan]

g,

Is there some reason that you need a message box? A little popup form
with a ListBox would look much better and could easily be based on a
query.

Wayne

 

[ghudson]

Thanks ReAn. Your function worked once I removed the Count([Accounts]) as Pat noticed and used a select SQL. I also had to remove the parenthesis after the rst.MoveNext() line for Access 97 did not like the ().

Wayne, I just wanted to alert the user after an import routine that their data contained account numbers that will not be imported because they are not in the approved list

. A simple message box displaying the message and results works for this instance and I am also printing the table just incase there are a lot of invalid accounts but this should be a rare occurrence but it is a feature they asked for.

Thanks again ReAn!

Below is what I ended up with...

    Dim rst As DAO.Recordset
    Dim sql As String, sAccountList As String, sMessage As String
    Dim r As Integer
    
    sql = "SELECT tInvalidAccounts.Account FROM tInvalidAccounts ORDER BY tInvalidAccounts.Account;"
    Set rst = CurrentDb.OpenRecordset(sql)
    
    r = 1
    While Not rst.EOF
      If r = 1 Then
        sAccountList = rst("Account")
      Else
        sAccountList = sAccountList & ", " & rst("Account")
      End If
      r = r + 1
      rst.MoveNext
    Wend
    
    If r - 1 = 0 Then
        sMessage = "There were no invalid accounts."
    Else
        If r - 1 = 1 Then
            sMessage = "There was one invalid account that you tried to import." & vbCrLf & vbLf & sAccountList & vbCrLf & vbLf & "That account will not be imported."
            DoCmd.SelectObject acTable, "tInvalidAccounts", True
            DoCmd.PrintOut acPages, 1
        Else
            sMessage = "There were " & (r - 1) & " invalid accounts that you tried to import." & vbCrLf & vbLf & sAccountList & vbCrLf & vbLf & "Those accounts will not be imported."
            DoCmd.SelectObject acTable, "tInvalidAccounts", True
            DoCmd.PrintOut acPages, 1
        End If
    End If
    
    MsgBox sMessage, vbCritical, "Invalid Accounts"

 

[ReAn]

Glad I could help.

Btw: dosent really matter here, but it's just good programming practice.

Matters for repitition.

Replace:
If r - 1 = 0 Then
With:
If r = 1 Then

Replace:
If r - 1 = 1 Then
With:
If r = 2 Then

When doing big repitition, saving the extra calculation per repitition is nice.

 

[ghudson]

Actuall that is what I first attempted but it errors if there are no invalid accounts found in the table.

 

[ReAn]

It shouldn't