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.

 

[Pat Hartman]

ReAn,
you can't use an aggregate function in a query and select all other columns. Ther will be nothing to aggregate. Not to mention the error message that none of the other fields are included in a group by.

ghudson,
Remove the Count(Accounts) and the code may work.

FYI,
It is more efficient to use stored querydef's than SQL strings. You should only be using SQL strings if the query is Dynamic. The query in this example is Static. No part of it ever changes. A Dynamic query is one where the fields selected might change or the criteria fields might change (not a value - that's a parameter query) or the relational operator might change.

 

[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